sql - 子查询会降低查询性能

Question

我有一个查询，其中有两个部分“搜索第 1 节”和“搜索第 2 节”，当这两个部分都取消注释时，查询最多需要 23 秒才能获得 87 条记录。如果我评论一个部分（第 1 部分现在评论）它会在 2 秒内给出结果。如何在未注释两个部分的情况下提高查询性能？

   declare @CountryId bigint = 336,
 @Lat varchar(30) = '31.512594000000004',
 @Lng varchar(30) = '74.34114',
 @Distance int = 10,
 @SearchString varchar(30) = 'sprite'
 DECLARE @orig_lat DECIMAL(12, 9) = CAST(@Lat AS DECIMAL(12,9));
 DECLARE @orig_lng DECIMAL(12, 9) = CAST(@Lng AS DECIMAL(12,9));
 DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326);

 select main.* from 
 ChowDeal.UVW_CD_SearchDeals search inner join 
 ChowDeal.UVW_CD_DealMenusForCustomer main 
 on search.Latitude is not null
    and search.Longitude is not null    
    and @orig.STDistance(geography::Point(search.Latitude, search.Longitude, 4326)) / 1609.34 < @Distance
    and main.MasterMenuID = search.MasterMenuID
 where 
    (


    -- Searching section 1
        --(@SearchString = '' or search.MenuName like '%'+ @SearchString +'%' )
        --or
        --(@SearchString = '' or search.Ingredient like '%'+ @SearchString +'%'  )
        --or
        --(@SearchString = '' or search.RestaurantName like '%'+ @SearchString +'%'  )

    -- Searching section 2
        --or
         search.MasterMenuID in 
                                        (   select DealID from ChowDeal.UVW_CD_SearchDeals 
                                            where MenuName like '%'+ @SearchString +'%' and DealID > 0                                          
                                        ) 
        )

标签： sqljoinsubqueryinner-join