Re: iis/asp + sql2000 a bit slow

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Date: Sat, 07 Feb 2004 05:05:43 GMT

Thanks, looks like a nice site though I haven't been able to improve it
quite yet ... :(

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:euHN57Q7DHA.1596@TK2MSFTNGP10.phx.gbl...
> In addition to Bob's points, see http://www.aspfaq.com/2424#db
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
>
> "Florian" <REMOVEUPPERCASEwizard_oz@gmx.net> wrote in message
> news:wdUUb.17021$uM2.8228@newsread1.news.pas.earthlink.net...
> > 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: Temporary table disappears
    ... You don't give the version of SQL server, but at a guess you are seeing some ... > So I leave these ten queries running until something ... > Connection Broken" ...
    (microsoft.public.sqlserver.connect)
  • Re: Performance problem
    ... The client API lingers to the connection for up to 60 seconds. ... same queries in managment studio, all queries executes fast enough(10 ... Why I get the duration time in sql profiler 6-12 seconds for the most ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2000 drops connection, DDOS attack?
    ... network error', you might try using a different protocol. ... using the Client Network utility or specified in the connection string. ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • iis/asp + sql2000 a bit slow
    ... I ran every single on of these queries with the SQL Query Analyzer, ... So I'm trying to find out where the overhead between ASP and the connection ...
    (microsoft.public.inetserver.asp.db)
  • Re: SQL 2000 drops connection, DDOS attack?
    ... there is no network connectivity to the SQL box or the SQL Server instance ... queries in a loop on the same connection? ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)