SQL SP takes long time to run from VB.NET app

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

SQL SP takes long time to run from VB.NET app

Author

Topic

chimp153
Starting Member

1 Post

Posted - 2007-11-22 : 16:52:55

Hi all,

I have a stored procedure that is called from a VB.NET application that takes an enormously long time to execute. In the QA it only takes 10sec but in the application it takes ages. The stored procedure is as follows:

PROCEDURE NAME IS SPTOPTWENTYUSERS

SELECT TOP 20 STRUSERNAME,SUM(INTBYTESRECVD) AS INTDOWNLOAD FROM TBLISAWEBLOGS
WHERE DTELOGDATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY STRUSERNAME
ORDER BY INTDOWNLOAD DESC

The code that runs it is as follows:

sSQLString = SPTOPTWENTYUSERS
Using cnn As New SqlConnection(GetPath)
Try
Dim cmd As New SqlCommand(sSQLString, cnn)
Dim dr As SqlDataReader

With cmd
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 0
.Parameters.Add("@BEGINDATE", SqlDbType.DateTime)
.Parameters.Add("@ENDDATE", SqlDbType.DateTime)
.Parameters("@BEGINDATE").Value = dtpStartDate.Value
.Parameters("@ENDDATE").Value = dtpEndDate.Value
End With
cnn.Open()
dr = cmd.ExecuteReader

Any help on why this happens would be much appreciated.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-23 : 22:02:54

Open up SQL Profiler. Configure a trace to include the Performance\Showplan XML event. Start the trace. Run the application so that your code is executed. Run the query through Query Analyzer. Stop the trace. Compare the two execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources