记一次sqlserver 有关排序导致的慢查询

1，执行sql语句，如下，执行时间17秒左右

SELECT Id,rn,created FROM (
SELECT
a.[Id] AS [Id] , row_number() OVER ( Order by a.Created desc) AS [rn],
a.created
FROM dbo.Listing (NOLOCK) a
JOIN dbo.ListingSetting (NOLOCK) b ON a.id = b.ListingId
JOIN dbo.ListingVersion (NOLOCK) c ON a.id = c.ListingId
JOIN dbo.OnlineListing (NOLOCK) d ON a.Id = d.ListingId
left join dbo.TimmingListingQueue(NOLOCK) e ON a.id=e.ListingId
left join dbo.EbayPromotionalSale(NOLOCK) f ON a.EbayPromotionalSaleId=f.Id
left join dbo.EbayListingOfferSet(NOLOCK) s on a.id=s.listingId
WHERE a.Enabled = 1
and (a.IsDelete = 0 or a.IsDelete is null) and a.ItemType=0
AND EXISTS(select 1 from dbo.V_TortSku t where t.itemid = a.itemid and t.itemtype = a.itemtype)
) a WHERE 1=1
and a.rn BETWEEN 1 AND 20

 

2，单独拆出sql，如下，执行时间600毫秒左右

SELECT
a.[Id] AS [Id] , row_number() OVER ( Order by a.Created desc) AS [rn],
a.created
FROM dbo.Listing (NOLOCK) a
JOIN dbo.ListingSetting (NOLOCK) b ON a.id = b.ListingId
JOIN dbo.ListingVersion (NOLOCK) c ON a.id = c.ListingId
JOIN dbo.OnlineListing (NOLOCK) d ON a.Id = d.ListingId
left join dbo.TimmingListingQueue(NOLOCK) e ON a.id=e.ListingId
left join dbo.EbayPromotionalSale(NOLOCK) f ON a.EbayPromotionalSaleId=f.Id
left join dbo.EbayListingOfferSet(NOLOCK) s on a.id=s.listingId
WHERE a.Enabled = 1
and (a.IsDelete = 0 or a.IsDelete is null) and a.ItemType=0
AND EXISTS(select 1 from dbo.V_TortSku t where t.itemid = a.itemid and t.itemtype = a.itemtype)
) a WHERE 1=1

 

3，sql去除分页条件，如下，执行时间600毫秒左右

SELECT Id,rn,created FROM (
SELECT
a.[Id] AS [Id] , row_number() OVER ( Order by a.Created desc) AS [rn],
a.created
FROM dbo.Listing (NOLOCK) a
JOIN dbo.ListingSetting (NOLOCK) b ON a.id = b.ListingId
JOIN dbo.ListingVersion (NOLOCK) c ON a.id = c.ListingId
JOIN dbo.OnlineListing (NOLOCK) d ON a.Id = d.ListingId
left join dbo.TimmingListingQueue(NOLOCK) e ON a.id=e.ListingId
left join dbo.EbayPromotionalSale(NOLOCK) f ON a.EbayPromotionalSaleId=f.Id
left join dbo.EbayListingOfferSet(NOLOCK) s on a.id=s.listingId
WHERE a.Enabled = 1
and (a.IsDelete = 0 or a.IsDelete is null) and a.ItemType=0
AND EXISTS(select 1 from dbo.V_TortSku t where t.itemid = a.itemid and t.itemtype = a.itemtype)
) a WHERE 1=1

 

4，分析后发现虽然以时间进行排序，但是数据表里面有很多数据时间是一样，导致每次排序查出来的数据都不一定一样

 

5，修改排序规则，将id加入排序规则，如下，执行时间600毫秒左右

SELECT Id,rn,created FROM (
SELECT
a.[Id] AS [Id] , row_number() OVER ( Order by a.Created desc,a.id) AS [rn],
a.created
FROM dbo.Listing (NOLOCK) a
JOIN dbo.ListingSetting (NOLOCK) b ON a.id = b.ListingId
JOIN dbo.ListingVersion (NOLOCK) c ON a.id = c.ListingId
JOIN dbo.OnlineListing (NOLOCK) d ON a.Id = d.ListingId
left join dbo.TimmingListingQueue(NOLOCK) e ON a.id=e.ListingId
left join dbo.EbayPromotionalSale(NOLOCK) f ON a.EbayPromotionalSaleId=f.Id
left join dbo.EbayListingOfferSet(NOLOCK) s on a.id=s.listingId
WHERE a.Enabled = 1
and (a.IsDelete = 0 or a.IsDelete is null) and a.ItemType=0
AND EXISTS(select 1 from dbo.V_TortSku t where t.itemid = a.itemid and t.itemtype = a.itemtype)
) a WHERE 1=1
and a.rn BETWEEN 1 AND 20

 

6，至此，不唯一的排序规则查询会导致查询性能下降