Performance with Stored Procedures

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 2000 Forums

SQL Server Development (2000)

Performance with Stored Procedures

Author

Topic

KevinMunro
Starting Member

7 Posts

Posted - 2004-12-05 : 09:02:43

Hello, first post on this group

I've had a performance problem with an insert query based on a selection. The query is something like:


INSERT INTO FRED SELECT DATA FROM MARTHA

I've been firing this SQL statement at my SQL Server database using ADO Version 2.5 using this statement:


mConnection.Execute vstrSQL, , adExecuteNoRecords

But I get better performance passing in the SQL statement directly into a stored procedure that just executes the SQL statement.

Any thoughts?

Thanks, Kevin.


CREATE PROCEDURE amsp_SQLSubmit
@SQL varchar(1000)
AS
select @SQL=replace(@SQL,'`','''')
set nocount on              
exec (@SQL)
GO

Note - in passing in the string to SQL Server I'm replacing the ' with ' and decoding.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-05 : 09:21:09

1) Why not just have the string defined within the procedure instead of passing it in?

2) Are you trying to substitute some parameters in your INSERT statement? If so, it's not clear to me what exactly you want substituted.

3) Only because I'm completely pedantic, the SET NOCOUNT ON should be the first line in your sproc ;-)

HTH

=========================================
Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)
(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-05 : 09:23:38

Welcome to the forums Kevin,

My thoughts:

- No performance difference between the two.
- SQL Injection potential in either case.

http://www.sommarskog.se/dynamic_sql.html

KevinMunro
Starting Member

7 Posts

Posted - 2004-12-06 : 05:36:37

quote:
Originally posted by Bustaz Kool

1) Why not just have the string defined within the procedure instead of passing it in?

The string is made up of criteria fed in from user selection in the UI.

quote:

2) Are you trying to substitute some parameters in your INSERT statement? If so, it's not clear to me what exactly you want substituted.

Yes but could be one param or up to 10 or more.

Thanks, Kevin

KevinMunro
Starting Member

7 Posts

Posted - 2004-12-06 : 05:37:57

Thanks ehorn,

I think the performance difference I thought I was experiencing was just perception.

Kevin.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources