Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

adoteykwame
Contributor

Reduce load time

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!

9 Replies
Not applicable

Re: Reduce load time

Hi,

can you please attach sample file ?

adoteykwame
Contributor

Re: Reduce load time

TABLEA:

LOAD

productID

B

C

Date

FROM xxx

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

TABLEB:

LEFT JOIN

LOAD

productID

E

F

G

H

I

J

H

FROM XXX

Not applicable

Re: Reduce load time

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

B

C

Date

FROM xxx

TABLEB:

LEFT JOIN(TABLEA)

LOAD

productID

E

F

G

H

I

J

H

FROM XXX

Output:

Load  *

Resident TABLEA

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

Drop table TABLEA;

Best of luck

Re: Reduce load time

Can you upload the Document Log?

-Rob

Re: Reduce load time

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

adoteykwame
Contributor

Re: Reduce load time

Hi,

Please find attached.

Re: Reduce load time

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

adoteykwame
Contributor

Re: Reduce load time

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

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

Community Browser