title: SQL Query Performance Optimization
body: <p>I have the following SQL and it returns 349017 records. The number of records will increase on a daily basis. </p>
  
  <p>Currently I used pagination to display only 12 records. It took around 2 to 3 secs to return every 12 records. How do I optimize the query to 0 second? Kindly give any solution / suggestions. </p>
  
  <p>I have a lot of image data that would be shown in the application. When the user scrolls, the images have to loaded quickly based on this query.</p>
  
  <pre><code>SELECT 
      vInfo.UserId
      ,vInfo.VehicleInfoId
      ,vImage.GuidedTourTemplateId
      ,vImage.VehicleImageId
      ,vInfo.Value AS VehicleName
      ,vInfo.Value AS ImageName
      ,Prop.PropertyId
      ,COALESCE(Loc.LocationName, 'NA') AS VehicleLocation
      ,Prop.PropertyName
      ,vImage.Latitude
      ,vImage.Longitude
      ,vImage.IsMain
      ,CASE 
          WHEN (DATEADD(DAY, tPlan.BackupDays, CAST(vImage.CreatedDate AS DATETIME)) &gt; (GETDATE()))
              THEN 1
          ELSE 0
          END AS IsAccess
      ,vImage.ImageURL
      ,vImage.ThumbImageURL
      ,CASE 
          WHEN vImage.AudioURL &lt;&gt; ''
              THEN 1
          ELSE 0
          END AS IsAudio
      ,vImage.AudioURL
      ,CASE 
          WHEN vImage.Comments &lt;&gt; ''
              THEN 1
          ELSE 0
          END AS IsComment
      ,vImage.Comments
      ,COALESCE(DImage.Damaged, 0) AS IsDamaged
      ,tImage.TotalAdditionalImages
      ,CONVERT(VARCHAR(12), CAST(vInfo.CreatedDate AS DATETIME), 107) AS CreateDate
      ,vInfo.EditDate
      ,0 AS RowNumber
  FROM
      [dbo].[VehicleInfo] vInfo
  INNER JOIN 
      [dbo].[Property] Prop ON Prop.PropertyId = vInfo.PropertyId
  INNER JOIN 
      [dbo].[Location] Loc ON Loc.LocationId = vInfo.LocationId
  INNER JOIN 
      [dbo].[VehicleImage] vImage ON vInfo.VehicleInfoId = vImage.VehicleInfoId
  INNER JOIN 
      [dbo].[PropertyPlan] tPropPlan ON vImage.PropertyId = tPropPlan.PropertyId
  INNER JOIN 
      [dbo].[PlanType] tPlan ON tPropPlan.PlanTypeId = tPlan.PlanTypeId
  OUTER APPLY 
      (SELECT COUNT(VehicleInfoId) AS TotalAdditionalImages
       FROM [dbo].[VehicleImage]
       WHERE VehicleInfoId = vInfo.VehicleInfoId) tImage
  OUTER APPLY 
      (SELECT 
           CASE 
              WHEN COUNT(VehicleInfoId) &gt; 0
                  THEN 1
                  ELSE 0
              END AS Damaged
       FROM [dbo].[DamagedVehicleImage]
       WHERE VehicleInfoId = vInfo.VehicleInfoId) DImage
  WHERE 
      vInfo.TenantId = '72c79d03-6692-4813-8c5a-4649cb34ca2f'
      AND Prop.DeleteStatus = 0
      AND vImage.IsMain = 1
  ORDER BY 
      CAST(vInfo.CreatedDate AS DATETIME) DESC
      ,Prop.PropertyName
  OFFSET 0 ROWS
  FETCH NEXT 12 ROWS ONLY
  </code></pre>
  
  <p>Execution plan</p>
  
  <p><a href="https://i.stack.imgur.com/gU9X6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gU9X6.png" alt="enter image description here"></a></p>