Database Reference
In-Depth Information
or, the blocks in a slice of multiple sparse selections:
"FIX (Jan, Budget, @IDESCENDANTS (Product), @IDESCENDANTS (East))"
4 .7. 2
Whoa! My Script Is Slow
one of the fun things about being a consultant is being called in when things are really
bad. really bad usually translates to “we have this calculation script that absolutely must
run in fewer than 10 seconds, but it is taking 10 minutes instead.” If I want to have the cli-
ent throw me out the door, I can always suggest starting with a database design session. In
most cases, unless the database design is total junk, the best approach is to try and tweak
the script. The mistake that most make is to start rewriting the script before understand-
ing the database design, the goal of the script, and the bottlenecks. here is my approach:
1. review the outline. make note of the dense dimensions and the block size.
overly large block sizes need special analysis.
2. gain a complete understanding of the goal of the script in business terms. often
times a less experienced developer takes the long way to achieve the intended
results. There may be an easier, faster way.
3. review the script, but do not start coding just yet. BSo calculation performance
is highly dependent on database design, block size, the number of data blocks,
and the distribution of the data. I have yet to meet the person who can consis-
tently identify the bottleneck in most scripts. There is no worse feeling than
spending 20 minutes rewriting a chunk of code only to find out that it ran in
1 second before the changes anyway.
4. Add “Set msg Summary” to the script and run it. make note of the elapsed time
and the sparse blocks read and written. Do not worry about the dense calcula-
tions for now.
5. Break the script into pieces that can execute individually. I like to create four scripts
if possible. Do not focus on the number of lines in each script. In most cases, there
is no relationship between the number of lines and the execution elapsed time.
6. run each script. Do not be too concerned that the calculations are correct. The
purpose is to find the bottlenecks in the code and not the calculated totals.
Capture the total time of each script and the block reads and writes.
7. It is not uncommon for one or two of the scripts to consume the bulk of the run
time. If so, then this is where the tuning efforts should be focused.
8. Break the long running scripts into even smaller scripts. Sometimes an indi-
vidual function is the culprit.
Let us work through an example. This is a nonsensical calculation script and the
dimensionality is irrelevant for our analysis.
--- beginning of script #1 ---
FIX(@DESCENDANTS("Segment 1"), "Budget", "Version_1", "FY12", "New
Product 1")
("Volume"="Volume"*1.10;
"Price"="Price"*1.10;
"Other Revenue"="Other Revenue"*1.10;
"COGS Rate"="COGS Rate"*1.10;
