Skip to content
Advertisement

After adding cursor to this query the execution time went from 10s to 2m.10s

I’m working on this query and after i added the bloc of cursor the execution time goes from 10s to 2m.

Is there any way to reduce the execution time? The query is a result of 3 selects on the same table but with different conditions.

I needed the cursor because I should get the price of an article from a table that contains prices per dates that I couldn’t get from in first queries.

declare @MonthGiftRecap TABLE 
    (
    UIDGIFT uniqueidentifier,
    Annee int,
    Mois int,
    TypeFamille nvarchar(500),
    Famille nvarchar(500),
    CodeCadeau nvarchar(500),
    Cadeau nvarchar(500),
    Inscrit Date,
    CodeAmbassadeur nvarchar(10),
    NomAmbassadeur nvarchar(500),
    TelephoneAmbassadeur nvarchar(500),
    Region nvarchar(500),
    NomAsm nvarchar(500),
    NumDocument nvarchar(500),
    DateConsommation date,
    NumAvoir nvarchar(500),
    Starter bit,
    MoisStarter int,
    UIDRegion uniqueidentifier,
    UIDPromotionTier uniqueidentifier,
    UIDTypeCadeauxFamille uniqueidentifier,
    UIDCadeaux uniqueidentifier,
    UIDDoc uniqueidentifier,
    UIDAnnee uniqueidentifier,
    UIDMois uniqueidentifier,
    UIDTier uniqueidentifier,
    UIDAsm uniqueidentifier,
    TypeAttribution nvarchar(50),
    DateReactivation date,
    DateAttribution Date,
    CArticle uniqueidentifier,
    PrixUnitaire money
    )


insert into @MonthGiftRecap

SELECT        
              NEWID(),dbo.Annee.Numero AS Annee, dbo.Mois.Numero AS Mois, dbo.TypeCadeauxFamille.TypeFamille, dbo.TypeCadeauxFamille.Libelle AS Famille,dbo.TypeCadeaux.Code CodeCadeau, dbo.TypeCadeaux.Libelle AS Cadeau, 
                           convert(date,dbo.Tier.DateSaisie) AS Inscrit, dbo.Tier.Code, dbo.Tier.Nom, dbo.Tier.Telephone, dbo.Region.Libelle AS Region, dbo.ASM.Nom AS NomASM, dbo.[Document].NumDocument, 
                           dbo.[Document].DateDocument AS DateConsommation, Avoir.NumDocument AS NumAvoir, dbo.TypeCadeaux.Starter, dbo.TypeCadeaux.MoisStarter, dbo.Tier.Region AS UIDRegion, 
                           dbo.PromotionTier.UID AS UIDPromotionTier, dbo.TypeCadeauxFamille.UID AS UIDTypeCadeauxFamille, dbo.TypeCadeaux.UID AS UIDCadeaux, dbo.[Document].UID AS UIDDoc, dbo.PromotionTier.Annee AS UIDAnnee,
                           dbo.PromotionTier.Mois AS UIDMois,dbo.Tier.UID AS UIDTier, dbo.ASM.UID AS UIDAsm,
                           case when (DATEADD(month, DATEDIFF(month, 0, convert(date,dbo.Tier.DateSaisie)), 0) Between Dateadd (Month, -4 , DATEFROMPARTS (dbo.Annee.Numero, dbo.Mois.Numero, 1)) AND  DATEFROMPARTS (dbo.Annee.Numero, dbo.Mois.Numero, 1))  Then 'STARTER'
                           when (select TypeBlocage from TierArchiveBlocage where Code = dbo.Tier.Code AND DateBlocage = (Select max(DateBlocage) from TierArchiveBlocage where Code=dbo.Tier.Code AND DateBlocage < DATEFROMPARTS (dbo.Annee.Numero, dbo.Mois.Numero, 1) AND Month(DateBlocage) between dbo.Mois.Numero-3 AND dbo.Mois.Numero-1 ) ) = 'ACV'  Then 'REACTIVATION'
                           else 'INACTIFS'
                          end TypeAttribution,(select DateBlocage from dbo.FNDateReactivation(dbo.Tier.Code,DATEFROMPARTS (dbo.Annee.Numero, dbo.Mois.Numero, 1))) DateDerniereReactivation,
                          dbo.PromotionTier.DateSaisie AS DateAttribution,dbo.Article.UID,null
                     

                     

FROM            dbo.ArticlePromotion RIGHT OUTER JOIN
                         dbo.TypeCadeaux INNER JOIN
                         dbo.TypeCadeauxFamille ON dbo.TypeCadeaux.TypeCadeauxCategorie = dbo.TypeCadeauxFamille.UID INNER JOIN
                         dbo.PromotionTier ON dbo.TypeCadeaux.UID = dbo.PromotionTier.TypeCadeaux INNER JOIN
                         dbo.Tier ON dbo.PromotionTier.Tier = dbo.Tier.UID INNER JOIN
                         dbo.Annee ON dbo.PromotionTier.Annee = dbo.Annee.UID INNER JOIN
                         dbo.Mois ON dbo.PromotionTier.Mois = dbo.Mois.UID ON dbo.ArticlePromotion.Annee = dbo.Annee.UID AND dbo.ArticlePromotion.Mois = dbo.Mois.UID AND 
                         dbo.ArticlePromotion.TypeCadeaux = dbo.TypeCadeaux.UID LEFT OUTER JOIN
                         dbo.Article ON dbo.ArticlePromotion.Article = dbo.Article.UID LEFT OUTER JOIN
                         dbo.Region ON dbo.Tier.Region = dbo.Region.UID LEFT OUTER JOIN
                         dbo.[Document] ON dbo.PromotionTier.Facture = dbo.[Document].UID LEFT OUTER JOIN
                         dbo.[Document] AS Avoir ON dbo.[Document].UIDDocumentAvoir = Avoir.UID LEFT OUTER JOIN
                         dbo.ASM ON dbo.Tier.ASM = dbo.ASM.UID

WHERE   (dbo.TypeCadeauxFamille.TypeFamille = N'BNV') AND (dbo.Annee.Numero = 2020) AND (dbo.Mois.Numero = 6)

insert into @MonthGiftRecap

SELECT        
                 NEWID(),dbo.Annee.Numero AS Annee, dbo.Mois.Numero AS Mois, dbo.TypeCadeauxFamille.TypeFamille, dbo.TypeCadeauxFamille.Libelle AS Famille,dbo.TypeCadeaux.Code CodeCadeau, dbo.TypeCadeaux.Libelle AS Cadeau, 
                           convert(date,dbo.Tier.DateSaisie) AS Inscrit, dbo.Tier.Code, dbo.Tier.Nom, dbo.Tier.Telephone, dbo.Region.Libelle AS Region, dbo.ASM.Nom AS NomASM, dbo.[Document].NumDocument, 
                           dbo.[Document].DateDocument AS DateConsommation, Avoir.NumDocument AS NumAvoir, dbo.TypeCadeaux.Starter, dbo.TypeCadeaux.MoisStarter, dbo.Tier.Region AS UIDRegion,
                           dbo.PromotionTier.UID AS UIDPromotionTier, dbo.TypeCadeauxFamille.UID AS UIDTypeCadeauxFamille, dbo.TypeCadeaux.UID AS UIDCadeaux, dbo.[Document].UID AS UIDDoc, 
                           dbo.PromotionTier.Annee AS UIDAnnee, dbo.PromotionTier.Mois AS UIDMois,dbo.Tier.UID AS UIDTier, dbo.ASM.UID AS UIDAsm,
                           CASE WHEN ISNUMERIC(dbo.PromotionTier.GradeAtteinte) = 1 THEN CONVERT(INT, dbo.PromotionTier.GradeAtteinte) ELSE dbo.PromotionTier.GradeAtteinte END TypeAttribution,
                           (select  DateBlocage from dbo.FNDateReactivation(dbo.Tier.Code,DATEFROMPARTS (dbo.Annee.Numero, dbo.Mois.Numero, 1))) DateDerniereReactivation,
                           dbo.PromotionTier.DateSaisie AS DateAttribution,dbo.Article.UID,null
                     

FROM            dbo.ArticlePromotion RIGHT OUTER JOIN
                         dbo.TypeCadeaux INNER JOIN
                         dbo.TypeCadeauxFamille ON dbo.TypeCadeaux.TypeCadeauxCategorie = dbo.TypeCadeauxFamille.UID INNER JOIN
                         dbo.PromotionTier ON dbo.TypeCadeaux.UID = dbo.PromotionTier.TypeCadeaux INNER JOIN
                         dbo.Tier ON dbo.PromotionTier.Tier = dbo.Tier.UID INNER JOIN
                         dbo.Annee ON dbo.PromotionTier.Annee = dbo.Annee.UID INNER JOIN
                         dbo.Mois ON dbo.PromotionTier.Mois = dbo.Mois.UID ON dbo.ArticlePromotion.Annee = dbo.Annee.UID AND dbo.ArticlePromotion.Mois = dbo.Mois.UID AND 
                         dbo.ArticlePromotion.TypeCadeaux = dbo.TypeCadeaux.UID LEFT OUTER JOIN
                         dbo.Article ON dbo.ArticlePromotion.Article = dbo.Article.UID LEFT OUTER JOIN
                         dbo.Region ON dbo.Tier.Region = dbo.Region.UID LEFT OUTER JOIN
                         dbo.[Document] ON dbo.PromotionTier.Facture = dbo.[Document].UID LEFT OUTER JOIN
                         dbo.[Document] AS Avoir ON dbo.[Document].UIDDocumentAvoir = Avoir.UID LEFT OUTER JOIN
                         dbo.ASM ON dbo.Tier.ASM = dbo.ASM.UID

WHERE   (dbo.TypeCadeauxFamille.TypeFamille = N'PSG') AND (dbo.Annee.Numero = 2020) AND (dbo.Mois.Numero = 6)

insert into @MonthGiftRecap

SELECT        
              NEWID(),dbo.Annee.Numero AS Annee, dbo.Mois.Numero AS Mois, dbo.TypeCadeauxFamille.TypeFamille, dbo.TypeCadeauxFamille.Libelle AS Famille,dbo.TypeCadeaux.Code CodeCadeau, dbo.TypeCadeaux.Libelle AS Cadeau, convert(date,dbo.Tier.DateSaisie) AS Inscrit, dbo.Tier.Code, 
                           dbo.Tier.Nom, dbo.Tier.Telephone, dbo.Region.Libelle AS Region, dbo.ASM.Nom AS NomASM, dbo.[Document].NumDocument, dbo.[Document].DateDocument AS DateConsommation, Avoir.NumDocument AS NumAvoir, 
                           dbo.TypeCadeaux.Starter, dbo.TypeCadeaux.MoisStarter, dbo.Tier.Region AS UIDRegion, dbo.PromotionTier.UID AS UIDPromotionTier, 
                           dbo.TypeCadeauxFamille.UID AS UIDTypeCadeauxFamille, dbo.TypeCadeaux.UID AS UIDCadeaux, dbo.[Document].UID AS UIDDoc, dbo.PromotionTier.Annee AS UIDAnnee, dbo.PromotionTier.Mois AS UIDMois,
                           dbo.Tier.UID AS UIDTier, dbo.ASM.UID AS UIDAsm,dbo.PromotionTier.GradeAtteinte TypeAttribution,
                           (select  DateBlocage from dbo.FNDateReactivation(dbo.Tier.Code,DATEFROMPARTS (dbo.Annee.Numero, dbo.Mois.Numero, 1))) DateDerniereReactivation,
                           dbo.PromotionTier.DateSaisie AS DateAttribution,dbo.Article.UID,null
                     

FROM            dbo.ArticlePromotion RIGHT OUTER JOIN
                         dbo.TypeCadeaux INNER JOIN
                         dbo.TypeCadeauxFamille ON dbo.TypeCadeaux.TypeCadeauxCategorie = dbo.TypeCadeauxFamille.UID INNER JOIN
                         dbo.PromotionTier ON dbo.TypeCadeaux.UID = dbo.PromotionTier.TypeCadeaux INNER JOIN
                         dbo.Tier ON dbo.PromotionTier.Tier = dbo.Tier.UID INNER JOIN
                         dbo.Annee ON dbo.PromotionTier.Annee = dbo.Annee.UID INNER JOIN
                         dbo.Mois ON dbo.PromotionTier.Mois = dbo.Mois.UID ON dbo.ArticlePromotion.Annee = dbo.Annee.UID AND dbo.ArticlePromotion.Mois = dbo.Mois.UID AND 
                         dbo.ArticlePromotion.TypeCadeaux = dbo.TypeCadeaux.UID LEFT OUTER JOIN
                         dbo.Article ON dbo.ArticlePromotion.Article = dbo.Article.UID LEFT OUTER JOIN
                         dbo.Region ON dbo.Tier.Region = dbo.Region.UID LEFT OUTER JOIN
                         dbo.[Document] ON dbo.PromotionTier.Facture = dbo.[Document].UID LEFT OUTER JOIN
                         dbo.[Document] AS Avoir ON dbo.[Document].UIDDocumentAvoir = Avoir.UID LEFT OUTER JOIN
                         dbo.ASM ON dbo.Tier.ASM = dbo.ASM.UID
WHERE   (dbo.TypeCadeauxFamille.TypeFamille = N'STB') AND (dbo.Annee.Numero = 2020) AND (dbo.Mois.Numero = 6)


Declare @DateConso date
Declare @DateCalc date
Declare @UIDArt uniqueidentifier
DECLARE @PractitionerId uniqueidentifier
DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
select UIDGIFT from @MonthGiftRecap
Order By TypeFamille
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
WHILE @@FETCH_STATUS = 0
BEGIN
Set @UIDArt = (Select CArticle from @MonthGiftRecap where UIDGIFT = @PractitionerId)
Set @DateConso =(Select DateConsommation from @MonthGiftRecap where UIDGIFT = @PractitionerId)
Update @MonthGiftRecap set PrixUnitaire = (Select CoutAchat from CoutArticle where Article = @UIDArt AND DateCalcul = (Select Max(DateCalcul) from CoutArticle where DateCalcul <  @DateConso AND Article = @UIDArt)) where UIDGIFT = @PractitionerId

FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Select * from @MonthGiftRecap

Advertisement

Answer

I think this would get you the PrixUnitaire without needing to go through the cursor. Try adding it to the three queries that feed the temp table:

(
    select top 1 CoutAchat from CoutArticle
    where Article = CArticle AND DateCalcul < DateConsommation
    order by DateCalcul desc        
) as PrixUnitaire
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement