Dynamic SQL taking much much longer than the hard-coded equivalent

Question

I have a dynamic piece of SQL. It takes about 4 minutes to run. If I instead take the output of the SQL and run that instead, it takes about 20 seconds. Why the discrepancy? I know it would take some amount of time to build up the SQL in the dynamic version, but I can't imagine it's THAT expensive.

Anyone have any ideas? The two queries should be identical, so I was suspecting it was something weird with query plan caching, but don't really have much of an idea.

Edit: To clarify what I mean by taking the output.

In the dynamic SQL the last line is

EXEC sp_executesql @myQuery,
    N'@var1 INT,
    @var2 INT,
    @var2 INT',
    @var1,
    @var2,
    @var3

I took the value of myQuery and put it in its own SQL file. That is running at 20 seconds while the dynamic one that uses execute takes 4 minutes.

Edit 2 I removed the parameters. I got interesting results. The dynamic SQL statement saw a performance improvement. The hardcoded version saw a huge performance hit. The two are about equal now.

Answer 1

I am going to assume you are using Microsoft SQL Server (you only tagged your question "sql").

There are some cases where different parameter values can result in a different optimization plan. Then that optimization plan is cached, and used the next time you execute the query with different parameter values. But the optimization plan isn't the best plan for the subsequent parameter values.

Here's an article about this problem and some workarounds: https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

So yes -- there are some cases where using a parameterized query can result in poor performance compared to running the same query without parameterization.

We can't know if this applies in your case if you aren't at liberty to post your code.

I respect that you can't do that -- by posting to StackOverflow, you implicitly license your code and/or words with a Creative Commons license. But it would not be appropriate to share code that is owned by your employer, unless they agree to it.