|
|
|
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 2:52 AM
Points: 7,
Visits: 103
|
|
Hi all,
We have a stored procedure that run a INSERT statement:
INSERT (c1, c2, c3, ...)
select (c1, c2, c3)
from ...
The select part query is a complex query. If we execute the INSERT query in a new query window it costs 1-2 minute (for us it is good time).
However if we execute the stored procedure in a new query window it cost more than 15 minutes (we have to cancel after this time because the execution does not end durign this time).
Someone can help me? why this succeeded? how we can improve the stored procedure performance?
Many thanks in advance.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 17,103,
Visits: 12,204
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 2:52 AM
Points: 7,
Visits: 103
|
|
Hi,
Thanks for you reply.
I have been doing many test during the last week and i detect that the problem probably is the 'Parameter sniffing'.
In this URL are a general article about this tecnique (it is spanish): http://grimpidev.wordpress.com/2009/01/23/parameter-sniffing/.
As you can see in this article to solve the 'Parameter sniffing' are some solution. In my case using local variables in the stored procedure (to solve the Parameter sniffing) the stored procedures run in 2 min.
I don't like very much this solution but i don´t find any other (i test adding new indexes, optimizing query etc ...)
Someone could give me any other alternative solution?
Many thanks in advance.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 17,103,
Visits: 12,204
|
|
ico-601891 (11/24/2009)
Someone could give me any other alternative solution?
Maybe, if you can post the full stored procedure, some info on the tables and indexes and the execution plan, as previously requested.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 1,788,
Visits: 2,455
|
|
simple answer since you gave us to little to go on is to us dynamic sql in your sproc which should get you exactly the same performance you would see when running the query directly.
Best,
TheSQLGuru
SQL Server MVP
|
|
|
|
