SQL查询性能优化

debugcn 发表于 Dev

StackUser

我有以下SQL，它返回349017条记录。记录数量每天都会增加。

目前，我使用分页仅显示12条记录。每12记录返回一次大约需要2到3秒。如何将查询优化为0秒？请给出任何解决方案/建议。

我有很多图像数据将在应用程序中显示。当用户滚动时，必须根据此查询快速加载图像。

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)) > (GETDATE()))
            THEN 1
        ELSE 0
        END AS IsAccess
    ,vImage.ImageURL
    ,vImage.ThumbImageURL
    ,CASE 
        WHEN vImage.AudioURL <> ''
            THEN 1
        ELSE 0
        END AS IsAudio
    ,vImage.AudioURL
    ,CASE 
        WHEN vImage.Comments <> ''
            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) > 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

执行计划

金登

解决方法确实很简单-您应该将vInfo.CreatedDateDATETIME列作为索引。

如果由于某种原因无法修改列类型，则可以创建一个PERSISTENT计算列，然后在其上定义一个索引。

例子：

ALTER TABLE dbo.vInfo 
ADD _CreatedDateAsDateTime AS CONVERT(DATETIME, CreatedDate) PERSISTED;

CREATE INDEX [IX_...] ON dbo.vInfo(_CreatedDateAsDateTime) INCLUDE(...);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。