topic Re: Reduce load time in QlikView

Reduce load time

Anonymous — Tue, 26 Mar 2013 12:01:57 GMT

Hello,

TableA loads with a where clause 'where date > '1/1/2011'' and TableB is join to TableA. TableB is huge and linked to TableA by ProductID. Now i would only want to load only relevent fields to reduce the load time.Any help please!

Re: Reduce load time

Tue, 26 Mar 2013 12:05:17 GMT

Hi,

can you please attach sample file ?

Re: Reduce load time

Anonymous — Tue, 26 Mar 2013 12:09:38 GMT

TABLEA:

LOAD

productID

Date

FROM xxx

WHERE where date > '1/1/2011''

TABLEB:

LEFT JOIN

LOAD

productID

FROM XXX

Re: Reduce load time

Tue, 26 Mar 2013 13:34:19 GMT

Hi,

In qlikview left join with where condition it will take more time to reload.

Try this below script, it might improve the performance. Try this and let me know

TABLEA:

LOAD

productID

Date

FROM xxx

TABLEB:

LEFT JOIN(TABLEA)

LOAD

productID

FROM XXX

Output:

Load *

Resident TABLEA

where date > '1/1/2011'';

Drop table TABLEA;

Best of luck

Re: Reduce load time

rwunderlich — Tue, 26 Mar 2013 19:50:01 GMT

Can you upload the Document Log?

-Rob

Re: Reduce load time

rwunderlich — Wed, 27 Mar 2013 16:28:00 GMT

Kind of hard to read like that. Can you post it as an attachment instead? Use the "Use Advanced Editor" link to be able to attach files.

-Rob

Re: Reduce load time

Anonymous — Wed, 27 Mar 2013 16:54:45 GMT

Hi,

Please find attached.

Re: Reduce load time

rwunderlich — Thu, 28 Mar 2013 05:32:46 GMT

If I understand correctly, you concatenate a few fact loads and then want to join rows from table PUB.uwm120 on field [Policy Number]. uwm120 is large so it takes a long time to pull down all the rows and you only need a subset of rows for the join.

You could move the join into the SQL so the join happens at the DB Server. That should speed things up. Since you are concatenating unlike columns, it may be easiest to add the umw120 join to each of the fact SQL SELECTs.

-Rob

http://masterssummit.com

Re: Reduce load time

Anonymous — Thu, 28 Mar 2013 09:14:39 GMT

Hi Rob,

I do not have access to the DB, only interfacing. Could you please illustrate the latter part "Since you are concatenating unlike columns, it may be easiest to add the umw120 join to each of the fact SQL SELECTs".

Thanks

Re: Reduce load time

rwunderlich — Thu, 28 Mar 2013 17:37:16 GMT

Instead of a final join, do the join in SQL with each select of the fact table. Like this:

CONCATENATE (FACT)

LOAD

blah, blah

;

SQL SELECT *

FROM

PUB.clm100 A

LEFT JOIN PUB.uwm120 B

A.[Policy Number] = B.[Policy Number]

WHERE trndat > '12/31/2009'

Note that the field list "*' above should be modified to include only the fields you want, so you are not bringing all fields into the LOAD statement.

-Rob