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 (~ milliard de lignes). Comme cela a été fait sur SQL Server 2008 R2, nous avons dû utiliser une auto-jointure. C'était lent.

J'ai pensé que j'expérimenterais la fonction de décalage; ce serait extrêmement précieux si rapide. Je l'ai trouvé ~ 2 à 3 fois plus rapide, mais comme cela devrait être une opération simple sous le capot, et comme son plan de requête / analyse de table était plus simple / considérablement réduit, je suis très déçu. Code à reproduire ci-dessous.

Créer DB:

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 statistiques (les requêtes sont exécutées deux fois pour réduire le temps 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.

Ainsi, lag prend 1 scan + 3109 lectures et prend ~ 1 s de temps CPU, une auto-jointure complexe qui doit parcourir à plusieurs reprises l'arbre bt prend 1 million de scans + 3,2 millions de lectures prend ~ 2,7 secondes.

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

Fonctionnant sur ThinkServer 140, RAM 8G (donc entièrement résident), double cœur, pas de conflit de disque. Je suis convaincu que le temps de transfert des ensembles de résultats vers le client, qui s'exécute sur la même machine, est négligeable.

select @@version 

Retour:

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

Éditer:

Par le commentaire de @ vnov, j'ai soigneusement réduit les frais généraux des clients avant de publier. Je regarde le temps CPU et non le temps 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 rien ajouter d'AFAICS significatif, sans parler d'un ordre de grandeur.



Edit2:

@Frisbee n'a pas vu pourquoi je pensais que le décalage était faible. Fondamentalement, l'algorithme consiste à se souvenir d'une valeur précédente et à la fournir n lignes plus tard. Si n = 1, c'est encore plus trivial, j'ai donc fait du code en utilisant des curseurs, avec et sans le décalage maison, et mesuré. J'ai également résumé les résultats de manière triviale pour ne pas renvoyer d'énormes ensembles de résultats, par vnov. Le curseur et les 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, le décalage de sélection simple par rapport à celui-ci était assez constant de ~ 360-400ms contre ~ 1700-1900ms, donc 4 ou 5 fois plus lent.

Pour les curseurs, celui du haut émule la première sélection, celui du bas émule la sélection avec décalage:

---------- 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 activé (supprimez SQL: événement de démarrage par lots), prend environ 2,5 heures pour moi, enregistrez la trace en tant que table appelée `` trace '', puis exécutez-la pour obtenir 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 curseur sans décalage prend en moyenne 13,65 secondes, le décalage d'émulation du curseur prend 16,04 secondes. La plupart du temps supplémentaire de ce dernier proviendra de la surcharge de l'interpréteur traitant les instructions supplémentaires (je m'attendrais à ce que ce soit beaucoup moins si implémenté en C), mais en tout cas, c'est moins de 20% de plus pour calculer le retard .

Alors, cette explication semble-t-elle raisonnable, et quelqu'un peut-il suggérer pourquoi le décalage est si médiocre dans une instruction select?

5
user3779002 30 déc. 2015 à 17:22

3 réponses

Meilleure réponse

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 autre 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;

stats

q1

q2

q3

1) Le LAG est implémenté en utilisant Window Spool, qui fournit deux fois le nombre de lignes (1 999 999) d'une table de travail temporaire (il est en mémoire dans ce cas, mais toujours). Le Window Spool ne met pas en cache les 1 000 000 lignes de la table de travail, il met en cache uniquement la taille de la fenêtre.

L'opérateur Fenêtre Spool développe chaque ligne dans l'ensemble de lignes qui représente la fenêtre qui lui est associée.

Il existe également de nombreux autres opérateurs moins lourds dans le plan. Le point ici est que LAG n'est pas implémenté comme vous le faites dans votre test de curseur.

2) Le plan de la requête de style ancien est assez bon. L'optimiseur est intelligent pour analyser la table une fois et effectuer une recherche d'index avec TOP pour chaque ligne à calculer MAX. Oui, c'est des millions de recherches, mais tout est en mémoire, donc c'est relativement rapide.

3) Passez la souris sur des flèches épaisses entre les opérateurs de plan et vous verrez la taille réelle des données. Il est deux fois plus grand pour Window Spool. Ainsi, lorsque tout est en mémoire et lié au processeur, cela devient important.

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

select  obig.g, A.g
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 processeur.

6
Vladimir Baranov 6 janv. 2016 à 23:32

Quel est votre retard? l'analyse doit toujours trouver le -1 g sur chaque ligne analysée, cela pourrait être / représente beaucoup de travail si g n'est pas la clé en cluster

0
automatic 30 déc. 2015 à 15:18

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

select * from big

Cela prend également beaucoup de temps.

Et votre requête ne peut pas être plus rapide que cela, car elle traite la même quantité de données. Il doit y avoir beaucoup d'E / S en cours. Je ne suis pas un expert en la matière, mais la taille de la table est approximative. Un serveur de 24 Mo et sql lit les données par blocs de 8 Ko, ce qui représente environ 3000 lectures physiques. Exécutez la requête et examinez le moniteur de performances / l'explorateur de processus, en particulier les E / S de disque.

0
vnov 30 déc. 2015 à 15:42