Fonction analytique TSQL 'lag' – performance inattendue médiocre

Dans un travail précédent, nous devions comparer l'élément x avec l'élément x-1 pour un grand nombre de données (~ milliards de lignes). Comme cela a été fait sur SQL Server 2008 R2, nous devions utiliser une auto-jointure. C'était lent.

Je pensais expérimenter avec la fonction de décalage; ce serait extrêmement utile si vite. Je l'ai trouvé ~ 2 à 3 fois plus rapide mais comme il devrait être une opération simple sous le capot, et comme son plan de requête / numérisation de table était plus simple / considérablement réduit, je suis très déçu. Code à reproduire ci-dessous.

Créer une database:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDBForLag') DROP DATABASE TestDBForLag GO create database TestDBForLag ALTER DATABASE TestDBForLag SET RECOVERY SIMPLE go use TestDBForLag go set nocount on create table big (g binary(16) not null) go begin transaction declare @c int = 0 while @c < 100 begin insert into big(g) values(cast(newid() as binary(16))) set @c += 1 end commit go 10000 -- n repeats of last batch, "big" now has 1,000,000 rows alter table big add constraint clustered_PK primary key clustered (g) 

Requêtes

 set statistics time on set statistics io on -- new style select g, lag(g, 1) over (order by g) as xx from big order by g -- old style select obig.g, ( select max(g) from big as ibig where ibig.g < obig.g ) as xx from big as obig order by g 

Vous pouvez consulter vous-même les plans de requête réels / estimés, mais voici les résultats des statistics (les requêtes sont exécutées deux fois pour réduire le time de compilation):

 (1000000 row(s) affected) Table 'Worktable'. {edit: everything zero here}. **Table 'big'. Scan count 1, logical reads 3109**, {edit: everything else is zero here}. SQL Server Execution Times: CPU time = 1045 ms, elapsed time = 3516 ms. --- (1000000 row(s) affected) **Table 'big'. Scan count 1000001, logical reads 3190609**, {edit: everything else is zero here}. SQL Server Execution Times:CPU time = 2683 ms, elapsed time = 3439 ms. 

Donc, lag prend 1 scan + 3109 lectures et prend ~ 1 sec cpu time, une auto-jointure complexe qui doit marcher à plusieurs resockets le btree prend 1 millions d'parsings + 3,2 millions de lectures prend ~ 2,7 secondes.

Je ne vois aucune raison pour cette performance pourrie. Des idées?

En cours d'exécution sur ThinkServer 140, 8G ram (donc entièrement résident), dual core, pas de conflit de disque. Je suis satisfait que le time de transférer des sets de résultats au client, qui s'exécute sur la même machine, est négligeable.

 select @@version 

résultats:

 Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Modifier:

Commentaire de per @ vnov, j'ai fait attention à réduire les frais généraux des clients avant de postr. Je regarde le time CPU pas le time global. Tester:

 select * from big Table 'big'. Scan count 1, logical reads 3109, {rest zero} SQL Server Execution Times: CPU time = 125 ms, elapsed time = 2840 ms. select count(*) from big Table 'big'. Scan count 1, logical reads 3109, {rest zero} SQL Server Execution Times: CPU time = 109 ms, elapsed time = 129 ms. 

lag ne devrait tout simplement pas append quelque chose d'important AFAICS, ne vous dérange pas un ordre de grandeur.



Edit2:

@Frisbee n'a pas vu pourquoi je pensais que le lag était pauvre. Fondamentalement, l'algorithm consiste à se souvenir d'une valeur précédente et à l'envoyer plus tard. Si n = 1 c'est encore plus sortingvial donc j'ai fait du code en utilisant des sliders, avec et sans le lag fait maison, et mesuré. J'ai aussi sortingvialement résumé les résultats afin de ne pas returnner d'énormes sets de résultats, par sharepoint vnov. Les deux sliders et sélections ont donné les mêmes résultats que sumg = 127539666, sumglag = 127539460. Le code utilise la même table DB + que celle créée ci-dessus.

La version sélectionnée:

 select sum(cast(g as tinyint)) as sumg from ( select g from big ) as xx select sum(cast(g as tinyint)) as sumg, sum(cast(glag as tinyint)) as sumglag from ( select g, lag(g, 1) over (order by g) as glag from big ) as xx 

Je n'ai pas fait de mesure en vrac, mais par observation, la sélection simple par rapport au décalage ici était assez constante ~ 360-400ms vs ~ 1700-1900ms, soit 4 ou 5 fois plus lentement.

Pour les sliders, le premier émule d'abord select, le second émule select avec lag:

 ---------- nonlagging batch -------------- use TestDBForLag set nocount on DECLARE crsr CURSOR FAST_FORWARD READ_ONLY FOR select g from big order by g DECLARE @g binary(16), @sumg int = 0 OPEN crsr FETCH NEXT FROM crsr INTO @g WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @sumg += cast(@g as tinyint) END FETCH NEXT FROM crsr INTO @g END CLOSE crsr DEALLOCATE crsr select @sumg as sumg go 300 ---------- lagging batch -------------- use TestDBForLag set nocount on DECLARE crsr CURSOR FAST_FORWARD READ_ONLY FOR select g from big order by g DECLARE @g binary(16), @sumg int = 0 DECLARE @glag binary(16) = 0, @sumglag int = 0 OPEN crsr FETCH NEXT FROM crsr INTO @g WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @sumg += cast(@g as tinyint) set @sumglag += cast(@glag as tinyint) -- the only ... set @glag = @g -- ... differences END FETCH NEXT FROM crsr INTO @g END CLOSE crsr DEALLOCATE crsr select @sumg as sumg, @sumglag as sumglag go 300 

Exécutez ce qui précède avec le profileur SQL sur (supprimer SQL: événement Batch Starting), prend ~ 2,5 heures pour moi, save la trace sous la forme d'une table appelée 'trace', puis exécutez ceci pour get la durée moyenne

 -- trace save duration as microseconds, -- divide by 1000 to get back to milli select cast(textdata as varchar(8000)) as textdata, avg(duration/1000) as avg_duration_ms from trace group by cast(textdata as varchar(8000)) 

pour moi le slider non-agrafant prend une moyenne de 13,65 secondes, le slider-emulation-lag prend 16,04 secondes. La majeure partie du time supplémentaire de ce dernier viendra du surcoût de l'interprète traitant des déclarations supplémentaires (je m'attendrais à ce qu'il soit beaucoup less si implémenté en C), mais dans tous les cas c'est less de 20% de plus pour calculer le décalage .

Donc, cette explication semble-t-elle raisonnable, et quelqu'un peut-il suggérer pourquoi le décalage est si peu performant dans une déclaration choisie?

Examinez les plans d'exécution des deux variantes et vous verrez ce qui se passe. J'utilise une version gratuite de SQL Sentry Plan Explorer pour cela.

Je compare ces trois requêtes (plus une de plus avec OUTER APPLY ):

 select count(*) from big; -- new style select g, lag(g) over (order by g) as xx from big order by g; -- old style select obig.g, ( select max(g) from big as ibig where ibig.g < obig.g ) as xx from big as obig order by g; 

Statistiques

q1

q2

q3

1) Le LAG est implémenté à l'aide de Window Spool, qui fournit deux fois le nombre de lignes (1 999 999) d'une table de travail temporaire (il est en memory dans ce cas, mais toujours). Le spool de window ne met pas en cache toutes les 1 000 000 lignes de la table de travail, il ne met en cache que la taille de la window.

L'opérateur Window Spool développe chaque ligne dans l'set des lignes représentant la window qui lui est associée.

Il y a aussi beaucoup d'autres opérateurs less lourds dans le plan. Le point ici est que LAG n'est pas implémenté comme vous le faites dans votre test de slider.

2) Le plan pour l'ancienne requête de style est plutôt bon. L'optimiseur est intelligent pour balayer la table une fois et faire une search d'index avec TOP pour chaque rangée pour calculer MAX . Oui, c'est million cherche, mais tout est dans la memory, donc c'est relativement rapide.

3) Passez la souris sur les flèches épaisses entre les opérateurs de plan et vous verrez la taille réelle des données. Il est deux fois plus gros pour Window Spool. Donc, quand tout est dans la memory et lié au CPU, cela devient important.

4) Votre ancienne requête de style pourrait être réécrite comme:

 select obig.g, Ag from big as obig OUTER APPLY ( SELECT TOP(1) ibig.g FROM big as ibig WHERE ibig.g < obig.g ORDER BY ibig.g DESC ) AS A order by obig.g; 

q4

, ce qui est un peu plus efficace (voir la colonne CPU dans la capture d'écran).


Ainsi, LAG est très efficace dans le nombre de pages lues, mais utilise beaucoup le CPU.

Quel est le g que vous êtes en retard? l'parsing doit toujours find les -1 g sur chaque ligne balayée, cela pourrait être / est beaucoup de travail si g n'est pas la key groupée

Le lag lui-même peut ne pas prendre beaucoup de time, puisque g est la key primaire en cluster. Si tu essayes:

 select * from big 

cela prend aussi beaucoup de time.

Et votre requête ne peut pas être plus rapide, car elle traite la même quantité de données. Il doit y avoir beaucoup d'inputs-sorties. Je ne suis pas un expert sur ce sujet, mais la taille de la table est aprox. Les servers de 24 Mo et sql lisent datatables par blocs de 8 Ko, ce qui fait environ 300 lectures physiques. Exécutez la requête et examinez le moniteur de performances / l'explorateur de process, l'E / S de disque en particulier.