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

[tanman1975]

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?

 

[jbenson001]

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

 

[adamroof]

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

 

[tanman1975]

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

 

[mrdenny]

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)

http://www.mrdenny.com