iis/asp + sql2000 a bit slow

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Florian (REMOVEUPPERCASEwizard_oz_at_gmx.net)
Date: 02/06/04 

Date: Fri, 06 Feb 2004 22:06:20 GMT

I have a performance problem, but I don't really know where the exact cause
lies.

Win2k (all patches) / IIS / ASP + VBScript

I have a table with about 10 columns and almost 1million of records that I
indexed (with index views). The page I'm talking about is doing 9 queries
(all just select) and takes too long to load.

I ran every single on of these queries with the SQL Query Analyzer, looking
at the execution plan. All queries run superfast, so I'm pretty sure that
the problem is not there.

As soon as I run this from the ASP page however it takes almost 1 sec per
query to run, a total of about 6 seconds. I don't see why this doesn't work
in one second, considering that there is absolutely no load on the server.
So I'm trying to find out where the overhead between ASP and the connection
to the SQL Server is. To eliminate IIS I wrote a test .vbs script and
executed it from the command prompt - it's slow also. There I saw that
executing the query seems to take so long.

Originally the page was accessing the data through ODBC, suspecting that
this might be a problem I changed it to sqloledb. Unfortunately it does not
seem to make a difference (tried named pipes and socket).

Another strange thing is that one of the queries is about twice as slow as
the other ones, even though it's of the exact same nature and it returns
superfast in the Query Analyzer. I also tried to access this view (the slow
one) from a remote computer with ODBC/MS Access and it's slow also.

Anyway, does anybody have an idea what's going on here? The code looks like
this:

strcon ="Provider=sqloledb;Data Source=dbserver;Network
Library=dbnmpntw;User ID=myuser;Password=mypwd;"
'strcon ="Provider=sqloledb;Data Source=127.0.0.1,1433;Network
Library=DBMSSOCN;User ID=myuser;Password=mypwd;"

Set Connection = CreateObject("adodb.connection")
Connection.ConnectionString = strcon
Connection.Open strcon

counter = 0
Set RS = Connection.Execute("select field1,total from view_distinct_field1
where field1 != '' order by total desc")
Do While Not RS.EOF
 WScript.Echo RS.Fields("field1")
 If counter >= Max Then
  Exit Do
 End If
 counter = counter + 1
 RS.MoveNext
Loop

This query is basically repeated 5 times, every time with a different field.
The counter is there so that only a maximum of X rows are returned.

The original way of connecting was:

Set Connection = CreateObject("ADODB.Connection")
ConnectionString = "DSN=" & SQLODBC
If (Len(SQLUser) > 0) Then
 ConnectionString = ConnectionString & "; UID=" & SQLUser & "; PWD=" &
SQLPwd
End If

Connection.Open ConnectionString

For those curious, the machine is a P4 2.6GHz with 512Mb of RAM. There is
nothing on this server except for Win2k, IIS and SQL 2000. The machine is
pretty much idle, though there are clients connecting randomely and
inserting data into the table - I'd guess about 1 row every few seconds.

I just can't seem to understand what is delaying this stuff so much, I'll
continue my search with trying maybe PERL or something. What is the SQL
Analyzer doing better than all the other apps?

Thanks for any hints.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
  • Re: ASP - FROM statement slows down connection to database
    ... Open your database in Access, switch to the Queries tab, create a new query ... in Design View without choosing a table, swtich to SQL View, paste the sql ... connection string rather than using an ... recordset open statements. ...
    (microsoft.public.inetserver.asp.db)
  • RE: Posible to speed up query update with Apllication.screenupdati
    ... Don't change anything on the database side; the only question is the most ... You create a connection by sending the connect string to the ODBC driver. ... (multiple queries). ... You now execute the query (there are several ways in ADO but I usually ...
    (microsoft.public.excel.programming)