ASP - SQLServer Performance Issue

Hi,

Sorry if this has been asked before, I've searched the archives and FAQ
with no luck.

I'm using ASP to build a page containing the results of a simple 'SELECT *
FROM whatevertable'.

'whatevertable' contains 60000+ rows, but only 1000 rows are displayed at
a time (clicking a 'next' button displays the next 1000)

When the page first loads, it can take up to 30 seconds (each 'next' page
after then only takes about 0.5 seconds)

Why is this first page so slow? I'm guessing it's because ADODB stores the
entire recordset, blocking the ASP script until it's done, (it's not the
database anyways; running the same query in query analyzer will return the
first record yesterday; it's quick!)

Assuming this is the cause, is there any way to get ASP and ADODB to run
synchronously, blocking only if there was an attempted to read a recordset
that hadn't yet crossed the network?

Thank you for any helps,
Kind regards,
Eliott

Re: ASP - SQLServer Performance Issue

MRe wrote:
> Hi,
>
> Sorry if this has been asked before, I've searched the archives and
> FAQ with no luck.
>
> I'm using ASP to build a page containing the results of a simple
> 'SELECT * FROM whatevertable'.
>
> 'whatevertable' contains 60000+ rows, but only 1000 rows are
> displayed at a time (clicking a 'next' button displays the next 1000)
>
> When the page first loads, it can take up to 30 seconds (each 'next'
> page after then only takes about 0.5 seconds)
>
> Why is this first page so slow?

Because you are using an inefficient technique. See:
http://www.aspfaq.com/show.asp?id=2120
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ASP - SQLServer Performance Issue

>> I'm using ASP to build a page containing the results of a simple
>> 'SELECT * FROM whatevertable'.
>>
>> When the page first loads, it can take up to 30 seconds (each 'next'
>> page after then only takes about 0.5 seconds)
>>
>> Why is this first page so slow?
>
> Because you are using an inefficient technique. See:
> http://www.aspfaq.com/show.asp?id=2120


Thank you for the link Bob,

There are a few interesting ideas there that I hadn't thought of, which
I think might just do the trick; I shall give them a twirl.

Thanks again,
Kind regards,
Eliott

Re: ASP - SQLServer Performance Issue

Hi, can someone please explain what's going on here...

I was modifying my..

SELECT * FROM 'whatevertable'

..the one that takes 30 seconds to display in ASP, and at one point, I
happened to call the adodb.recordset.open() method passing a..

/* some comment */SELECT * FROM 'whatevertable'

..query. After that, the page only took 3 seconds to display. Bedazzled, I
tried placing a /**/ comment at the start of another query, one that had
taken 70 seconds to display. Now it only takes 7 seconds - exact same
output, just faster! It has also increased a query that took 0.3 seconds to
0.03 seconds.

Have I stumbled across one of those hidden MS features, like switching off
that, 'crash after 5000 bytes of un-save changes', check-box in Word, or
what?

Thank you,
Kind regards,
Eliott

Re: ASP - SQLServer Performance Issue

MRe wrote:
> Hi, can someone please explain what's going on here...
>
> I was modifying my..
>
> SELECT * FROM 'whatevertable'
>
> ..the one that takes 30 seconds to display in ASP, and at one point,
> I happened to call the adodb.recordset.open() method passing a..
>
> /* some comment */SELECT * FROM 'whatevertable'
>
> ..query. After that, the page only took 3 seconds to display.

Did you test this in Query Analyzer and get the same results? If you didn't
test it in Query Analyzer, then that should be your first step.

You should always test your queries in QA before attempting to run them from
client applications via ADO. How else do you know where to place the blame
if something goes wrong?


> Bedazzled, I tried placing a /**/ comment at the start of another
> query, one that had taken 70 seconds to display. Now it only takes 7
> seconds - exact same output, just faster! It has also increased a
> query that took 0.3 seconds to 0.03 seconds.

Again ... do you get the same results in Query Analyzer?

>
> Have I stumbled across one of those hidden MS features, like
> switching off that, 'crash after 5000 bytes of un-save changes',
> check-box in Word, or what?
>

Impossible to say without a better repro script:

SQL Server version
CREATE TABLE script(s)
scripts containing insert statements for sample data

See http://www.aspfaq.com/5006 for quick ways to acquire the above.

actual sql statement than recreates the behavior
if behavior only occurs when running via ADO, then actual asp script to
reproduce the behavior (stripped to the bare minimum ... leave out all the
html, etc.; only provide what is necessary to see the behavior)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"