Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

running stored procedure from VB 6 is slow,but not from query analyser

Status
Not open for further replies.
Sep 28, 2001
55
US
I designed a stored procedure (it creates a table) on our SQL Server 2000 that runs in about a second when executed from the query analyser. However, when I run the same procedure with the same parameters from my VB6 (sp6) application it takes almost a minute to execute. Has anyone run into this problem?
 
This is more of a front end problem rather than the sql. What is going on, in the app? It could be how you are displaying the data, or if you are doing calculations there..etc.

Jim
 
start proc with a SET NOCOUNT ON, end it with SET NOCOUNT OFF

That will "prevent" the procedure from sending the rows affected value to your app which could chew up some vb to sql time.

This sped up my asp.net webpage on local intranet...

...
CREATE PROC bla
@param int
AS
SET NOCOUNT ON
SQL Query

SET NOCOUNT OFF
GO
...

my 2 pennies
 
oddly enough, it stopped doing it after I set ARITHABORT on.

I have no idea why that fixed it( I set ARITHABORT on because of a DTS realted problem) but if anyone has a clue why, I'd love to hear it
 
Setting ARITHABORT ON changes the way that the SQL Server deals with some math issues. Basically it disables some checks that SQL does.

ARITHABORT ON is the default option in Query Analyzer.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top