CODEWITHSUNDEEP
sqlsql-server
user1652427
user1652427

Reputation: 697

Is there a way to use a variable in your select statement for multiple columns?

I have a dynamic piece of SQL. It takes about 4 minutes to run. If I instead make it static SQL, it takes about 20 seconds.

Pretty much, the two queries are:

@myVar = 1
SELECT *
FROM TABLE
WHERE someColumn = myVar

vs:

@myQuery = '
SELECT *
FROM TABLE
WHERE someColumn = myVar'

EXEC sp_executesql @myQuery,
    N'@myVar INT,
    @myVar

My real query is much much more complicated. Looking at the statistics, the dynamic one has more than 10x the number of reads. I want to make this query static to fix this. The whole reason it's dynamic though is that my insert and select is made using a variable.

I have something like:

@someVar1 = "column1, column2, column3"
@someVar2 = "column4, column5, column6"

and then will have

@myQuery = 'INSERT INTO '+ @someVar1 + 
           'SELECT ' + @someVar2 + ' FROM ....'

etc.

Is there any way I could just do something like:

INSERT INTO @myVar1
SELECT @myVar2
FROM
...

I don't know how else to fix this query, and it desperately needs fixing.

EDIT I made some changes. I forgot to mention that the dynamic stuff is also in an insert statement. This is what happens when I make a post after just waking up.

Upvotes: 0

Views: 122

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

You are chasing unicorns. SQL Server will not let you replace @var with column1, column2 in a non-dynamic query. The syntax just doesn't support what you would like it to do (not that it would improve the situation anyway).

So you need to solve this "problem" in other ways. I find it extremely difficult to believe that the dynamic version of the same query leads to a 10x displacement in reads. If this is the case (and you aren't just exaggerating for effect - please show evidence), it is almost certainly due to parameter sniffing where the (static) query that is cached used parameters that led to one plan shape, and that plan shape is not optimal for the other (dynamic) query which either has different query text or different parameters or both. You can counteract that to some degree by forcing RECOMPILE at runtime and ensuring the query text is identical every time (this includes white space, capitalization, etc.).

SET @myQuery = N'INSERT dbo.DestinationTable(' 
  + @DestinationColumns + ')
  SELECT ' + @SourceColumns + '
  FROM dbo.SourceTable
  WHERE someColumn = @myVar OPTION (RECOMPILE);';

But I'm not convinced that's your issue, either. I think it may be more perception/fear than actual observation.

As I mentioned in a comment, if you are building a lot of different dynamic SQL statements, you should consider turning on the optimize for ad hoc workloads setting, which will prevent your plan cache from getting bloated with plans that you won't ever use again.

The free version of SQL Sentry Plan Explorer will allow you to obfsucate your plans quite easily in about half a second, and even let you upload them where we can look at them. This won't let us dissect the actual query text, but it will show that there are differences in the plan for the query itself - which it doesn't really matter if the query was constructed dynamically or not, just that it is different. Disclaimer: I work for SQL Sentry.

Upvotes: 1

Related Questions