topic Slowly building a QVD to avoid SQL server performance degredation in QlikView

Slowly building a QVD to avoid SQL server performance degredation

johnpaul — Tue, 09 Oct 2012 07:23:25 GMT

My application uses QVD's to store an audit trail.

The issue is that on the first load, the SQL query will often cause the SQL server to grind to a halt in production. In the dev environment with less records, this isn't an issue.

Once the QVD is built, then the subsequent loads will only add to the QVD what has changed, which is all good.

So one option is to use the SELECT TOP X records to load only the first say 10,000 records at a time. These records are then written to the QVD and the record counter is updated, so on the next reload, the next X records are loaded.

This works OK, but I would need to ask my user to keep hitting the reload command.

(the second option is to rewrite the SQL query and use temporary tables etc.)

Is there a way to do this in a For Loop?

What I would need is to have is a variable which will tell me how many records have been returned by the query.

I have tried the

load field, count(field) as NumberOfRecordsLoaded

Resident Table

Group by field;

but that doesn't seem to work, as the NumberOfRecordsLoaded doesn't seem to get a value.

Anyone done something similar to this?

Re: Slowly building a QVD to avoid SQL server performance degredation

erichshiino — Wed, 10 Oct 2012 03:50:15 GMT

Hi, John

Instead of

load field, count(field) as NumberOfRecordsLoaded

Resident Table

Group by field;

you could try this:

let nRows = NoOfRows('Table') ;

Or maybe your load would be like this:

Temp:

load count(field) as NumberOfRecordsLoaded

Resident Table;

nRows = peek('NumberOfRecordsLoaded');

drop table Temp

If you write you for... loop, between steps you could add some sleep time to give some time and avoid using all database resources.

Hope this helps

Erich

Re: Slowly building a QVD to avoid SQL server performance degredation

johnpaul — Wed, 10 Oct 2012 09:37:34 GMT

Thanks,

That worked OK.

The method I used was the following one:

let nRows = NoOfRows('Table') ;