ASP Speed Tricks updated

[Shailesh Humbad]

I posted this article I wrote a couple weeks ago, but
I've completely updated it, with new sections on
the performance of 'bursting' GetRows and GetString.

If you're interested:

http://www.somacon.com/aspdocs/

 

[Ray at]

Not sure about anyone else, but I cannot resolve that domain, with our
without the www. I'm hitting the root DNS servers for my DNS updates, so I
/think/ that I typically have up to date DNS resolution. I believe that I
was able to resolve it before though, as I remember when you posted the
article the first time.

Ray at home

 

[Chris Barber]

Works from the UK (now).

Chris.

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
Not sure about anyone else, but I cannot resolve that domain, with our
without the www. I'm hitting the root DNS servers for my DNS updates, so I
/think/ that I typically have up to date DNS resolution. I believe that I
was able to resolve it before though, as I remember when you posted the
article the first time.

Ray at home

 

[Chris Hohmann]

I posted this article I wrote a couple weeks ago, but
I've completely updated it, with new sections on
the performance of 'bursting' GetRows and GetString.

If you're interested:

http://www.somacon.com/aspdocs/

Did you ever have an opportunity to review the union query solution for
complex tables? My findings showed it to be fourteen times (14x) faster
than the ComplexTable3 method. That's not a misprint. Fourteen times
faster! Here's the original message:

http://www.google.com/[email protected]

 

[Guinness Mann]

I posted this article I wrote a couple weeks ago, but
I've completely updated it, with new sections on
the performance of 'bursting' GetRows and GetString.
http://www.somacon.com/aspdocs/

At this point here:

' Set up field references after opening recordset
Set objField0 = objRS(0)
Set objField1 = objRS(1)
Set objField2 = objRS(2)
Set objField3 = objRS(3)

Couldn't you have gone back to:

' Set up field references after opening recordset
Set objField0 = objRS("Field0")
Set objField1 = objRS("Field1")
Set objField2 = objRS("Field2")
Set objField3 = objRS("Field3")

And gained back the advantges of readibilty and maintainability?

-- Guinness

 

[Shailesh Humbad]

Yes, you're exactly right. In my production code, I had been doing the
same thing. The only drawback is the slight speed penalty of using
string lookup when setting the references. I'll change the example.

Thanks,
Shailesh

 

[Chris Hohmann]

In a hypothetical Public Companies and Officers report, the sub-query
would be selecting a list of Officers for each Public Company. It is
not possible in a UNION to apply a criteria to the sub-query based on
each result of the primary query. It might be possible with some
procedural logic (like TSQL or PL/SQL), but that is outside the scope of
the article and this newsgroup. Each query of the UNION is considered
independent, and would need its own criteria.

However, I do believe that this technique can work in some
circumstances, and it does increase speed tremendously. I just can't
think of an example where it would be useful. In the examples I can
think of, it would be easier to use GROUP BY rather than UNION. I was
just working today for my client on a report that was begging to be done
with a UNION. I'd describe it, but it is a very specific case, and I
haven't solved it completely yet. Maybe you have a good example
already? I'd like to include the UNION technique if a convincing
example can be nailed down.

Shailesh

[tblCompany]
ID:AutoNumber
Name:Text
Symbol:Text
Exchange:Text
Industry:Text

[tblOfficer]
ID:AutoNumber
Name:Text
Title:Text
CompanyID:Number

[qryComplexReport]
SELECT
R.Column1,
R.Column2,
R.Column3,
R.Column4
FROM
(
SELECT
Name AS Column0,
Name AS Column1,
Symbol AS Column2,
Exchange AS Column3,
Industry AS Column4
FROM
tblCompany
UNION ALL
SELECT
C.Name,
O.Name,
O.Title,
NULL,
NULL
FROM
tblCompany AS C,
tblOfficer AS O
WHERE
O.CompanyID = C.ID
ORDER BY
Column0,
Column3 DESC,
Column1
) AS R

 

[Shailesh Humbad]

I wanted to try a real example, so I tried printing Orders from the
Northwind sample database. The "Orders" table contains each order, and
"Order Details" contains the items for each order. I printed one line
detailing the order, and then multiple lines for each item in that
order. There are about 800 orders and 2000 items, so 2800 lines to be
printed. Using the UNION ALL query took 0.31 seconds. Using a prepared
command object and second recordset as in complextable3.asp took 2.77
seconds. Cool! Looks like I'll have to make another update to the
article. One thing that needs to be analyzed is what happens to
performance if the number of fields needed from each query is very
different. For example, suppose I needed 15 fields for each Order, but
only 3 fields for each item. The record for each item would contain 12
blank fields. I suspect that this won't be too much of a problem though.

Shailesh

 

[Chris Hohmann]

I wanted to try a real example, so I tried printing Orders from the
Northwind sample database. The "Orders" table contains each order, and
"Order Details" contains the items for each order. I printed one line
detailing the order, and then multiple lines for each item in that
order. There are about 800 orders and 2000 items, so 2800 lines to be
printed. Using the UNION ALL query took 0.31 seconds. Using a prepared
command object and second recordset as in complextable3.asp took 2.77
seconds. Cool! Looks like I'll have to make another update to the
article. One thing that needs to be analyzed is what happens to
performance if the number of fields needed from each query is very
different. For example, suppose I needed 15 fields for each Order, but
only 3 fields for each item. The record for each item would contain 12
blank fields. I suspect that this won't be too much of a problem though.

Shailesh

Empirical testing would tell the tale. I will however throw out some
conjecture. If you're using an empty strings or NULL as filler and the
data profile is correct (VARCHAR vs. CHAR in SQL Server), then from a
size standpoint, very little additional data is rendered. The most
important thing to note here is that the performance advantage of the
union method over multiple recordsets improves as the result set is
scaled. Your test of 2000/800 records demonstrates a performance factor
of 9x. If you scale your result set to 20000/8000 you would see a
startling jump in that performance factor. In fact, it's likely that the
multiple recordset method would timeout at those numbers while the union
method would happily go about its business. There's an "economies of
scale" issue that comes into play for the union method. It is beyond the
scope of this thread, but the very indexes you referred to in your prior
post would allow the union query to perform better on a record for
record basis, for larger result sets. Finally, an issue that has not be
touched upon is the resource utilization footprint of each method.
Specifically, memory usage, threads, handles, registers, etc... Again,
empirical testing would tell the tale, but the disparity between the
union method and the multiple recordset method would be even more
extreme than simple performance. Consider the fact that in the 2000/800
example, 801 recordsets are being instantiated compared to the one (1)
recordset instantiated in the union method. 800 x [resource overhead for
a recordset]. Scary.

HTH
-Chris

 

[Shailesh]

Thanks for your suggestion of trying the UNION query. I understand
all the points you made about scalability and resource utilization. I
have revised my article and posted a section on this technique:

http://www.somacon.com/aspdocs/

The result is that the union query method is 10 times faster than the
sub-query method for a 3000 record ASP report.

I thought you might like to hear about how I implemented the technique
in a real program. My current client has a "patient ledger" function
in their medical billing software, which is built on ASP/IIS. This
requires records from three different tables to be displayed: charges,
payments, and invoices. If you can believe it, I originally used
three separate queries to retrieve the records into a redimmed
VBScript array. Then I used a VBScript implementation of Quicksort to
order the records. This version was overall slow, and did not
correctly order the payment lines in relation to the invoice lines.

So upon revisiting the issue to create a similar "patient statement"
function, I spent several hours analyzing the relationships between
the resulting records. At first, I thought more complex sorting would
be needed, and for awhile started implementing my own sorting
algorithms. I quickly realized VBScript has no native implementation
of linked lists, making an efficient sorting algorithm next to
impossible to write from scratch. So I ditched the idea of separate
queries and tried to come up with a union query that did all the
ordering for me. The final query contained 14 columns, 4 of which
were used simply for ordering and row identification. Two of the
three select queries had 5 null columns. This query was several times
faster even for small record sets, and correctly ordered all the
lines, which is quite marvelous. Despite the query's complexity (its
length is ~2800 characters), the page loads almost instantly on a dual
Xeon 2.4 server with SQL Server. The major problem I had with
converting the page from 3 separate queries to a union query was
making sure all the column data types were identical, but this could
have been avoided by better preparation. SQL Server is much more
picky about the data types than Jet SQL. SQL Server also could not
handle an ntext field type in the union, which had to be converted to
nvarchar.

Anyway, thanks for the suggestions.

Shailesh

I wanted to try a real example, so I tried printing Orders from the
Northwind sample database. The "Orders" table contains each order, and
"Order Details" contains the items for each order. I printed one line
detailing the order, and then multiple lines for each item in that
order. There are about 800 orders and 2000 items, so 2800 lines to be
printed. Using the UNION ALL query took 0.31 seconds. Using a prepared
command object and second recordset as in complextable3.asp took 2.77
seconds. Cool! Looks like I'll have to make another update to the
article. One thing that needs to be analyzed is what happens to
performance if the number of fields needed from each query is very
different. For example, suppose I needed 15 fields for each Order, but
only 3 fields for each item. The record for each item would contain 12
blank fields. I suspect that this won't be too much of a problem though.

Shailesh

Empirical testing would tell the tale. I will however throw out some
conjecture. If you're using an empty strings or NULL as filler and the
data profile is correct (VARCHAR vs. CHAR in SQL Server), then from a
size standpoint, very little additional data is rendered. The most
important thing to note here is that the performance advantage of the
union method over multiple recordsets improves as the result set is
scaled. Your test of 2000/800 records demonstrates a performance factor
of 9x. If you scale your result set to 20000/8000 you would see a
startling jump in that performance factor. In fact, it's likely that the
multiple recordset method would timeout at those numbers while the union
method would happily go about its business. There's an "economies of
scale" issue that comes into play for the union method. It is beyond the
scope of this thread, but the very indexes you referred to in your prior
post would allow the union query to perform better on a record for
record basis, for larger result sets. Finally, an issue that has not be
touched upon is the resource utilization footprint of each method.
Specifically, memory usage, threads, handles, registers, etc... Again,
empirical testing would tell the tale, but the disparity between the
union method and the multiple recordset method would be even more
extreme than simple performance. Consider the fact that in the 2000/800
example, 801 recordsets are being instantiated compared to the one (1)
recordset instantiated in the union method. 800 x [resource overhead for
a recordset]. Scary.

HTH
-Chris

 

[Shailesh Humbad]

That's a good point. I think I could exclude maybe one of the sorting
columns. The others are actually displayed in the ledger or identify
the record type, so I have to return them.

For the most part, my queries are unique and the table names are short.
But I'll keep the points of using aliases and views in mind. I use
Textpad for writing the queries and ASP pages, and I do indent them
liberally and consistently. The only problem is that in VBScript, each
line of the SQL string must be quoted and end with &_, which often leads
to minor typos. Other languages are not much better though. I wish
there was an easier way to demarcate line-spanning strings.

You can see how I indented the UNION query in the example:

http://somacon.com/aspdocs/sendplaintextcode.php?f=complextable6.asp

S.

 

[dlbjr]

Use Stored Procedures if Available and Pass Parameters.

Get away from SQL Statements in your asp.
Build the asp code to be transparent over any database server.

-------------------------------------------------
d l b j r

Unambit from meager knowledge of inane others,
engender uncharted sagacity.
-------------------------------------------------

 

[Guinness Mann]

The only problem is that in VBScript, each line of the
SQL string must be quoted and end with &_, which often leads
to minor typos.

I use a different system. I put the &'s at the beginning of the line
making it easy to ensure they're all there with a quick glance. I also
line other things up so that it's obvious if something is missing. (
I'm going to try to show an example, but I fully expect the newsreaders
to mangle it.)

strSQL = ""_
& "SELECT idnum, ClassIdNum, Type, "_
& "fname, lname, Middle, "_
& "MOS, COMP, RANK "_
& "FROM Roster "_
& "WHERE "_
& "(lname='" & lastname & "') "_
& "AND "_
& "(fname='" & firstname & "') "_
& "AND "_
& "(idnum='" & idnum & "')"


-- Rick

 

[Shailesh Humbad]

Get away from SQL Statements in your asp.

Build the asp code to be transparent over any database server.

In my particular application, I'm actually an advocate of leaving the
SQL statements in the ASP code. The primary reason for this has been to
avoid switching back and forth between editing environments, which saves
a lot of time. Of course, my situation may be a bit unique, as I am
working independently on the entire program, which is in beta, and I get
paid by the hour.

But I can see how transparency would be important, and after the
application development has slowed down, I would almost definitely go
through the code and transfer the SQL statements into the database
(previously Jet, now SQL Server). Another reason would be to improve
the speed of the queries, or if the application was going to have a
heavier load than is currently expected.

I tried to type out Rick's example, but I noticed the end-of line
keystroke sequence is:

Hold down Shift
Press "
Press _
Release Shift
Press Enter
Hold down Shift
Press &
Release Shift

&_ at the end of the line may decrease legibility, but the keystroke
sequence is a bit shorter:

Hold down Shift
Press "
Press &
Press _
Release Shift
Press Enter

The main typos I suffer from are missing an underscore in a line that
has been cut and pasted, so I'm not sure if that format would help out.

I'll give it a more thorough try next time I code though. Thanks for
the suggestions.

Shailesh

 

[Guinness Mann]

I tried to type out Rick's example, but I noticed the end-of line
keystroke sequence is:

Hold down Shift
Press "
Press _
Release Shift
Press Enter
Hold down Shift
Press &
Release Shift

Actually, it goes:

Press ""
Press <left-arrow>
Press _
Press <enter>
Press &
Press <tab>
Press <end>

When I was a little kid and I assembled a new bicycle, the screws came
in a plastic bag and there was always a discrepancy between the number
of screws supplied and the number required.

After I grew up I assembled my first Japanese bicycle and I was amazed
to find that the screws came inserted in a geometrical (rectangle)
pattern in a piece of cardboard so that it was immediately apparent
whether the right quantity of screws was present.

My "ampersands at the beginning of the line" format is my way of
implementing that "immediately apparent" paragigm. I submit that
overall, even if the keystroke sequence is slightly longer than you're
used to, you'll save time due to fewer mistakes. I also submit that
after you've trained your fingers to type *any* end of line sequence
that you'll no longer notice it one way or the other.

The main typos I suffer from are missing an underscore in a line that
has been cut and pasted, so I'm not sure if that format would help out.

Also, note that using my technique all lines except for the last stands
alone (and even that can be made standalone.) That means that you can
always cut and paste entire lines with no problem.

-- Rick

 

[Guinness Mann]

Here's a more extreme example of my style, with everything lined up:

strSQL = ""_
& "SELECT * FROM Tests_Taken "_
& "WHERE test_id = '" & testId & "' "_
& "AND classid = '" & classId & "' "_
& "AND studentid = '" & studentId & "' "

Sometimes if I'm doing a lot of cutting & pasting I'll change the last
lines to:

....
& "AND studentid = '" & studentId & "' "_
& ""

Now *all* lines are capable of being cut & pasted on their own.

It's really hard to make a mistake under these circumstances...

-- Rick

 

[Shailesh Humbad]

That's an interesting way of typing the end-of-line sequence; I wouldn't
have thought of it. I am using Textpad, which auto-indents lines, so I
don't usually have to type Tabs. I understand that any typing sequence
becomes second-nature with practice. Just to play devil's advocate a
little more, pressing left arrow requires moving my right hand away from
'home position' on the keyboard (on my MSFT Natural kybd at least), so
that would be another disadvantage for me. Also, either way allows
cutting and pasting lines complete without modification (except for the
last line if it is non-empty). On the other hand, I rarely type out an
entire SQL string at once, and I'm usually making many modifications to
it and arrow-key jumping all over the place in the code. So, your
technique's better readibility, like the Japanese bill-of-goods cards,
would have an edge in that case.

Sometimes what I do is leave the sql string on a single line, and then I
turn on word-wrap and set the lines to wrap at column 80 (in Textpad).
I usually do this for conceptually simple queries that just have a lot
of text.

Shailesh