Re: iis/asp + sql2000 a bit slow

From: Bob Barrows (reb01501_at_NOyahoo.SPAMcom)
Date: 02/06/04 

Date: Fri, 6 Feb 2004 17:43:52 -0500

Florian wrote:
> 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")

WScript.Echo? I thought your were using ASP ...

I suspect that you are suffering the consequences of failing to encapsulate
your queries into a stored procedure. You're doing too much processing in
the client.

Your bottleneck is not the queries: it is the time needed to send the
queries and return their results across the network. In addition, recordset
loops are very expensive: you should use GetRows to stuff your data into
arrays and loop through the arrays instead. You will see a great increase in
performance if you follow these guidelines. 

-- 
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"

Relevant Pages

  • Re: COM+ and ADo and performance.
    ... Change CursorLocation to clUseServer and CursorType to ctOpenForwardOnly it ... > queries and there is no more than 5-10 records at a time. ... I simply write the same query in Query Analyzer. ... But When I execute the same insert query from TClientDataSet, ...
    (borland.public.delphi.database.ado)
  • Re: embedded in strings
    ... The thing that had me going was that the same queries worked fine insql ... query analyzer if you want to see what I'm talking about. ... > Can you explain a little better how it throws a null reference exception? ... > | I'm having problems with queries like the following in sqlserverce. ...
    (microsoft.public.sqlserver.ce)
  • Re: embedded in strings
    ... queries with vaues longer than the column being queried. ... I guess I could send you the sqlce database so you can test it. ... > | the null reference exception. ... > | query analyzer if you want to see what I'm talking about. ...
    (microsoft.public.sqlserver.ce)
  • Re: Is sql server as good as access 97?
    ... Once you really get into using Query Analyzer for writing your ... Transact-SQL and for formulating 'queries' for exporting to your code, ... resulting stored procedure runs in a small fraction of the time it took to ... Unfortunately the database I am converting ...
    (microsoft.public.sqlserver.programming)
  • Re: Default Queries
    ... Both are capable of returning records with duplicate ... But they both would return the exact same results. ... > Can anyone shed some light on the built in Queries? ... > what I'm trying to say is it lists duplicate computer names when running ...
    (microsoft.public.sms.misc)