SQL Server Performance Forum – Threads Archive

simple selects

Hello In our software there are a lot of queries that just gets a bunch of rows from different tables, often joined tables, from the database (using ado). In some cases we use strings to build up a query, then execute it to retrieve the rows.
In other cases, we send arguments off to a corresponding SP that does the select. I most cases it starts out with the former and is moved to the latter when the query grows. My question is, is it generally better to use the former or the latter approach when it comes to performance? (I realize there’s probably only a minor difference, but since it’s no big problem to use one or the other you might as well chose the better one even if the difference is small.) thanks /Linus —
http://anticAPSLOCK.com

---

The later: 1. You send less text to server.
2. Sp is available to dba for optimization if necessary.
3. Using sp_executeSQL you can achieve reusing exec plan so regarding recompilation they are the same (in theory). One advantage dynamic sql may have is you can use constants instead of parameters and achieve better execution plan. We almost always use sp and if we find query slow because of bad execution plan, then we force one with hints or rewrite query (using table variables) to force specific execution path. This is posible almost allways, but there are exceptions. Sometimes the best execution plan depends too much on parameter value, so you can’t choose the best one that covers all.

---

Thanks for your input!
Another reason I found since I posted the question is that by using an SP you will get a row in sysdepends as well, which might make it easier to find referenced tables.
Of course, if you have to use dynamic sql in your sp’s thats probably void anyway? /l —
http://anticAPSLOCK.com

---

Just use sp_executeSQL then. The only exception is when the query is long running and reusing old execution plan causes unacceptably slow execution. In that case use constants in your dynamic sql instead of parameters.

---

yes dynamic SQL won’t be tracked in sysdepends. also sysdepends depends on the order in which procs, tables, etc. are created. e.g. if you drop and then recreate a table then SQL will not go through and work out which procs use this table Cheers
Twan

---

In other words, sysdepends are useless? /linus —
http://anticAPSLOCK.com

---

no it isn’t useless, but unless you have tight control over how the database objects are created/maintained then it is unreliable it is good to check which procs use which tables, but only if you don’t drop/recreate tables… (i.e. use the ALTER TABLE command…) Cheers
Twan

---

]]>