by Sean P » Wed, 16 Oct 2002 05:29:07
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
by Patrick Loga » Wed, 16 Oct 2002 05:49:42
--
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
by Sean P » Wed, 16 Oct 2002 06:06:40
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
> --
> 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
by Patrick Loga » Wed, 16 Oct 2002 06:31:34
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.
> 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
by Sean P » Wed, 16 Oct 2002 06:50:05
> 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
by Andrew Merisan » Wed, 16 Oct 2002 20:20:53
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
> > 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
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)