topic Re: Why drop table after store qvd statment in QlikView

Why drop table after store qvd statment

vvvvvvizard — Wed, 22 Mar 2017 12:09:29 GMT

We have a tier 2 type environment , the first set of qvw load the data and transform it and store qvd's

The second set of qvw , load in the qvd's

the relationship is one to one , one qvw loads in all the qvd's from the qvw that had the store qvd statements

Is it necessary to drop all tables after the store qvd statements . Our store statements are the last statements in the model to execute then the script ends , is qlikview not smart enough to drop the data out of memory once the model finishes executing .

Now if i press ctr + t , i dont see the table structure / joins which makes life a little bit more difficult

Re: Why drop table after store qvd statment

vinieme12 — Wed, 22 Mar 2017 12:36:44 GMT

Because qlikview doesn't know how to read minds.

People use Qlikview store command for many reason's, some are below

to create a Backup/ Archive

Or to re-use again within same document

Or to actually just create QVD's

Re: Why drop table after store qvd statment

vvvvvvizard — Wed, 22 Mar 2017 12:43:17 GMT

In our environment we were asked to add drop table statements after the store qvd statements .

My question is what is the benefit of doing this ? Is it for performance

Re: Why drop table after store qvd statment

vvvvvvizard — Wed, 22 Mar 2017 12:47:43 GMT

All the model are were setup exactly like this

Tab N

Load ...

Tab N+1

Load ...

Tab 100

Load ...

store table 1 into

store tabe 2 into

...... until

store table 100 into

exit script

this worked fine

Now we were told to add in drop table statement like this

Tab1

Load ...

Tab2

Load ...

store table 1 into

store tabe 2 into

store table 100 into

......

drop tables 1,2,3,4,5,6 ... 100

Exit script

Whats the benefit of doing this ?

Re: Why drop table after store qvd statment

swuehl — Wed, 22 Mar 2017 12:48:55 GMT

Could be to keep the QVW that stores the QVW slim, not using more ressources than necessary.

(edit: then I would even use DROP directly after the STORE and load /store one table after the other)

Note that if you don't drop the tables, your data model could get messy with a lot of synthetic keys, unless you take care of the modelling already when doing the initial data extraction (edit: and these tables and syn keys might get huge...)

Re: Why drop table after store qvd statment

vinieme12 — Wed, 22 Mar 2017 12:56:46 GMT

It's your choice if you don't want to drop the table after load.

What will happen is

1)the size of the QVW will keep on increasing as you keep loading and storing the table's

2) Your QVW will eat up much space on the drive for the same data you just stored as QVD!

3) At the end of the all your table load qlikview will try to create associations between the tables you have loaded, which can take long time depending on number of fields.

4)the next time you open the app, it'll open very slowly (because all that unnecessary data)

... but do you really need all those tables in a single QVW?

Re: Why drop table after store qvd statment

vvvvvvizard — Wed, 22 Mar 2017 13:23:34 GMT

We do , do the modelling during ETL , wonder how much slimmer it makes our models / vs the tradeoff of adding more dev/ troubleshooting time , opening one model to see the table structure , another to see the mapping of table structure to sql field , where i could of just opened one model and make changes .

Re: Why drop table after store qvd statment

vvvvvvizard — Wed, 22 Mar 2017 14:30:46 GMT

1 yes it will keep increasing , with or without drop table statement

2 it will with or without the drop statement

3 This to me is a reason to use the drop statement , do the associations once in the front end model instead of twice

Its not really 100 tables about 35 , il reduce it

Re: Why drop table after store qvd statment

swuehl — Wed, 22 Mar 2017 14:40:06 GMT

You may also want to look at this discussion (and more using the search in the forum...)

QlikView Project Tiers/Layers