SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

Post by Sean P » Wed, 16 Oct 2002 05:29:07



When running certain stored procedures from an ADO client (VB,VBS,ASP
(VBScript)) the execution time is 10 times as long as when running the same
procedure with the same arguments inside of Query Analyzer. Can anyone
explain this to me?

I am running W2K Server SP2, SQL Server 2000 SP2, dual 1.4GHz, 1 GB RAM,
SCSI RAID 5 HD Config.

Thanks in advance,
Sean

 
 
 

SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

Post by Patrick Loga » Wed, 16 Oct 2002 05:49:42


Post the code that executes the stored proc.....  Version numbers of ADO and
Providers as well.

--
Patrick Logan, MCSD
Opinions expressed are my own and not necessarily those of my employer.


Quote:> When running certain stored procedures from an ADO client (VB,VBS,ASP
> (VBScript)) the execution time is 10 times as long as when running the
same
> procedure with the same arguments inside of Query Analyzer. Can anyone
> explain this to me?

> I am running W2K Server SP2, SQL Server 2000 SP2, dual 1.4GHz, 1 GB RAM,
> SCSI RAID 5 HD Config.

> Thanks in advance,
> Sean


 
 
 

SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

Post by Sean P » Wed, 16 Oct 2002 06:06:40


I'm using ADO 2.7, and here is one example (VB) that produces the results I
metioned before.

        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim SQL As String
        Dim sConnect As String
        Dim i As Integer
        Dim sOut As String
        Dim dTime As Double

        Me.MousePointer = vbHourglass
        dTime = Timer
        SQL = "exec sp_GetVirtualLines -1"

        cn.Open "Provider=SQLOLEDB.1;Initial Catalog=MyDB;Data
Source=MyServer;Network Library=dbmssocn;", _
                   "MyUser", _
                   "*********"
        rs.CursorLocation = adUseServer
        rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly

        sOut = ""
        If Not (rs.EOF And rs.BOF) Then
            sOut = sOut & "<tr>" & vbCrLf
            For i = 0 To rs.Fields.Count - 1
                sOut = sOut & "<th>" & rs.Fields(i).Name & "</th>" & vbCrLf
            Next
            sOut = sOut & "</tr>" & vbCrLf
            While Not rs.EOF
                sOut = sOut & "<tr>" & vbCrLf
                For i = 0 To rs.Fields.Count - 1
                    sOut = sOut & "<td>" & rs.Fields(i) & "</td>" & vbCrLf
                Next
                sOut = sOut & "</tr>" & vbCrLf
                rs.MoveNext
            Wend
        End If
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing


> Post the code that executes the stored proc.....  Version numbers of ADO
and
> Providers as well.

> --
> Patrick Logan, MCSD
> Opinions expressed are my own and not necessarily those of my employer.



> > When running certain stored procedures from an ADO client (VB,VBS,ASP
> > (VBScript)) the execution time is 10 times as long as when running the
> same
> > procedure with the same arguments inside of Query Analyzer. Can anyone
> > explain this to me?

> > I am running W2K Server SP2, SQL Server 2000 SP2, dual 1.4GHz, 1 GB RAM,
> > SCSI RAID 5 HD Config.

> > Thanks in advance,
> > Sean

 
 
 

SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

Post by Patrick Loga » Wed, 16 Oct 2002 06:31:34


Couple of immediate observations....

1.  Why the server-side cursor?  User client-side and stream it as fast as
your network bandwidth allows.
2.  In the Open function of the Recordset, you need to specify the Command
Type of adCmdStoredProc as the fourth parameter.  If you don't specify, it
will make a call to SQL Server trying to ascertain what type of command it
might be.
3.  Are you timing the raw data retrieval or is your string concatenation
while parsing the fields going into your timing?
4. Might consider a disconnected recordset, get all the data back then parse
the rows at your leisure.
5. Pre-binding to the stored procedure using a Command object may also yield
faster results.

Few quick ideas.....

HTH
--
Patrick Logan, MCSD
Opinions expressed are my own and not necessarily those of my employer.


> I'm using ADO 2.7, and here is one example (VB) that produces the results
I
> metioned before.

>         Dim cn As New ADODB.Connection
>         Dim rs As New ADODB.Recordset
>         Dim SQL As String
>         Dim sConnect As String
>         Dim i As Integer
>         Dim sOut As String
>         Dim dTime As Double

>         Me.MousePointer = vbHourglass
>         dTime = Timer
>         SQL = "exec sp_GetVirtualLines -1"

>         cn.Open "Provider=SQLOLEDB.1;Initial Catalog=MyDB;Data
> Source=MyServer;Network Library=dbmssocn;", _
>                    "MyUser", _
>                    "*********"
>         rs.CursorLocation = adUseServer
>         rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly

>         sOut = ""
>         If Not (rs.EOF And rs.BOF) Then
>             sOut = sOut & "<tr>" & vbCrLf
>             For i = 0 To rs.Fields.Count - 1
>                 sOut = sOut & "<th>" & rs.Fields(i).Name & "</th>" &
vbCrLf
>             Next
>             sOut = sOut & "</tr>" & vbCrLf
>             While Not rs.EOF
>                 sOut = sOut & "<tr>" & vbCrLf
>                 For i = 0 To rs.Fields.Count - 1
>                     sOut = sOut & "<td>" & rs.Fields(i) & "</td>" & vbCrLf
>                 Next
>                 sOut = sOut & "</tr>" & vbCrLf
>                 rs.MoveNext
>             Wend
>         End If
>         rs.Close
>         Set rs = Nothing
>         cn.Close
>         Set cn = Nothing



> > Post the code that executes the stored proc.....  Version numbers of ADO
> and
> > Providers as well.

> > --
> > Patrick Logan, MCSD
> > Opinions expressed are my own and not necessarily those of my employer.



> > > When running certain stored procedures from an ADO client (VB,VBS,ASP
> > > (VBScript)) the execution time is 10 times as long as when running the
> > same
> > > procedure with the same arguments inside of Query Analyzer. Can anyone
> > > explain this to me?

> > > I am running W2K Server SP2, SQL Server 2000 SP2, dual 1.4GHz, 1 GB
RAM,
> > > SCSI RAID 5 HD Config.

> > > Thanks in advance,
> > > Sean

 
 
 

SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

Post by Sean P » Wed, 16 Oct 2002 06:50:05


1. Tried both client-side and server-side. No difference.
2. Tried that. No difference
3. Using SQL Profiler for timing.
4. Tried that also. No difference.
5. Command object didn't work either.


> Couple of immediate observations....

> 1.  Why the server-side cursor?  User client-side and stream it as fast as
> your network bandwidth allows.
> 2.  In the Open function of the Recordset, you need to specify the Command
> Type of adCmdStoredProc as the fourth parameter.  If you don't specify, it
> will make a call to SQL Server trying to ascertain what type of command it
> might be.
> 3.  Are you timing the raw data retrieval or is your string concatenation
> while parsing the fields going into your timing?
> 4. Might consider a disconnected recordset, get all the data back then
parse
> the rows at your leisure.
> 5. Pre-binding to the stored procedure using a Command object may also
yield
> faster results.

> Few quick ideas.....

> HTH
> --
> Patrick Logan, MCSD
> Opinions expressed are my own and not necessarily those of my employer.



> > I'm using ADO 2.7, and here is one example (VB) that produces the
results
> I
> > metioned before.

> >         Dim cn As New ADODB.Connection
> >         Dim rs As New ADODB.Recordset
> >         Dim SQL As String
> >         Dim sConnect As String
> >         Dim i As Integer
> >         Dim sOut As String
> >         Dim dTime As Double

> >         Me.MousePointer = vbHourglass
> >         dTime = Timer
> >         SQL = "exec sp_GetVirtualLines -1"

> >         cn.Open "Provider=SQLOLEDB.1;Initial Catalog=MyDB;Data
> > Source=MyServer;Network Library=dbmssocn;", _
> >                    "MyUser", _
> >                    "*********"
> >         rs.CursorLocation = adUseServer
> >         rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly

> >         sOut = ""
> >         If Not (rs.EOF And rs.BOF) Then
> >             sOut = sOut & "<tr>" & vbCrLf
> >             For i = 0 To rs.Fields.Count - 1
> >                 sOut = sOut & "<th>" & rs.Fields(i).Name & "</th>" &
> vbCrLf
> >             Next
> >             sOut = sOut & "</tr>" & vbCrLf
> >             While Not rs.EOF
> >                 sOut = sOut & "<tr>" & vbCrLf
> >                 For i = 0 To rs.Fields.Count - 1
> >                     sOut = sOut & "<td>" & rs.Fields(i) & "</td>" &
vbCrLf
> >                 Next
> >                 sOut = sOut & "</tr>" & vbCrLf
> >                 rs.MoveNext
> >             Wend
> >         End If
> >         rs.Close
> >         Set rs = Nothing
> >         cn.Close
> >         Set cn = Nothing



> > > Post the code that executes the stored proc.....  Version numbers of
ADO
> > and
> > > Providers as well.

> > > --
> > > Patrick Logan, MCSD
> > > Opinions expressed are my own and not necessarily those of my
employer.



> > > > When running certain stored procedures from an ADO client
(VB,VBS,ASP
> > > > (VBScript)) the execution time is 10 times as long as when running
the
> > > same
> > > > procedure with the same arguments inside of Query Analyzer. Can
anyone
> > > > explain this to me?

> > > > I am running W2K Server SP2, SQL Server 2000 SP2, dual 1.4GHz, 1 GB
> RAM,
> > > > SCSI RAID 5 HD Config.

> > > > Thanks in advance,
> > > > Sean

 
 
 

SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

Post by Andrew Merisan » Wed, 16 Oct 2002 20:20:53


Hey Sean,

I'm not sure what the sp looks like...But I would try doing a:
Set RS = CN.Execute(SQL)
with rs being a client side ado recordset.

HTH

Andrew


> 1. Tried both client-side and server-side. No difference.
> 2. Tried that. No difference
> 3. Using SQL Profiler for timing.
> 4. Tried that also. No difference.
> 5. Command object didn't work either.



> > Couple of immediate observations....

> > 1.  Why the server-side cursor?  User client-side and stream it as fast
as
> > your network bandwidth allows.
> > 2.  In the Open function of the Recordset, you need to specify the
Command
> > Type of adCmdStoredProc as the fourth parameter.  If you don't specify,
it
> > will make a call to SQL Server trying to ascertain what type of command
it
> > might be.
> > 3.  Are you timing the raw data retrieval or is your string
concatenation
> > while parsing the fields going into your timing?
> > 4. Might consider a disconnected recordset, get all the data back then
> parse
> > the rows at your leisure.
> > 5. Pre-binding to the stored procedure using a Command object may also
> yield
> > faster results.

> > Few quick ideas.....

> > HTH
> > --
> > Patrick Logan, MCSD
> > Opinions expressed are my own and not necessarily those of my employer.



> > > I'm using ADO 2.7, and here is one example (VB) that produces the
> results
> > I
> > > metioned before.

> > >         Dim cn As New ADODB.Connection
> > >         Dim rs As New ADODB.Recordset
> > >         Dim SQL As String
> > >         Dim sConnect As String
> > >         Dim i As Integer
> > >         Dim sOut As String
> > >         Dim dTime As Double

> > >         Me.MousePointer = vbHourglass
> > >         dTime = Timer
> > >         SQL = "exec sp_GetVirtualLines -1"

> > >         cn.Open "Provider=SQLOLEDB.1;Initial Catalog=MyDB;Data
> > > Source=MyServer;Network Library=dbmssocn;", _
> > >                    "MyUser", _
> > >                    "*********"
> > >         rs.CursorLocation = adUseServer
> > >         rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly

> > >         sOut = ""
> > >         If Not (rs.EOF And rs.BOF) Then
> > >             sOut = sOut & "<tr>" & vbCrLf
> > >             For i = 0 To rs.Fields.Count - 1
> > >                 sOut = sOut & "<th>" & rs.Fields(i).Name & "</th>" &
> > vbCrLf
> > >             Next
> > >             sOut = sOut & "</tr>" & vbCrLf
> > >             While Not rs.EOF
> > >                 sOut = sOut & "<tr>" & vbCrLf
> > >                 For i = 0 To rs.Fields.Count - 1
> > >                     sOut = sOut & "<td>" & rs.Fields(i) & "</td>" &
> vbCrLf
> > >                 Next
> > >                 sOut = sOut & "</tr>" & vbCrLf
> > >                 rs.MoveNext
> > >             Wend
> > >         End If
> > >         rs.Close
> > >         Set rs = Nothing
> > >         cn.Close
> > >         Set cn = Nothing



> > > > Post the code that executes the stored proc.....  Version numbers of
> ADO
> > > and
> > > > Providers as well.

> > > > --
> > > > Patrick Logan, MCSD
> > > > Opinions expressed are my own and not necessarily those of my
> employer.



> > > > > When running certain stored procedures from an ADO client
> (VB,VBS,ASP
> > > > > (VBScript)) the execution time is 10 times as long as when running
> the
> > > > same
> > > > > procedure with the same arguments inside of Query Analyzer. Can
> anyone
> > > > > explain this to me?

> > > > > I am running W2K Server SP2, SQL Server 2000 SP2, dual 1.4GHz, 1
GB
> > RAM,
> > > > > SCSI RAID 5 HD Config.

> > > > > Thanks in advance,
> > > > > Sean

 
 
 

1. Execution Plan shows seek, Profiler shows scan

Using Perfmon, I noticed a large amount of full scans/sec taking place
on my server. I turned Profiler on to look into this further. I found
a series of scans taking place after stored procs were called. If I
look at the execution plan of these procs in QA, it shows index seeks
and/or clustered index seeks.

I then added the execution plan and show plan trace events to my trace
and re-recorded. Still, the execution plans show seeks, however during
the time between the rpc start event and the rpc stop event, there are
a series of scans taking place of the tables/indexes involved in the
proc. Any idea what is causing these scans?

Thanks for you help.

2. Foreign Key to UniqueIdentifier

3. SQL Query Analyzer Server Trace vs SQL Profiler Trace

4. VB code for customised Conflict Viewer

5. sql analyzer and profiler do not show user interface

6. Update Statistics

7. Sql Server Query Analyzer Execution Plan

8. mySQL Connector/J - bulk insert - need more power

9. SQL Server 7 query analyzer: the execution plan

10. Query Analyzer Execution Time?

11. Sql Server 2000 Performance and Sql Query Analyzer

12. Execution Time : Stored Proc ( SQL Server 2000)