New to SQL 2

[bminaeff]

Hi there,
Quick question to those more seasoned than me. I often use VB 2005 to select data from a local DB. I usually use a stored procedure, but have occasionally just hard coded my query into my program. My question is, which is faster/ more efficient? Are there any advantages/disadvatages to using a stored procedure?

Thanks,
Bill M.

 

[jmeckley]

there is a debate about stored procs vs dynamic sql that is older than me i believe preformance is more about proper indexing and other dba related functionality, not sp vs. dynamic sql. if the db is properly configured then the preformance gain between SP and dynamic sql is negligable to the end user.

if you're programming with .net use sqlparameters when building your sql commands to avoid sql injection attacks. this is the security flaw with dynamic sql.

also, if the sql is poorly structured then you will not get good preformance. doesn't matter if it's an SP or dynamic sql.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mrdenny]

There isn't really any debate between stored procs and dynamic SQL.

Stored procedures will cache the execution plans way better requiring less compiles on the database. Execution plan compiles are one of the most expensive operations which the SQL Server does.

Dynamic SQL also has security issues which come up because you have to give the user rights to the tables instead of to just the stored procedure.

When you use stored procedures life is much easier to deal with when you have to make changes to your T/SQL code as you simply have to update the stored procedure instead of having to push code to all the clients.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[LNBruno]

This is, IMHO, the de facto standard answer to the question:

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

< M!ke >
[small]Holidays cause stress. Crazy people crack under stress. Consider yourself warned.[/small]

 

[r937]

Execution plan compiles are one of the most expensive operations which the SQL Server does.

but still often a lot less expensive than running the actual query itself


example 1: compile = 9 ms, query = 37 ms

example 2: compile = 9 ms, query = 2937 ms





r937.com | rudy.ca

 

[mrdenny]

This is true.

But if a query takes 9 ms to compile and you run it 20000 times a day (which is totally possible on a web application) ... that's 180 seconds of CPU time per day spent doing something which it doesn't have to.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[r937]

yes, of course you are right

but each of the 20000 visitors isn't gonna feel a thing!!!

and meanwhile you are spending how many manhours adapting your queries into stored procs?



r937.com | rudy.ca

 

[jbenson001]

And how many man hours are you going to spend maintaining your code all over an application, when it can be done in one place, the stored procedure?

 

[r937]

and from how many different places is the stored proc called?

typically, the average is 1.00000937



r937.com | rudy.ca

 

[mrdenny]

Yes, the users may not feal anything, however the system can spend it's time doing something else.

and meanwhile you are spending how many manhours adapting your queries into stored procs

My concern at work isn't about making my life easier. It's about making the system work as best it possibly can.

The same code is usually used in more than one place. You'll see it in the front end customer interface, and probably a couple of places in the sales/customer service interface.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLSister]

You would spend no man-hours converting your queries to sps if you always used sps.

From a database perspective, I would never allow anything except sps to be used to access my database. That way I do not have to give rights at the table level and I can look in one place for all the code which accesses my database. Also changes to the code are easier to deploy.

The users do feel the impact when the system grows to be so large that these milleseconds add up. At this point, performance tuning and changing the entire app to run properly is both urgent and far more complex. Better to start with the things you know are better for performance from the start. You will still eventually need to performance tune, but you won't have to change every single query.

"NOTHING is more important in a database than integrity." ESquared

 

[r937]

you guys make a lot of sense -- for a corporate environment, with delineated roles (e.g. a separate DBA dept)

for the single developer, where you are not only the business analyst, interface designer, web developer, database modeller, and dba, taking the time to create a stored proc over and above a query is just so much extra work for no apparent gain

i'm just trying to present a balanced perspective

but you corporate DBAs, who get to set rules like "nobody accesses my database except through stored procs," please do keep on doin' what you doin'



r937.com | rudy.ca

 

[gmmastros]

Rudy,

I think you make some valid points too. Depending on the application, dynamic SQL isn't so bad. You could argue that my application would fall in to the sort of general area where dynamic SQL would probably work just fine.

However... whenever I am working on new functionality, which requires a new query, I usually use Query Analyzer (or SQL Server Management Studio) to construct the query, primarily to make sure that it does what I want it to. At that point, it is simpler to 'slap on' a 'Create Procedure' line and parameters, than it is to 'stringify' the query to be executed from the front end.

Again, speaking from the perspective of my application, which is really quite large... I have 1 executable, 1 active X control (ocx) and approximately 80 ActiveX dll's. If a bug is discovered in one of the executables, it must go through a rigorous testing phase before it can be released to my customers (there are about 100 of them). If a bug is found in a query, all we need to do is to test the query and distribute the stored procedure. From experience, it is simpler to debug a stored procedure and distribute that than it is to test/compile/distribute the executables.

In my opinion, stored procs are better for the database, but they are also better from an overall software development perspective too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheSQL]

There isn't really any debate between stored procs and dynamic SQL.

That was good for a laugh.

I spent about an hour reading all of the responses to this post the other night. It's a pretty good debate, presenting some lucid (and not-so-lucid) points about the pros and cons of each, for whoever may be interested. Enjoy.

Though my lawyer advised me not to comment ;-), I'm rather fond of sprocs myself, but can understand where dynamic SQL has it's place. I don't think there's any definitive answer, as is usually the case.

 

[jmeckley]

r937, i have the same responsibilities you have at my current job. therefore I use an ORM tool to map my database to business entities. there are open source tools like nhibernate, active record and sub-version. I use a $ tool called LLBL Gen Pro. These tools are a great time saver for developing the basis of a rich domain model in little time.

Jason Meckley
Programmer
Specialty Bakers, Inc.