Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem LeftJoin resident Million of rows

Hello,

i'm  trying  to  reload  my qvw using sql  server  and qvd 

so  on my  script i'm  using (left join)  many  times,  based on a principal table (sales) then left join to  other  resident tables.

the  loading is doing okay,  but in the last (left join)  crush ( memory  is going to  256go)  then he stop

i'm  using  millions of rows

in général  my  qvw  looks like 10go

i tried to  move the table  in witch  he crushed to  load it  as  first  and it's going okay! ( there are no  problems with  the script but i think  that's related to optimasation.

would you  please take a look  in the screenshot

any  help  please

saturation mémoire -reload + users +multi  sql opened.png

Ce message a été modifié par : mohamed ali cherif

5 Replies
Gysbert_Wassenaar

You're right. The left join is eating all your machines memory. Can you post the script you're trying to execute?


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hello  Gysbert,

FYI:  the memory  go  up  to 256Go (max of the Server Ram)  on the last left join before :

STORE VenteMpx into 'E:\QVD\QVD\VenteMpx.qvd';

Drop Table VenteMpx;

Please Find below the script

Regards,

the script:

--------------

OCGeant:

LOAD [oc1] ,[DATE DEBUT]

      ,[DATE FIN] ;

SQL SELECT distinct  [OC] as oc1

      ,[DATE DEBUT]

      ,[DATE FIN]

     

FROM [dbo].[OC Geant]

WHERE [Code Article] IS NOT NULL ;

OCMPX:

LOAD [OC] ,DEBUT

      ,FIN ;

SQL SELECT distinct  [OC]

      ,[DATE DEBUT] AS [DEBUT]

      ,[DATE FIN] AS [FIN]

     

FROM [dbo].[OC MPX]

WHERE [Code Article] IS NOT NULL ;

ComparableMPX:

load datecol&[Code Site]&code4 as CompKey ,Comp

;

SQL SELECT DISTINCT [Date Time] as datecol

      ,[Code Site]

      ,[Code Rayon] as code4

      , Comp

  FROM [dbo].[Budget MPX];

Mapcomp:

     Mapping Load CompKey ,Comp Resident ComparableMPX ;

    

    

Permanent :

LOAD [Code Site]

      ,[Code Article]

      ,[PrixPermanentTTC]

      ,[PrixPermanentHT]

      ,[Date debut] AS DateDebutPermanent

      ,[Date fin]AS DateFinPermanent;

SQL SELECT distinct [Code Site]

      ,[Code Article]

      ,[PrixVente]as PrixPermanentTTC

      ,[PrixVenteHT]as PrixPermanentHT

       , ([Date debut]) as [Date debut] , ([Date fin ])as [Date fin]

FROM [dbo].[PrixVentePermanent]

;  

RemisePromo :

LOAD [Code Article]

      ,[Valeur]

      ,[Date debut] AS DateDebutRemisePromo

      ,[Date fin]AS DateFinRemisePromo;

SQL SELECT distinct [Code Article]

      ,[Valeur]

      , ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[RemisePromo]

;  

 

PermanentNationalMPX :

LOAD [Code Article]

      ,[PrixPermanentNationalMPXTTC]

      ,PrixPermanentNationalMPXHT

     ,[Date debut] AS DateDebutPermanentNationalMPX

      ,[Date fin]AS DateFinPermanentNationalMPX;

SQL SELECT distinct [Code Article]

      ,[PrixVente] as PrixPermanentNationalMPXTTC

      ,[PrixVenteHT] as PrixPermanentNationalMPXHT

       , ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[PrixVentePermanentNationalMPX]

;  

     

RemiseHabituelle :

LOAD [Code Article]

      ,[PrixRevientRemHabHT]

      ,PrixRevientRemHabTTC

      ,[Date debut] AS DateDebutRemiseHabituelle

      ,[Date fin]AS DateFinRemiseHabituelle;

SQL SELECT distinct [Code Article]

      ,[PrixRevientHT] as PrixRevientRemHabHT

      ,[PrixRevientTTC] as PrixRevientRemHabTTC

      , ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[RemiseHabituelle]

;

       

RemiseHabituellePromo :

LOAD [Code Article]

      ,[PrixRevientRemHabPromoHT]

      ,PrixRevientRemHabPromoTTC

      ,[Date debut] AS DateDebutRemiseHabituellePromo

      ,[Date fin]AS DateFinRemiseHabituellePromo;

SQL SELECT distinct [Code Article]

      ,[PrixRevientHT] as PrixRevientRemHabPromoHT

      ,[PrixRevientTTC] as PrixRevientRemHabPromoTTC

       , ([Date debut]) as [Date debut] , ([Date fin]) as [Date fin]

FROM [dbo].[RemiseHabituellePromo]

;         

VenteGeanttmp:            

Load

   ([Prix de vente TTC]/Quantité) as PrixUnitaireArticle,

   If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)) as PrixArticleHT,

   If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])as PrixRevientTTC,

   (If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient] as MargeHT,

   ([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) as MargeTTC,

   [Prix de Revient]* Quantité as ValeurRevientHT,

   (If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) * Quantité as ValeurRevientTTC,

   ((If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient]) * Quantité as ValeurMargeHT,

   (([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])))* Quantité as ValeurMargeTTC,

   (If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)))* Quantité as PrixVenteHT,

     ID as IDGeant,

     N°tickets,

     EAN,

     [Code Caisse],

     [Code Site],

     [Prix de vente TTC],

     Quantité,

     [Date Time], 

     DateOfHire as DateOfHireGeant,

     Promo,

     [Code Article],

     [Code Structure],

     Alimentaire,

     [Prix de Revient],

     TVA,

      Date([Date Time])&[Code Site] as DivKey,

      Date([Date Time]) as Date,

  

     REPLACE([N°tickets]&[Code Caisse]&Year([Date Time])&Month([Date Time])&Day([Date Time]), ' ', '')as nbrClientGeant,

     KeyVenteSite,

     code4 ,

     datecol

    ;

SQL SELECT    V.[N°tickets], V.[Code Caisse], V.[Code Site], V.[Prix de vente TTC] ,

                      V.Quantité , DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time], V.ID, V.DateOfHire,

                     V.EAN ,V.[Code Article], V.TVA , V.Alimentaire , V.[Code Structure], V.[Prix de Revient] ,

                     

                     

                    Case when  V.[Code Article] in   (SELECT   [Code Article]

   FROM     [Promotion Geant] 

   where  CONVERT(DATE, V.[Date Time]) between

          [Date Debut Promo] and  [Date Fin Promo])then 1 else 0 end as Promo,

    KeyVenteSite,code4 ,

     datecol

                     

FROM         dbo.[Vente Geant] V

where year([Date Time])=year(getdate())

;

STORE VenteGeanttmp into 'E:\QVD\QVD\VenteGeantFULL.qvd';

Drop Table VenteGeanttmp;

VenteGeant: 

LOAD PrixUnitaireArticle,

     PrixArticleHT,

     PrixRevientTTC,

     MargeHT,

     MargeTTC,

     ValeurRevientHT,

     ValeurRevientTTC,

     ValeurMargeHT,

     ValeurMargeTTC,

     PrixVenteHT,

     IDGeant,

     N°tickets,

     EAN,

     [Code Caisse],

     [Code Site],

     [Prix de vente TTC],

     Quantité,

     [Date Time],

     DateOfHireGeant,

     Promo,

     [Code Article],

     [Code Structure],

     Alimentaire,

     [Prix de Revient],

     TVA,

     DivKey,

     nbrClientGeant,

     KeyVenteSite,

     code4 ,

     Date(Date#(Date,'YYYY-MM-DD'),'YYYYMMDD') as Date ,

     Date#(Date,'YYYY-MM-DD') as DateTest ,

    'O' as Comp,

     datecol

FROM

E:\QVD\QVD\VenteGeantFULL.qvd

(qvd)

Where (isnull([Code Article]) = 0) and (isnull([Code Structure]) = 0) and (isnull([Prix de Revient])=0);

LEFT  JOIN(VenteGeant)

IntervalMatch (datecol)

LOAD DISTINCT [DATE DEBUT]

      ,[DATE FIN]

resident      OCGeant

      ;

      LEFT JOIN (VenteGeant)

LOAD *

RESIDENT OCGeant;

LEFT  JOIN(VenteGeant)

IntervalMatch (datecol, [Code Site], [Code Article])

LOAD DISTINCT DateDebutPermanent, DateFinPermanent, [Code Site], [Code Article]

resident      Permanent

      ;

       LEFT JOIN (VenteGeant)

LOAD *

RESIDENT Permanent;

LEFT  JOIN(VenteGeant)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemisePromo,DateFinRemisePromo,[Code Article]

resident      RemisePromo  ;

LEFT JOIN (VenteGeant)

LOAD *

RESIDENT RemisePromo;

STORE VenteGeant into 'E:\QVD\QVD\VenteGeant.qvd';

Drop Table VenteGeant;

VenteMpxtmp:            

Load

   ([Prix de vente TTC]/Quantité) as PrixUnitaireArticle,

   If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)) as PrixArticleHT,

   If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])as PrixRevientTTC,

   (If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient] as MargeHT,

   ([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) as MargeTTC,

   [Prix de Revient]* Quantité as ValeurRevientHT,

   (If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])) * Quantité as ValeurRevientTTC,

   ((If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité))) - [Prix de Revient]) * Quantité as ValeurMargeHT,

   (([Prix de vente TTC]/Quantité)- ( If(Alimentaire=1,[Prix de Revient]*(1+TVA/100),[Prix de Revient])))* Quantité as ValeurMargeTTC,

   (If(Alimentaire=1 ,([Prix de vente TTC]/Quantité)/(1+TVA/100),([Prix de vente TTC]/Quantité)))* Quantité as PrixVenteHT,

     ID as IDMPX,

     N°tickets,

     EAN,

     [Code Caisse],

     [Code Site],

     [Prix de vente TTC],

     Quantité,

     [Date Time],

     DateOfHire as DateOfHireMPX,

     Promo,

     [Code Article],

     [Code Structure],

     Alimentaire,

     [Prix de Revient], 

     TVA,

     Date([Date Time])&[Code Site] as DivKey,

     Date([Date Time]) as Date,

     //AutoNumber(N°tickets&[Code Site]&[Code Caisse]&Date([Date Time])) as nbrClientMPX,

     REPLACE([N°tickets]&[Code Site]&[Code Caisse]&Year([Date Time])&Month([Date Time])&Day([Date Time]), ' ', '')as nbrClientMPX,

     KeyVenteSite,

     code4 ,

     datecol

     ;

SQL SELECT     V.[N°tickets], V.[Code Caisse], V.[Code Site], V.[Prix de vente TTC] ,

                      V.Quantité , DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time], V.ID, V.DateOfHire, V.Promo,

                     V.EAN ,V.[Code Article], V.TVA , V.Alimentaire , V.[Code Structure],V.[Prix de Revient] ,KeyVenteSite

                      ,

     code4 ,

     datecol

FROM         dbo.[Vente MPX] V

where year([Date Time])=year(getdate())

;

STORE VenteMpxtmp into 'E:\QVD\QVD\VenteMpxFull.qvd';

Drop Table VenteMpxtmp;

VenteMpx: 

LOAD PrixUnitaireArticle,

     PrixArticleHT,

     PrixRevientTTC,

     MargeHT,

     MargeTTC,

     ValeurRevientHT,

     ValeurRevientTTC,

     ValeurMargeHT,

     ValeurMargeTTC,

     PrixVenteHT,

     N°tickets,

     EAN,

     [Code Caisse],

     [Code Site],

     [Prix de vente TTC],

     Quantité,

     [Date Time],

     Promo,

     [Code Article],

     [Code Structure],

     Alimentaire,

     [Prix de Revient],

     TVA,

     DivKey,

     IDMPX,

     DateOfHireMPX,

     nbrClientMPX,

     KeyVenteSite,

     code4 ,

     datecol,

     Date(Date#(Date,'YYYY-MM-DD'),'YYYYMMDD') as Date ,

     Date#(Date,'YYYY-MM-DD') as DateTest ,

     ApplyMap('MapComp', datecol&[Code Site]&code4 , 'O') as Comp

FROM

E:\QVD\QVD\VenteMpxFull.qvd

(qvd)

Where (isnull([Code Article]) = 0) and (isnull([Code Structure]) = 0) and (isnull([Prix de Revient])=0);

LEFT  JOIN(VenteMpx)

IntervalMatch (datecol)

LOAD DISTINCT DEBUT

      ,FIN

resident      OCMPX            

      ;

      LEFT JOIN (VenteMpx)

LOAD *

RESIDENT OCMPX;

LEFT  JOIN(VenteMpx)

IntervalMatch (datecol, [Code Site], [Code Article])

LOAD DISTINCT DateDebutPermanent, DateFinPermanent, [Code Site], [Code Article]

resident      Permanent

      ;

       LEFT JOIN (VenteMpx)

LOAD *

RESIDENT Permanent;

LEFT  JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemisePromo,DateFinRemisePromo,[Code Article]

resident      RemisePromo

      ;

        LEFT JOIN (VenteMpx)

LOAD *

RESIDENT RemisePromo;

LEFT  JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutPermanentNationalMPX, DateFinPermanentNationalMPX,[Code Article]

resident      PermanentNationalMPX

      ;

        LEFT JOIN (VenteMpx)

LOAD *

RESIDENT PermanentNationalMPX;

LEFT  JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemiseHabituelle,DateFinRemiseHabituelle,[Code Article]

resident      RemiseHabituelle

      ;

         LEFT JOIN (VenteMpx)

LOAD *

RESIDENT RemiseHabituelle;

LEFT  JOIN(VenteMpx)

IntervalMatch (datecol,[Code Article])

LOAD DISTINCT DateDebutRemiseHabituellePromo, DateFinRemiseHabituellePromo,[Code Article]

resident      RemiseHabituellePromo

      ;

          LEFT JOIN (VenteMpx)

LOAD *

RESIDENT RemiseHabituellePromo;

STORE VenteMpx into 'E:\QVD\QVD\VenteMpx.qvd';

Drop Table VenteMpx;

BudgetMpxtmp:            

Load

  [idObjCA]

      ,[CA TTC]

      ,[VAL MRG TTC]

      ,[CA HT]

      ,[VAL MRG HT]

      ,[Rayon]

      ,[Date Time]

      ,[Code Site]

      ,[Code Rayon]

      ,[Code Structure]

      ,Comp

    ;

SQL SELECT  [idObjCA]

      ,[CA TTC]

      ,[VAL MRG TTC]

      ,[CA HT]

      ,[VAL MRG HT]

      ,[Rayon]

      ,DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time]

      ,[Code Site]

      ,[Code Rayon]

      ,[Code Structure]

      ,Comp

      FROM [QlikDataWarehouse].[dbo].[Budget MPX];

STORE BudgetMpxtmp into 'E:\QVD\QVD\BudgetMpx.qvd';

Drop Table BudgetMpxtmp;

BudgetMpx: 

LOAD [idObjCA]

      ,[CA TTC]

      ,[VAL MRG TTC]

      ,[CA HT]

      ,[VAL MRG HT]

      ,[Rayon]

      ,[Date Time]

      ,[Code Site]

      ,[Code Rayon]

      ,[Code Structure]

      ,Comp

FROM

'E:\QVD\BudgetMpx.qvd'

(qvd)

;

STORE BudgetMpx into 'E:\QVD\QVD\BudgetMpx.qvd';

Drop Table BudgetMpx;

BudgetGeanttmp:            

Load

  [idObjCA]

      ,[CA TTC]

      ,[VAL MRG TTC]

      ,[CA HT]

      ,[VAL MRG HT]

      ,[Rayon]

      ,[Date Time]

      ,[Code Site]

      ,[Code Rayon]

   ,[Code Structure]

   ,Comp

    ;

SQL SELECT  [idObjCA]

      ,[CA TTC]

      ,[VAL MRG TTC]

      ,[CA HT]

      ,[VAL MRG HT]

      ,[Rayon]

      ,DATEADD(hour, DATEDIFF(hour, 0, [Date Time]), 0) as [Date Time]

      ,[Code Site]

      ,[Code Rayon]

       ,[Code Structure]

      ,Comp

      FROM [QlikDataWarehouse].[dbo].[Budget Geant]

  ;

STORE BudgetGeanttmp into 'E:\QVD\QVD\BudgetGeant.qvd';

Drop Table BudgetGeanttmp;

BudgetGeant: 

LOAD [idObjCA]

      ,[CA TTC]

      ,[VAL MRG TTC]

      ,[CA HT]

      ,[VAL MRG HT]

      ,[Rayon]

      ,[Date Time]

      ,[Code Site]

      ,[Code Rayon]

      ,[Code Structure]

      ,Comp

FROM

'E:\QVD\QVD\BudgetGeant.qvd'

(qvd)

;

STORE BudgetGeant into 'E:\QVD\QVD\BudgetGeant.qvd';

Drop Table BudgetGeant;

drop table OCMPX;

drop table ComparableMPX;

drop table RemiseHabituelle;

drop table RemiseHabituellePromo;

drop table PermanentNationalMPX;

drop table RemisePromo;

drop table Permanent;

drop table OCGeant;

Casse:            

load  [Code Site],[Code Article],[Date] as [Date Time]

     ,casse

     ,QuantiteCasse

      ,[Code Structure]

;

SQL SELECT CASE WHEN [Code Site]IN( 9001 ,9002) then 90012 else [Code Site]  end as  [Code Site]

      ,[Code Article]

      ,[Quantite]*PrixRevientTTC as casse

      ,Quantite as QuantiteCasse

      ,[Date]

      ,[Code Structure]

  FROM [dbo].[Casse];

STORE Casse into 'E:\QVD\QVD\Casse.qvd';

Drop Table Casse;

Ajust :

load [Code Site],[Code Article],[Date] as [Date Time]

     ,ajust

     ,QuantiteAjust

      ,[Code Structure]

;

SQL SELECT  CASE WHEN [Code Site]IN( 9001 ,9002) then 90012 else [Code Site]  end as  [Code Site]

      ,[Code Article]

      ,[Quantite]*PrixRevientTTC as ajust

      ,Quantite as QuantiteAjust

      ,[Date]

      ,[Code Structure]

  FROM [dbo].[ajust];

  STORE Ajust into 'E:\QVD\QVD\Ajust.qvd';

Drop Table Ajust;

Anonymous
Not applicable
Author

hi gysbert,

would you  please take  a look  and help  me

ty

Anil_Babu_Samineni

Can you share Log file, please? It might cause time spirit using Left Join. Try to share Log file with us. Thanks

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Leave the script as-is (problematic JOIN at the end) but run it in the debugger and select a limited load on the left (for example 1000 rows). The theoretical maximum of records to be produced by the last JOIN is now 1 million. Create table boxes to check whether the JOIN has peen correctly programmed. You're probably lacking a key field and thereby creating some sort of cartesian product.

Peter