4

I have a simple SELECT statement with a couple columns referenced in the WHERE clause. Normally I do these simple ones in the VB code (setup a Command object, set Command Type to text, set Command Text to the Select statement). However I'm seeing timeout problems. We've optimized just about everything we can with our tables, etc.

I'm wondering if there'd be a big performance hit just because I'm doing the query this way, versus creating a simple stored procedure with a couple params. I'm thinking maybe the inline code forces SQL to do extra work compiling, creating query plan, etc. which wouldn't occur if I used a stored procedure.

An example of the actual SQL being run:

SELECT TOP 1 * FROM MyTable WHERE Field1 = @Field1 ORDER BY ID DESC
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kane Jeeves
  • 475
  • 2
  • 9
  • 19
  • 1
    posting the code would be helpful to answer the question. – Taryn Aug 22 '12 at 15:09
  • possible duplicate of [Ad hoc queries vs stored procedures vs Dynamic SQL](http://stackoverflow.com/questions/2934634/ad-hoc-queries-vs-stored-procedures-vs-dynamic-sql) – dash Aug 22 '12 at 15:14
  • 1
    Whatever you do, use parameters in your sql! The only way to really check your performance bottleneck is to profile your query, but starting off with parameterized sql, either via inline sql or a stored proc, is a good start. – dash Aug 22 '12 at 15:15
  • SELECT TOP 1 * FROM MyTable WHERE Field1 = @Field1 ORDER BY ID DESC (ID being the identity seed first column) – Kane Jeeves Aug 22 '12 at 15:18
  • What does the execution plan look like? Is `Field1` indexed? Might you be encountering blocking from concurrent data modification statements? – Martin Smith Aug 22 '12 at 15:22
  • Query is parametrized - that's a plus! – marc_s Aug 22 '12 at 15:24

4 Answers4

5

A well formed "inline" or "ad-hoc" SQL query - if properly used with parameters - is just as good as a stored procedure.

But this is absolutely crucial: you must use properly parametrized queries! If you don't - if you concatenate together your SQL for each request - then you don't benefit from these points...

Just like with a stored procedure, upon first executing, a query execution plan must be found - and then that execution plan is cached in the plan cache - just like with a stored procedure.

That query plan is reused over and over again, if you call your inline parametrized SQL statement multiple times - and the "inline" SQL query plan is subject to the same cache eviction policies as the execution plan of a stored procedure.

Just from that point of view - if you really use properly parametrized queries - there's no performance benefit for a stored procedure.

Stored procedures have other benefits (like being a "security boundary" etc.), but just raw performance isn't one of their major plus points.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

It is true that the db has to do the extra work you mention, but that should not result in a big performance hit (unless you are running the query very, very frequently..)

Use sql profiler to see what is actually getting sent to the server. Use activity monitor to see if there are other queries blocking yours.

Ray
  • 21,485
  • 5
  • 48
  • 64
  • Ya it does can run extremely frequently, like 100 - 500 per min. – Kane Jeeves Aug 22 '12 at 15:16
  • then put it in a stored proc - that should make a difference - or parameterize, as marc_s suggests (I personally find stored procs easier to code and maintain) – Ray Aug 22 '12 at 15:18
0

Your query couldn't be simpler. Is Field1 indexed? As others have said, there is no performance hit associated with "ad-hoc" queries.

For where to put your queries, this is one of the oldest debates in tech. I would argue that your requests "belong" to your application. They will be versionned with your app, tested with your app and should disappear when your app disappears. Putting them anywhere other than in your app is walking into a world of pain. But for goodness sake, use .sql files, compiled as embedded resources.

bbsimonbb
  • 27,056
  • 15
  • 80
  • 110
0

Select statement which is part of form clause of any another statement is called as inline query. Cannot take parameters. Not a database object

Procedure: Can take paramters Database object can be used globally if same action needs to be performed.

user11022700
  • 1