Difference in execution plan for stored porocedure and straight queries

Question

Difference in execution plan for stored porocedure and straight queries

Simon Dennison

SSC Rookie

Points: 31
More actions
June 30, 2008 at 6:47 am

#188540

I have a stored procedure that is taking about 30 seconds to run (in our underpowered test environment), when I run the contents of the SP directly, it takes 2 seconds!
I've looked at the execution plans for both and one of the queries differs in the order the joins are handled.
The offending query selects data from four tables that are all INNER JOINed, and a function is called on two of the columns (from two different tables) in the WHERE clause. This is a decryption function that checks that the decrypted values match, and has a significant cost.
I think that this where clause and function is being executed where the two tables are joined, and because the tables are joined in a different order they are run over many more rows.
The query looks something like this:
SELECT *
FROM t1
INNER JOIN t2 ON t1.t2_id = t2.id
INNER JOIN t3 ON t2.t3_id = t3.id AND t3.date < GETDATE()
INNER JOIN t4 ON t3.id = t4.id = t3.t4_id AND t4.col2 = 1
WHERE decrypt(t1.col2) = decrypt(t2.col3)
Is there a way to force the execution plan to do it's work in the order I specify? I've tried using sp_recompile, but the execution plan has not changed.
Any help, links to articles, or other suggestions welcome.
Many thanks, Simon.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

In general, no, and you mostly don't want to force the optimiser to do things a specific way, as it usually knows better.

Could you post the execution plans in xml format please (save as a .sqlplan, zip and attach to your post)

What indexes do you have defined on the tables?

It probably won't make a difference to the plan, but the filters (t3.date < GETDATE()) should be in the where clause, not in the from clause

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

If you specify a local join hint (LOOP / HASH / MERGE) on one of your joins, the optimizer can be forced into enforcing the join order you have specified in the query.

You will get this warning:

"Warning: The join order has been enforced because a local join hint is used."

This is typically something to be avoided. It is best to try to figure out why the optimizer is giving you the bad join order and fix that issue. This could be because of statistics, parameter sniffing, poorly written queries, etc. If you post the plans, we can probably help you resolve this issue. This is a better approach than specifying hints in most cases because the optimizer will be able to handle future conditions that you may not anticipate.

You are already in a bit of trouble because of the condition using functions - this is going to cause some scanning because of a lack of ability to use indexes.

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 3

It's probably parameter sniffing. As everyone else has already said, get the execution plans and see what the differences are.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Simon Dennison SSC Rookie Points: 31 More actions · Answer 4

Thanks for all your quick responses.

I've hopefully attached the execution plan. I've had to change all the table and column names as per company policy, sorry.

Basically, query 1 is it being run in the SP, query 2 is as standalone. Following the icons from left to right, along the top, my problems start at the 9th icon. From here on in, the query is as described in my previous post.

In query 1, it looks to me like t1 is joined to t3 first and the decryption function is run, this result set is then joined to t4 and then to t2.

In query 2, t3 and t4 are joined, then to t2, and finally to t1 where that decrypt function is run accross much fewer rows.

The clauses in the IN parts of the joins were put there to suggest that these should be done first.

As Michael has pointed out, we can't index the encrypted columns becaue they have to be run through the function first, we normally avoid comparing on the encrypted columns at all.

We can take the hit of 2 seconds, but not 30. I just dont understand why a different execution plan is created for the SP from that of running the query seperately.

Many thanks, Simon.

Simon Dennison SSC Rookie Points: 31 More actions · Answer 5

Interestingly, and rather annoyingly, by adding a hint as per Michaels post, it shaves half a second off by joining in the order we specified. It's now running at 1.5 seconds!

I understand this isn't best practice, but Machael, thank you very much.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

You should be a bit worried by that.

Make sure your statistics are up-to-date. From what I have seen, the query you are running is not complicated enough that SQL would have trouble finding the correct execution plan. What SQL version, service pack, and compatibility level are you running?