query tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query tuning

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-10-03 : 23:45:20

Hi all,

I have a below query which taken more than 10 hours to complete

insert into HCP
(DATEMODIFIED
,CATEGORY_ID
,TITLE_ID
,NAMEGIVEN
,NAMEMIDDLE
,NAMEFAMILY
,POSITION_ID
,GENDER_ID
,ACTIVEINDICATOR
,INACTIVEREASON_ID
,SPECIALTY_ID
,MOBILE,EMAIL
,COUNTRY_ID
,ISACTIVE
,NAMEPREFERRED
,DATECREATED
,Veeva_ID)

select DATEMODIFIED
,CATEGORY_ID
,TITLE_ID
,NAMEGIVEN
,NAMEMIDDLE
,NAMEFAMILY
,POSITION_ID
,GENDER_ID
,ACTIVEINDICATOR
,INACTIVEREASON_ID
,SPECIALTY_ID
,MOBILE
,EMAIL
,COUNTRY_ID
,ISACTIVE
,NAMEPREFERRED
,DATECREATED
,Veeva_ID
FROM JUMP_HCP_UPLOAD A
where not exists (select 1 from HCP f where f.Vee_ID=A.Vee_ID)

Here the HCP table contains morethan 80 Lack records and we all the index in tact for HCP table,
Could you please help me fine tune this SP.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-04 : 00:48:17

Is Vee_ID indexes in both tables? Show us the execution plan.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-10-04 : 01:00:38

I am sorry the execution plan is very big how can we attach execution plan to this reply.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-04 : 01:07:35

Sounds like you don't have it indexed properly then. What indexes do you have on these tables? Please post each of them.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-10-07 : 02:20:40

index_name index_description index_keys
IX_clustred_veeva_id nonclustered located on PRIMARY Veeva_ID ( on JUMP_HCP_UPLOAD table). Only this index and 10 K records are there.

index_name index_description index_keys
HCP_LegacyID nonclustered located on PRIMARY Country_ID, LegacyID
HCP29 nonclustered located on PRIMARY NameFamily
HealthCareProfessional_PK clustered, unique, primary key located on PRIMARY ID
IX_HCP_Country_ID_ID_NameGiven_NameFamily nonclustered located on PRIMARY Country_ID
IX_HCP_Country_ID_IsActive_ID nonclustered located on PRIMARY Country_ID, IsActive
IX_HCP_Country_ID_IsActive_ID_NameGiven_NameFamily nonclustered located on PRIMARY Country_ID, IsActive
IX_HCP_IsActive_ID nonclustered located on PRIMARY IsActive
IX_nonclustred_veevaid nonclustered located on PRIMARY Veeva_ID (on HCP table there 1 lack records are there)

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-07 : 12:39:54

You need an index on Vee_ID on both JUMP_HCP_UPLOAD and HCP tables.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-10-09 : 01:34:44

Is this a "one-off" data integration or is it a regular query? Depending on the situation , there are different tactics available to speed up the INSERT process.
Have you added the index mentioned?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources