General question about store as .qvd

[stevelord]

Hi, I have a table load with numerous calculated fields built by sum(if()) statements and grouped by the other fields.  There are subsequent resident loads in which formulas work with the calculated fields to populate empty values in some fields based on the presence of related data in other fields.  My laptop has 8GB of RAM and it loads fine there, but the server has 4GB RAM and is being crashed by the script.  I believe I could break it into two scripts, have one go as far as building and transforming the key table, then saving it as a QVD, then have the remaining script run separately to populated and stratify that table.

I dabbled with store as qvd before, but it wasn't working so I wandered off.  Can anyone give a general overview of the best practices layout and syntax (or a good current hyperlink) for breaking up a large server-crashing script into two or three more managable scripts using store as qvd?  Would I be making separate QVWs, or doing something internal to the one script that allows QlikView and the server to catch a breath while loading the one script?

Thanks!

Here is an outline of the script I have:

ClientAccountName:

Loads client data

Offices:

Loads data about internal structure of client

Users:

Loads users and office data

Biometrics:

Loads users and biometrics data - this is the one with a few dozen sum(if()) and the group by statement.  My script pauses here for almost a minute to transform the table, then zooms on through what's left.  I'd like to make a qvd off of this and have the remaining script look at that like a regular qvd file.

Biometrics1-5:

Five different biometric tables running formulas on Biometrics fields to populate empty values where possible (if needed data is present in other fields)

These go by pretty fast

Attributes1-2:

A couple more tables extracting gender values from biometrics testnames specific to gender that have values in them

Riskbanding1:

A table to stratify biometric values as normal or high with a bunch of dual if statements

NumericRiskBanding:

Same as RiskBanding except it gives number messages instead of text messages (1=normal, 2 =at risk)

-Steve

[swuehl]

You said, you tried using QVDs, but it wasn't working. That's not a good error description.

It would be easier to help you with a concrete setting / syntax rather than repeating what you can find in the HELP or reference manual. If possible, post some of your script code.

Besides this, I would suggest that you first try to understand what actually crashes your script on the server. At what point does the script crash? Does QV maybe create some (probably unwanted) synthetic key tables after loading your resident tables?

[stevelord]

Server crashes on the biometrics table where I have a lot of sum(if()) statements creating numerous new fields out of a couple of existing fields, and groups them by the remaining fields. The script runs smoothly on my machine except for a 30 second pause on that biometrics table (it zooms through the first few tables in a few seconds and the subsequent resident loads/calculated fields in 1-2 seconds each on my machine.)

No synthetic keys or loops, using drop table after each resident load to move forward with only one (modified) biometrics table. Starting point is four qvd files containing raw data taken from the database.

I was just fishing for fresh probably random suggestions/best practice to help optimize the script so it can run on machines with less RAM than mine. It runs smoothly on 8GB RAM and does what it’s supposed to do otherwise (been thoroughly vetted over a few months.) The reference manual has been little help on simpler issues in the past, but I’ll give it another look. Workarounds might be get more RAM for the server, rearrange its processing schedule, or I can transform data/store as qvd on my machine and put that file next to the main database or something. J We’re evolving.

-Steve