sql—将游标添加到此查询后，执行时间从10s变为2m.10s

yeotifhr  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(199)

我正在处理这个查询，在添加了cursor块之后，执行时间从10秒变为2米。
有没有办法缩短执行时间？查询是对同一个表进行3次选择的结果，但条件不同。
我需要光标，因为我应该从一个表中获取一篇文章的价格，该表包含在第一次查询中无法获取的每个日期的价格。

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
sqlsql-server

1条答案

按热度按时间
dphi5xsq

dphi5xsq1#

我想这会让你 PrixUnitaire 不需要通过光标。尝试将其添加到提供temp表的三个查询中：

(
    select top 1 CoutAchat from CoutArticle
    where Article = CArticle AND DateCalcul < DateConsommation
    order by DateCalcul desc        
) as PrixUnitaire
赞(0）分享  回复(0）举报  2021-07-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com