topic Re: optimize load query in QlikView

optimize load query

Anonymous — Fri, 16 Oct 2015 08:36:43 GMT

How could I optimize the following query when loading

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 ]

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

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

KeyVenteSite,(select top 1 [OC]

from [dbo].[OC ] r

where [Date Time] between r.[DATE DEBUT] and r.[DATE FIN]

)as oc

,(select top 1 t.comp

from [dbo].[Budget ] t

where t.[Code Site]=[Code Site]

and t.[Code Rayon] =substring([Code Structure],1,4)

and t.[Date Time]=convert(date,[Date Time])

)

as[Comp]

FROM dbo.[Vente ] V

Where (V.[Code Article] is not null) and (V.[Code Structure] is not null) and (V.[Prix de Revient] is not null)

It took hours .

Re: optimize load query

maleksafa — Fri, 16 Oct 2015 08:54:30 GMT

this is an sql thing and not qlikview, what you can do is use the sql tuning advisor to optimize the query by creating indexes on the tables.

i have noticed some sub queries in your query and lot of convert in your where conditions which consume the most, try removing those convert by adding converted fields to your tables.

Re: optimize load query

sujeetsingh — Fri, 16 Oct 2015 08:55:46 GMT

You can break the query in sub parts resident load in Qlikview and then have flags with inner joins to trim the data and achieve the required output.

Re: optimize load query

Anonymous — Fri, 16 Oct 2015 08:59:37 GMT

How to do it please ?

Re: optimize load query

Anonymous — Fri, 28 Sep 2018 20:26:31 GMT

Yes you can follow the Sujit's approach.Extract all the tables ([Promotion ] ,[OC ],[Budget ],[Vente ] ) from the source and then do necessary joining among tables in QlikView. Please share sample data so that I can do the joining for you.