加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 (https://www.hxwgxz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 编程 > 正文

sql-server – 通过数百万行的可自定义排序进行分页性能

发布时间:2020-12-26 03:08:55 所属栏目:编程 来源:网络整理
导读:在我们的应用程序中,我们有一个网格,用户可以在其中分页大量记录(1000万到2000万).网格支持在多个列(20)中按升序和降序排序.许多值也不是唯一的,因此应用程序也会将id排序为打破平局,以确保行始终显示在同一页面上.例如,如果用户想要按窗口小部件大小排序(从
副标题[/!--empirenews.page--]

在我们的应用程序中,我们有一个网格,用户可以在其中分页大量记录(1000万到2000万).网格支持在多个列(20)中按升序和降序排序.许多值也不是唯一的,因此应用程序也会将id排序为打破平局,以确保行始终显示在同一页面上.例如,如果用户想要按窗口小部件大小排序(从最大的开始),应用程序将生成一个看起来有点像这样的查询:

SELECT TOP 30
    * -- (Pretend that there is a list of columns here)
FROM Test
--  WHERE widgetSize > 100
ORDER BY
    widgetSize DESC,id ASC

此查询需要大约15秒才能运行(使用缓存数据),主要成本似乎是通过widgetSize排序~1.3m行.为了调整这个查询,我发现如果我添加WHERE子句仅限于最大的widgetSizes(在上面的查询中注释掉),那么查询只需要大约800ms(所有前50,000个结果都有一个小部件大小> 100).

为什么没有WHERE子句的查询这么慢?我已经检查了widgetSize列上的统计信息,它们显示前739行有一个WidgetSize> 506.由于只需要30行,SQL服务器是否可以不使用此信息来推断它只需要对小部件大小的行进行排序?

我知道我可以通过在widgetSize和id上添加索引来更快地执行此特定查询,但是此索引仅在此特定方案中有用,并且如果(例如)用户反转排序方向则变得毫无价值.此表包含许多其他列,每个索引都很大(~200mb),因此我无法为每个可能的排序顺序添加索引.

有没有办法让我可以在不为每个可能的排序顺序添加索引的情况下执行这些查询查询? (用户可以按20列中的任何一个排序)

以下脚本创建上表并使用一些代表性数据填充它.该表比实际表格窄得多,但仍然展示了我所看到的性能.在我的PC上,使用where子句的查询需要大约200ms,而没有where caluse的查询大约需要800ms.

警告:运行此脚本后生成的数据库大小约为2Gb.

CREATE TABLE Test
(
    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,widgetSize INT NOT NULL
)

CREATE TABLE #Data
(
    widgetSize INT NOT NULL,recordCount INT NOT NULL
)

INSERT INTO #Data (widgetSize,recordCount)
VALUES
    (40826,1),(30317,(28513,(24255,(20247,(20245,(16445,(15719,(8489,(8486,(4753,(4424,(4409,(3738,(3732,(3725,4),(3691,(3678,(3655,(3653,3),(3575,(3572,(3569,(2919,(2903,(2804,(2795,(2765,(2732,(2731,(2677,(2631,(2624,(2548,(2544,(2531,2),(2516,(2512,(2503,(2502,(2472,(2467,(2460,(2452,(2442,(2439,(2412,(2411,(2405,(2382,(2375,(2348,(2341,(2322,(2321,(2316,(2314,(2291,(2284,(2258,(2251,(2232,(2229,7),(2222,(2204,(2186,(2173,(2145,(2143,(2113,(2110,(2089,(2082,(2080,(2056,(2054,(2052,(2019,(1991,(1900,(1870,(1869,(1856,(1826,(1802,(1792,(1786,(1784,(1781,(1780,(1771,(1758,(1756,(1749,(1742,(1740,(1729,(1728,(1726,(1718,(1717,(1707,(1701,(1696,(1694,(1688,(1679,(1649,(1632,(1621,(1616,(1588,(1584,(1554,(1539,(1525,(1516,(1515,(1476,(1467,(1463,(1406,(1390,(1370,(1350,(1338,(1335,(1326,(1325,(1316,(1315,(1311,(1308,(1305,(1302,(1299,(1298,(1285,(1283,(1282,(1270,(1261,(1255,(1251,(1250,(1242,(1220,(1219,(1217,(1216,(1193,(1190,(1164,(1147,(1137,(1134,(1133,(1128,(1120,(1113,(1105,(1099,6),(1098,(1096,(1095,(1092,(1082,(1061,(1050,(1040,(1007,(987,(966,(960,(954,(952,(951,(950,(924,(923,(917,(916,(907,(902,(900,(896,(892,(889,(879,(876,(874,(868,(861,8),(860,(854,(853,(852,(851,(847,(846,(843,13),(839,(838,(837,(825,(824,(820,(819,(818,5),(817,9),(814,(811,(809,(807,(804,(798,(795,(794,(791,(789,(788,(782,(778,(770,(769,(768,(763,(760,(756,(755,(753,(751,(748,(747,(746,(745,(744,(743,(742,(741,(737,(735,(734,(733,(731,(730,(728,(727,(726,(724,(721,(718,(714,(710,(707,(706,(703,(697,(696,(692,(686,(684,(683,(680,(678,(674,(672,(671,(669,(668,(667,(666,(665,(663,(662,(661,(658,(657,(656,(655,(654,(652,(651,(650,(649,(644,(643,(642,(641,(637,(636,(632,(631,(630,(629,(627,(625,(624,(623,(620,(618,(617,(616,(615,(614,(612,(605,(603,(601,(595,(594,(593,(590,(588,(587,(586,(583,(582,(580,(578,(577,(576,(575,(574,(573,(572,(571,(570,(569,(568,(567,(566,(565,(564,(563,(562,(560,(559,(558,(557,(556,(555,(554,(553,(552,(551,(550,(549,(548,(547,(546,(544,(543,(542,(541,(538,(536,(534,(533,(532,(531,(530,(529,11),(528,(527,(526,(525,(524,(523,(522,(521,(520,(518,12),(517,(515,(514,(513,(511,16),(510,(509,(508,(507,(506,41),(505,(504,(503,(502,(501,(500,(499,(498,(497,(496,10),(495,(494,(493,(492,(491,(490,(489,(488,(487,(486,(485,(484,(483,(482,(481,(480,(479,(478,(477,(476,19),(475,(474,(473,(472,(471,(470,(469,(468,(467,(466,(465,(464,(463,(462,(461,(460,(459,(458,(457,(456,(455,(454,(453,(452,(451,20),(450,51),(449,(448,(447,(446,(445,(444,(443,80),(442,(441,(440,(439,(438,14),(437,58),(436,(435,(434,(433,(432,(431,(430,30),(429,21),(428,(427,18),(426,(425,(424,(423,(422,(421,(420,(419,(418,15),(417,(416,22),(415,(414,(413,(412,(411,(410,68),(409,62),(408,(407,(406,(405,(404,(403,(402,31),(401,24),(400,(399,(398,(397,(396,(395,(394,(393,(392,(391,(390,(389,(388,26),(387,(386,27),(385,23),(384,25),(383,(382,(381,69),(380,(379,34),(378,(377,(376,(375,(374,35),(373,32),(372,43),(371,28),(370,(369,(368,(367,(366,36),(365,45),(364,42),(363,82),(362,(361,33),(360,29),(359,(358,(357,17),(356,(355,(354,(353,(352,(351,(350,(349,(348,(347,(346,(345,(344,(343,(342,(341,(340,(339,(338,(337,(336,(335,(334,(333,(332,(331,(330,(329,(328,(327,(326,50),(325,(324,(323,(322,(321,(320,(319,(318,(317,(316,(315,(314,(313,39),(312,(311,(310,(309,(308,(307,(306,(305,(304,(303,(302,38),(301,47),(300,(299,(298,(297,46),(296,(295,(294,(293,(292,(291,(290,(289,(288,(287,(286,(285,(284,(283,(282,(281,(280,(279,(278,(277,(276,(275,(274,(273,53),(272,(271,(270,(269,(268,(267,40),(266,143),(265,(264,(263,(262,(261,(260,52),(259,96),(258,(257,(256,(255,(254,(253,(252,64),(251,(250,(249,(248,(247,(246,131),(245,108),(244,(243,(242,(241,(240,(239,57),(238,55),(237,(236,(235,(234,(233,(232,(231,(230,(229,(228,(227,(226,(225,54),(224,90),(223,91),(222,60),(221,277),(220,70),(219,(218,(217,100),(216,185),(215,98),(214,(213,(212,(211,77),(210,150),(209,175),(208,(207,199),(206,158),(205,(204,85),(203,129),(202,75),(201,59),(200,73),(199,123),(198,72),(197,155),(196,193),(195,66),(194,119),(193,(192,(191,(190,(189,284),(188,(187,79),(186,118),(185,93),(184,92),(183,194),(182,152),(181,(180,134),(179,(178,121),(177,(176,140),(175,262),(174,159),(173,(172,(171,(170,116),(169,168),(168,297),(167,171),(166,214),(165,474),(164,176),(163,(162,215),(161,310),(160,(159,183),(158,208),(157,377),(156,248),(155,804),(154,452),(153,133),(152,224),(151,826),(150,299),(149,367),(148,427),(147,413),(146,1190),(145,796),(144,450),(143,334),(142,308),(141,707),(140,580),(139,601),(138,403),(137,351),(136,411),(135,547),(134,528),(133,506),(132,306),(131,485),(130,419),(129,832),(128,1034),(127,894),(126,1168),(125,313),(124,787),(123,1079),(122,984),(121,1086),(120,1525),(119,1007),(118,539),(117,1596),(116,1307),(115,2081),(114,1256),(113,2200),(112,1184),(111,535),(110,1404),(109,1219),(108,1675),(107,1765),(106,1784),(105,890),(104,931),(103,1769),(102,1720),(101,1528),(100,1639),(99,1955),(98,1434),(97,979),(96,2295),(95,2516),(94,3043),(93,2972),(92,3493),(91,1873),(90,1047),(89,2228),(88,2328),(87,1804),(86,5243),(85,2256),(84,1602),(83,898),(82,2025),(81,2207),(80,2559),(79,2720),(78,3302),(77,5410),(76,994),(75,2767),(74,3343),(73,3951),(72,4116),(71,6164),(70,2992),(69,2066),(68,18269),(67,13159),(66,13142),(65,7387),(64,8759),(63,4887),(62,1847),(61,10239),(60,6990),(59,8785),(58,8161),(57,10081),(56,4899),(55,1744),(54,9916),(53,8713),(52,9529),(51,8827),(50,10255),(49,6392),(48,2253),(47,9939),(46,12083),(45,12103),(44,12667),(43,19758),(42,9699),(41,5450),(40,26566),(39,41836),(38,48441),(37,49562),(36,71987),(35,32390),(34,7159),(33,179598),(32,158675),(31,132676),(30,151839),(29,139014),(28,632065),(27,7800),(26,259440),(25,215240),(24,170986),(23,157141),(22,167304),(21,20408),(20,11949),(19,267541),(18,208096),(17,174708),(16,156445),(15,153569),(14,73937),(13,73821),(12,310246),(11,231829),(10,179047),(9,145506),(8,133433),(7,108736),(6,73381),(5,84825),(4,86641),(3,86172),(2,87690),(1,148110),(0,7960761),(-1,861),(-2,365),(-3,356),(-4,578),(-5,293),(-6,(-7,414),(-8,748),(-9,113),(-10,782),(-11,705),(-12,711),(-13,915),(-14,(-15,(-16,(-17,(-18,56),(-19,(-20,(-21,(-22,(-23,(-24,(-25,44),(-26,(-27,(-28,(-29,(-30,(-31,(-58,(-59,(-60,(-61,(-64,(-70,(-97,(-145,(-234,(-239,(-240,(-272,(-273,(-274,(-276,(-1094,(-1096,(-1337,(-1341,(-3545,(-3547,(-10962,(-10964,(-255449,(-255470,(-365104,(-365105,1)

DECLARE c CURSOR FOR
SELECT widgetSize,recordCount FROM #Data
OPEN c

DECLARE @widgetSize INT
DECLARE @rowCount INT
FETCH NEXT FROM c INTO @widgetSize,@rowCount

WHILE @@FETCH_STATUS = 0  
BEGIN  
    ;WITH cte AS
    (
        SELECT rowNumber = 1
        UNION ALL
        SELECT rowNumber + 1
        FROM cte
        WHERE rowNumber < @rowCount
    )
    INSERT INTO Test
    (
        widgetSize
    )
    SELECT
        @widgetSize
    FROM   cte 
    OPTION (MAXRECURSION 0)

    FETCH NEXT FROM c INTO @widgetSize,@rowCount
END   

CLOSE c  
DEALLOCATE c

DROP TABLE #Data

CREATE STATISTICS WidgetSize
ON Test (WidgetSize) WITH FULLSCAN

解决方法

这类问题没有神奇的解决方案.为了避免可能昂贵的排序,必须有一个可以提供所请求顺序的索引(并且优化器必须选择使用该索引).如果没有支持索引,最好的SQL Server本身就可以在排序结果集之前限制符合条件的行(基于WHERE子句).如果没有WHERE子句,这意味着对表中的所有行进行排序.

I’ve checked the statistics on the widgetSize column and they show that the top 739 rows have a WidgetSize > 506

(编辑:核心网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

热点阅读