Is this an efficient sql server query?

Question

Hi,

If I write a sproc in this format, will sql server do this effeciently or should the sql 'weaving' be done on the server side (.net)?

Note: this is just a rough idea of what my query looks like, there are more 'if' clauses the build up my query that I execute using 'exec'

declare @sql nvarchar(4000)
declare @order nvarchar(4000)


set @sql = 'select id, name'
set @sql = @sql + ' ....' 

if(@sortOrder)
    set @order = 'order by name desc'

exec @sql + @order

Answer 1

Do you have a good reason for doing this as dynamic SQL?

I've done what you have before in SQL server and it works fine; it's probably safer (depending on usage) than passing in fully formed SQL to a proc that just runs it blindly.

If you're using SQL 2k5 or 2k8, you can use nvarchar(max) for longer strings.

Answer 2

Yes.

It compiles in the "exec" command. The speed difference should be minimal between that, and doing it all on .NET side.

At some point and time, it compiles the SQL to some internal code. It can happen as .NET pushes it, or afterwards.

Answer 3

Instead of building up a SQL string, why not just use a case statement in the order by?

e.g. (assuming one is sorting by first and/or last name on a table with FirstName and LastName fields)

order by
  case 
  when @sortExpression = 'lastname asc' then CAST(LastName as sql_variant)
  when @sortExpression = 'firstname asc' then CAST(FirstName as sql_variant)
  end asc,
  case
  when @sortExpression = 'lastname desc' then CAST(LastName as sql_variant)
  when @sortExpression = 'firstname desc' then CAST(FirstName as sql_variant)
  end desc

In addition, if your order by clause is different each time via dynamic SQL, the query plan will never be re-used.

Answer 4

I would build the rest of the query up to the order by ie.

@sql = 'SELECT id, name FROM myTable ORDER BY ' + @order

... and pass the column name and direction to the proc. That way is more secure because not much can be passed to an order by clause to cause any harm.

To answer your question, yes, it is efficient.

Answer 5

I see two better ways to do this. Instead of using a dynamic SQL query, I would use an actual SQL statement in a stored procedure, cased by the things you want to order by:

select
  t.* 
from 
  table as t 
order by
  case @val
    when 1 then column1
    when 2 then column2
  end

If you find that the order by is too dynamic and must be built up, or is easer to do so, then I would create a table-valued function which returns the set, and then create a dynamic sql statement against that:

select
  t.*
from
  xfn_Function(@arg1, @arg2) as t
order by
  t.col1, t.col2

Where of course, t.col1, t.col2, etc, etc, are dynamically generated before you send the whole thing to the server.

Answer 6

The clearest, simplest, easiest, most optimizable way is to have complete SQL statements for each option.

Answer 7

To specifically answer your question, there will be no performance difference at all between a dynamic SQL statement which is formed in your .Net layer, and exactly the same SQL string which is formed inside a proc.

Answer 8

The most efficient is to get whatever data you want from the database and then handle the sorting in your application. Changing the order by isn't going to change the amount of data. If the user changes the sort order, why make another trip to the database?

Answer 9

Dynamic query performance is almost equivalent to server side weaving. if you can not find any other way to avoid dynamic query, nest it in server side.

following also works in case you do not have many "ifs"

if(@sortorder = 'name') select id, name from tbl order by name asc
if(@sortorder = 'id') select id, name from tbl order by id asc