I need to optimizate a query that take a lot of time.

Question

I need to optimizate a query that take a lot of time.

dvora.id

Mr or Mrs. 500

Points: 549
More actions
January 19, 2009 at 5:03 am

#125773

Hi,
I created a selective insert query.
When I’m activating the update part the query takes a lot of time more than hour.
I think that I’ll need to insert some index in order to improve the work, but I never did that
Can somebody help me?
Please, see attach the update part:
UPDATE dbo.tblSDHRemployees
SET AD_Username = ADID, employee_number = EE_NO,
[full name] = DISPLAY_NAME,
dbo.tblSDHRemployees.REGION = dbo.vwmilpitas.REGION,
dbo.tblSDHRemployees.LOCATION = dbo.vwmilpitas.LOCATION,
entity_code= ENTITY_COD, [First Name] = EE_FIRST_NAME,
[Last Name] = EE_LAST_NAME, JOB_TITLE = TITLE,
EMPLOYMENT_DATE = DOH, [E-mail Address] = EMAIL_ADDRESS,
MnGR_code = (CASE WHEN isnumeric(MGR_NO)
= 1 THEN cast(MGR_NO AS int)
ELSE NULL END), MANAGER = MGR_NAME, VP_CODE = VP_G,
VP_name = VP_GROUP_NAME, DEPT_CODE = LEFT(DEPT, 3),
DEPARTMENT = DEPT, ORG_FUNCTION = ORG_FUNC, ORG = P_L
FROM dbo.vwmilpitas
WHERE EXISTS
(SELECT *
FROM dbo.tblSDHRemployees
WHERE employee_number = ee_no)
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Please post table definitions, index definitions and execution plan (saved as a .sqlplan file, zipped and attached)

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

dvora.id (1/19/2009)
Hi,
I created a selective insert query.
When I’m activating the update part the query takes a lot of time more than hour.
I think that I’ll need to insert some index in order to improve the work, but I never did that
Can somebody help me?
Please, see attach the update part:
UPDATE dbo.tblSDHRemployees
SET AD_Username = ADID, employee_number = EE_NO,
[full name] = DISPLAY_NAME,
dbo.tblSDHRemployees.REGION = dbo.vwmilpitas.REGION,
dbo.tblSDHRemployees.LOCATION = dbo.vwmilpitas.LOCATION,
entity_code= ENTITY_COD, [First Name] = EE_FIRST_NAME,
[Last Name] = EE_LAST_NAME, JOB_TITLE = TITLE,
EMPLOYMENT_DATE = DOH, [E-mail Address] = EMAIL_ADDRESS,
MnGR_code = (CASE WHEN isnumeric(MGR_NO)
= 1 THEN cast(MGR_NO AS int)
ELSE NULL END), MANAGER = MGR_NAME, VP_CODE = VP_G,
VP_name = VP_GROUP_NAME, DEPT_CODE = LEFT(DEPT, 3),
DEPARTMENT = DEPT, ORG_FUNCTION = ORG_FUNC, ORG = P_L
FROM dbo.vwmilpitas
WHERE EXISTS
(SELECT *
FROM dbo.tblSDHRemployees
WHERE employee_number = ee_no)
Thanks in advance

I think the problem with your query is that you update one table with the data from another table, without referencing to the updated table in the FROM clause. I rewrote the query, make sure the join is as unique as it should be. I also made aliases for the tables as much as possible, you can of course change this.UPDATE SDHR

SET AD_Username = VWM.ADID, employee_number = VWM.EE_NO,

[full name] = VWM.DISPLAY_NAME,

dbo.tblSDHRemployees.REGION = VWM.REGION,

dbo.tblSDHRemployees.LOCATION = VWM.LOCATION,

entity_code= VWM.ENTITY_COD, [First Name] = VWM.EE_FIRST_NAME,

[Last Name] = VWM.EE_LAST_NAME, JOB_TITLE = VWM.TITLE,

EMPLOYMENT_DATE = VWM.DOH, [E-mail Address] = VWM.EMAIL_ADDRESS,

MnGR_code = (CASE WHEN isnumeric(VWM.MGR_NO)

= 1 THEN cast(VWM.MGR_NO AS int)

ELSE NULL END), MANAGER = VWM.MGR_NAME, VP_CODE = VWM.VP_G,

VP_name = VWM.VP_GROUP_NAME, DEPT_CODE = LEFT(VWM.DEPT, 3),

DEPARTMENT = VWM.DEPT, ORG_FUNCTION = VWM.ORG_FUNC, ORG = VWM.P_L

FROM dbo.tblSDHRemployees SDHR

INNER JOIN dbo.vwmilpitas VWM ON SDHR.employee_number = VWM.ee_no

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

dvora.id Mr or Mrs. 500 Points: 549 More actions · Answer 3

thanks Ronald ,

You're right.

The table now is working faster even that i didn't add index.

Thanks every body!

Deby

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 4

I'm glad to help... For understanding what went wrong, you might check out this article: http://www.sqlservercentral.com/articles/Performance+Tuning/62278/

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

dvora.id Mr or Mrs. 500 Points: 549 More actions · Answer 5

dvora.id

Mr or Mrs. 500

Points: 549

January 19, 2009 at 8:25 am

#929251

thanks for the tip.

It is realy helpful!