Calling SP's V Running Query in code

~J~ · 20 Dec 2006 at 11:41

Quick question:

As a VERY simple example, if I wanted to return data from a SQL server by running a query, would it be quicker to call a Stored Procedure from a VB.Net application or running the query syntax from code?

Mr^B · 20 Dec 2006 at 11:55

~J~ said:
Quick question:

As a VERY simple example, if I wanted to return data from a SQL server by running a query, would it be quicker to call a Stored Procedure from a VB.Net application or running the query syntax from code?

It's *usually* quicker to use a Stored Procedure - as you don't have to wait for it to compile the execution plan each time it runs. However this isn't always the case.

~J~ · 20 Dec 2006 at 12:17

Mr^B said:
It's *usually* quicker to use a Stored Procedure - as you don't have to wait for it to compile the execution plan each time it runs. However this isn't always the case.

Yeah, you've answered what I thought. I'm in the middle of designing a new app for a customer and they've a bit of a "know it all" at their place who I think "may" mess about with the SP's if they're on the server (or enhance them without paying us!) and thought it would be more secure to put them in the code. But didn't want an impact on performance.

Tricky one.

akakjs · 20 Dec 2006 at 14:25

SPs are often (note not always) more secure as they include type checking. Inline queries have to be treated more carefully.

As for performance I believe (on mssql at least) under most conditions their is very little in it, as the execution plan for a normal queries is cached after the first run anyways.

akakjs

lokkers · 21 Dec 2006 at 13:22

I've found simple queries (like "select column from table where column = value") are fine in code and quicker to write, but when you get to more complicted stuff (e.g. needing a join or updates/inserts) then SPs or views are the way to go.

Its also easier to deploy fixes if some of your code is accessible in SQL (saves you having to deploy new EXEs to fix bugs in SQL).

Berserker · 21 Dec 2006 at 14:59

Stored Procedures are best used to good effect when you are not doing the following:

Using MySQL. Stored Procedures were added as an afterthought to MySQL - they have some effect, but it's not as significant as it is in other databases.
Only going to run the query once. Obvious really - the overhead of creating the stored procedure would be the same or more than the overhead of just parsing the original query.
Running a slow query - in which case the parsing overhead becomes insignificant compared to the time taken to actually get the data.

ByteJuggler · 21 Dec 2006 at 16:48

I'd have to check this to be sure, but IIRC there's very little in it, providing that you use parameterized queries, and not purely dynmically generated SQL with statically encodes parameter values. E.g. simplistically, suppose you ran 3 queries like so (in pseudocode):

SQLText = "select * from table where x = 1;"
Execute()
SQLText = "select * from table where x = 2;"
Execute()
SQLText = "select * from table where x = 3;"
Execute()

In this case, each of these queries would be seperately optimised/compiled for execution, incurring the cost 3 times. However if your code did:

SQLText = "select * from table where x = ?;"
Param[0] = 1
Execute()
Param[0] = 2
Execute()
Param[0] = 3
Execute()

Then the optimisation would happen only once. Even if you replaced the SQL text everytime, as long as the text was the same, the plan would already be cached by the server and would not have to be redone. So as long as you use parameterized queries, you should get nearly SP like performance, IIRC.