SQL2000 vers SQL2005. Les requêtes sont maintenant beaucoup plus lentes

Question

Cette requête prenait 3 secondes en SQL2000, maintenant elle prend environ 70 secondes. Les deux bases de données donnent les mêmes résultats. La base de données 2005 ne fonctionne pas en mode de compatibilité.

Actuellement, nous sommes en train de reconstruire la requête pour l'exécuter en SQL2005 par un processus d'élimination et de compréhension de la logique.

Cependant - quelqu'un peut-il voir quelque chose d'évident que nous avons manqué.

Et/ou y a-t-il des outils qui pourraient aider ici ?

Nous avons regardé le plan d'exécution... et le profileur. Et l'assistant de réglage de l'index.

Le profileur indique qu'un nombre considérable d'enregistrements supplémentaires sont interrogés pour obtenir les mêmes résultats.

Je sais que c'est une question très difficile à déboguer sans les données... une autre paire d'yeux est toujours la bienvenue s'il y a quelque chose d'évident !

Cheers

Dave

ALTER            PROCEDURE [dbo].[GetNodeList]
@ViewID int,
@UserID int = null
as

Select ProcessList.*, 
A.NDOC_DOC_ID, 
A.NDOC_Order,
A.OMNIBOOK_ID, 
A.Node_Order
from (
    (SELECT N.NOD_ID, 
    N.NOD_Name, 
    N.NOD_Procname, 
    N.NOD_Xpos, 
    N.NOD_Ypos, 
    N.NOD_Zpos,
    VN.VNOD_VIE_ID
    FROM Node N 
    INNER JOIN View_NODe VN 
    ON N.NOD_ID = VN.VNOD_NOD_ID
    Where VN.VNOD_VIE_ID = @ViewID) ProcessList
Left Join
 (
    SELECT N.NOD_ID, 
    N.NOD_Name, 
    N.NOD_Procname, 
    N.NOD_Xpos as NOD_Xpos, 
    N.NOD_Ypos as NOD_Ypos, 
    N.NOD_Zpos as NOD_Zpos, 
    VN.VNOD_VIE_ID, 
    ND.NDOC_DOC_ID as NDOC_DOC_ID, 
    ND.NDOC_Order as NDOC_Order,
    null as OMNIBOOK_ID, 
    null as Node_Order
    FROM Node N 
    INNER JOIN View_NODe VN 
    ON N.NOD_ID = VN.VNOD_NOD_ID
    LEFT JOIN NODe_DOCument ND 
    ON N.NOD_ID = ND.NDOC_NOD_ID
    WHERE VN.VNOD_VIE_ID=@ViewID
    and ND.NDOC_DOC_ID is not null

    and (@UserID is null 
        or exists (Select 1 
                from Document D 
                where Doc_ID = ND.NDOC_DOC_ID 
                and dbo.fn_UserCanSeeDoc(@UserID,D.Doc_ID)<>0
        )
    )

    UNION

    SELECT N.NOD_ID, 
    N.NOD_Name, 
    N.NOD_Procname, 
    N.NOD_Xpos, 
    N.NOD_Ypos, 
    N.NOD_Zpos, 
    VN.VNOD_VIE_ID, 
    null, 
    null,
    NOM.OMNIBOOK_ID, 
    NOM.Node_Order
    FROM Node N 
    INNER JOIN View_NODe VN 
    ON N.NOD_ID = VN.VNOD_NOD_ID
    LEFT JOIN NODe_OMNIBOOK NOM 
    ON N.NOD_ID = NOM.NODE_ID
    WHERE VN.VNOD_VIE_ID=@ViewID
    and NOM.OMNIBOOK_ID is not null
    and exists (select 1 from Omnibook_Doc where OmnibookID = NOM.OMNIBOOK_ID)
) A
--On ProcessList.NOD_ID = A.NOD_ID
ON ProcessList.NOD_Xpos = A.NOD_Xpos
And ProcessList.NOD_Ypos = A.NOD_Ypos
And ProcessList.NOD_Zpos = A.NOD_Zpos
And ProcessList.VNOD_VIE_ID = A.VNOD_VIE_ID
) 
ORDER BY 
ProcessList.NOD_Xpos,
ProcessList.NOD_Zpos,
ProcessList.NOD_Ypos,
Coalesce(A.NDOC_Order,A.Node_Order),
Coalesce(A.NDOC_DOC_ID,A.OMNIBOOK_ID)

Answer 1

J'ai déjà vu ça avant, quand les statistiques n'ont pas suivi les données. Il est possible dans ce cas que SQL Server 2005 utilise les statistiques différemment de SQL Server 2000. Essayez de reconstruire vos statistiques pour les tables utilisées dans la requête ; donc pour chaque table :

UPDATE STATISTICS <table> WITH FULLSCAN

Oui, j'ajouterais le FULLSCAN à moins que vous ne connaissiez suffisamment bien vos données pour penser qu'un échantillon d'enregistrements donnera d'assez bons résultats. Cela ralentira la création des statistiques, mais les rendra plus précises.

Answer 2

Est-il possible que vos statistiques ne se soient pas retrouvées ? dans la base de données 2k5 ? Donc la base de données n'a pas l'information nécessaire pour faire un bon plan ? Par opposition à votre ancienne base de données qui a de bonnes statistiques sur la table et peut choisir un meilleur plan pour les données ?

Answer 3

Pourrait-il s'agir d'un problème de "reniflage des paramètres", c'est-à-dire que SQL Server met en cache un plan de requête optimisé pour les paramètres fournis lors de la première exécution ? Microsoft technet en a plus

Answer 4

Un collège a trouvé une solution... concernant le retour de la fonction fn_UserCanSeeDoc dans le SQL.

Vous voyez ci-dessous l'ancien code de fonction commenté, puis le nouveau code SQL en ligne en dessous. Le code s'exécute maintenant très rapidement (de plus d'une minute à environ une seconde).

En regardant l'ancien SQL, je suis surpris de voir à quel point SQL2000 a fait un bon travail pour le faire fonctionner !

Cheers

--and dbo.fn_UserCanSeeDoc(@UserID,D.Doc_ID)<>0

-- if exists(Select 1 from Omnibook where Omnibook_ID = @DocID) 
--      Begin
--          Set @ReturnVal =  1
--      End 
--  
--  else
--      Begin
--          if exists(
--              Select 1
--              from UserSecurityModule USM
--              Inner join DocSecurity DS
--              On USM.SecurityModuleID = DS.SecurityModuleID
--              where USM.UserID = @UserID
--              and DS.DocID = @DocID
--          )
--          
--              Set @ReturnVal =  1
--          
--          else
--                  
--              Set @ReturnVal = 0
--      End

AND D.Doc_ID IN (select DS.DocID from UserSecurityModule USM
                Inner join DocSecurity DS
                On USM.SecurityModuleID = DS.SecurityModuleID
                where USM.UserID = @UserID)