March 31, 2011 at 7:26 am
This is another execution plan
March 31, 2011 at 7:27 am
Which update statment giving me a problem?
Thank you
March 31, 2011 at 7:38 am
Krasavita (3/31/2011)
Hi, here is another stored procedure I found with a problem with nested Loop.Can you please help me to identifiy which update statment is wrong.Thank you so much
You're still looking at a lot of the same situation. You still don't have proper joins between your tables. You're getting table scans and index scans. You really should contact a consultant. Have you addressed the issues or possible solutions that several of us have brought forward? From what I'm seeing on this query, the answer is no. If you're not going to do the things we suggest, why are you continuing to ask questions? If you're looking for the "run faster" switch, it just isn't there.
"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
March 31, 2011 at 7:44 am
I am sorry, I didn't right this code and I don't know which statment is causing a problem, I will re write the statment after I find out which update is a problem. Is it both of them?
March 31, 2011 at 8:10 am
Krasavita (3/31/2011)
I am sorry, I didn't right this code and I don't know which statment is causing a problem, I will re write the statment after I find out which update is a problem. Is it both of them?
As I said on Page 2 of this thread:
UPDATE DLT_Employee
SET
BU=ne.Business_Unit_Number ,
Office_Id=ne.Office_Location ,
Job_Code=ne.Job_Code ,
Sector_Id=ne.Sector
FROM [DB1].MIT.dbo.National_Employees ne, DLT_Employee de
WHERE de.DLT_Emp_Id=@NED_Emp_ID
AND de.DLT_Emp_Id=ne.Address_Book_ID
Doesn't have criteria to join between the DLT_Employee and the other tables (I think that, based on what I'm seeing the exec plan National_Employees is a view). You're also getting a table scan on DLT_Employee caused by Emp_ID. So there's evidently no cluster on that table and no non-clustered index that can help. I'd get an index there, preferably a good cluster.
The other update
Update dbo.NED_Employee
SET
Last_Name=ne.Last_Name ,
First_Name=ne.First_Name ,
Date_Of_Birth=ne.Birth_Date ,
Country_Of_Birth=ne.Birth_Country ,
Manager_ID=ne.Supervisor_UserID ,
Job_Title=ne.Job_Title ,
Hire_Date=ne.Hire_Date ,
Department_ID=d.Department_ID,
Termination_Date=ne.Term_Date
FROM [DB1].MIT.dbo.National_Employees ne, dbo.ADP_Department d, NED_Employee ned
WHERE ned.NED_Emp_ID=@NED_Emp_ID
and LTRIM(RTRIM(ne.Office_Location))=d.Office_Id
AND ned.NED_Emp_ID=ne.Address_Book_ID
-- Insert new records into DLT_Employee
Has another remote query into a view, I think. You're also getting a clustered index scan, probably caused by the function on the LTRIM & RTRIM functions on ne.Office_Location. You can't have functions on columns in the join or where clause without getting scans.
Start fixing these things.
"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
March 31, 2011 at 9:50 am
Thank you so much,
I fixed
March 31, 2011 at 11:45 am
Krasavita (3/30/2011)
I resolved the problem,but not sure for 100%I ran update statment 2 hours on that table (I thought update was complited) in the query analyzer and tryed to close it just now and got message if I need to commited and I said yes and table is not locked anymore, why did I get this message?is that the reason table was locked?
Thank you
sounds like you ran:
BEGIN TRAN
UPDATE TABLE_A SET FLDA = 'value' etc .....
but you did not run:
COMMIT (to commit the transaction)
or
ROLLBACK (to undo the transaction)
The table will be locked until you run COMMIT or ROLLBACK
March 31, 2011 at 11:50 am
Grant Fritchey (3/31/2011)
Krasavita (3/31/2011)
I am sorry, I didn't right this code and I don't know which statment is causing a problem, I will re write the statment after I find out which update is a problem. Is it both of them?Doesn't have criteria to join between the DLT_Employee and the other tables (I think that, based on what I'm seeing the exec plan National_Employees is a view). You're also getting a table scan on DLT_Employee caused by Emp_ID. So there's evidently no cluster on that table and no non-clustered index that can help. I'd get an index there, preferably a good cluster.
Looks like old-style code where the "join" is listing the tables in the FROM clause, and the "on" criteria is in the WHERE clause.
March 31, 2011 at 12:16 pm
no, for some reasons when run any code and I close query analyzer, get an error message this code was not commit, even if I do select on the table and I don't know how to get rid of this
March 31, 2011 at 12:28 pm
Krasavita (3/31/2011)
no, for some reasons when run any code and I close query analyzer, get an error message this code was not commit, even if I do select on the table and I don't know how to get rid of this
There is a setting in management studio for this. It is called implicit transactions.
Read the following about it. You want to uncheck the box called implicit transactions.
http://blog.techdreams.org/2007/11/implicit-transactions-onoff-sql-server.html
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply