Individual pieces in query run fast, whole thing runs very slow

[millerk]

I've got a stored procedure that runs a number of different queries. Most of these involve doing joins, updates, and inserts with tables on two different servers.

There are about 8 different queries that get run in this stored procedure. If I run them individually, there is one that takes 17 seconds and the rest are all less than one or two seconds. If I highlight all the code and run it, it takes about a minute. If I execute the stored procedure, it runs for at least 5 minutes.

Anything I can look for?

 

[gmmastros]

Off the top of my head, I would suspect [google]parameter sniffing[/google].

The first thing you should try is adding the 'WITH RECOMPILE' option to the stored procedure.

Ex:

Create Procedure ProcedureName
  @Param1 Int,
  @Param2 VarChar(20)
[!]With Recompile[/!]
As
etc....

Most of the time, the 'with recompile' option will resolve parameter sniffing issues.

Also, I happen to think 17 seconds is an eternity. Often times, you can add an index (or 2) to your tables to fix performance issues. Other times, it's a matter of getting the query to use existing indexes. As such, I encourage you to read a little about [google]sql server sargable where clause[/google].

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom