Problem understanding an execution plan

Question

Problem understanding an execution plan

Corobori

Ten Centuries

Points: 1316
More actions
October 21, 2005 at 3:21 pm

#110633

I thinking that a Stored Procedured I am running is a bit slow so I decided to check it in the Execution Plan. I pasted it below.
I red marked a couple of items where it seems the most of time is used:
1) I am wondering why it takes so much time to execute this "SELECT @Aif_Id=Ai_Id FROM inserted"
2) Also in my SP there is this statement "IF EXISTS(SELECT * FROM inserted WHERE Ai_Sospechoso=1 )"
Based on this Execution Plan, what should I do to improve this ?

Jean-Luc
www.corobori.com

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

1)If this is for use in a TRIGGER, I must mention that the triggers fires only once per command/set. So if inserted contains more than 1 ai_id (like 1,2,3)you will only have 1 in @aif_id (lets say 2). Same with Aif_Edicion

"SELECT @Aif_Id=Ai_Id FROM inserted" = scan the whole table and pick only one.

2)Not sure if indexes on tblAvisosInternet will help. If you try to search on boolean variables, convert(bit,value) usually helps selecting the proper index.

2. is SET NOCOUNT ON present in your statement? (helps reducing a roundtrip)

3.Currently you are not specifying an owner? Therefor sql server has to check if there isn't another table called tblAvisosInternet. Generally all objects are owned by dbo and other uses are granted use of it (since he/she approves which object will be allowed.

Corobori Ten Centuries Points: 1316 More actions · Answer 2

Thanks for your tips. I actually reengineered the triggers and the stored procedure because some triggers where written a long time ago when part the insert was done manually with a SQL Statement. In other words the job done by one trigger was more easily done by the new stored procedure.

Anyways if somebody knows a website learning how to read the execution trace it would be nice.

Jean-Luc
www.corobori.com

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

this should help you started:

http://www.sql-server-performance.com/query_execution_plan_analysis.asp

in addition a new article about indexes:

http://www.sqlservercentral.com/columnists/lPeysakhovich/indexcreationguidelines.asp

and the follup up discussion

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=163&messageid=119673&p=2