Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnpaul
Partner - Creator
Partner - Creator

Slowly building a QVD to avoid SQL server performance degredation

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?

1 Solution

Accepted Solutions
erichshiino
Partner - Master
Partner - Master

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

View solution in original post

2 Replies
erichshiino
Partner - Master
Partner - Master

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

johnpaul
Partner - Creator
Partner - Creator
Author

Thanks,

That worked OK.

The method I used was the following one:

let nRows = NoOfRows('Table') ;