Query Tune - 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 2000 Forums

Transact-SQL (2000)

Query Tune

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-23 : 02:17:25

Dear All,

Please Look up this query.

if Exists (
select 'x'
From P21_Pay_History_Detail P21 (nOLOCK)
where C03_Organisation_code = 'UNRWACON'
AND C29_Location_Code = 'CON'
AND P06_Payroll_Code = 'asp'
AND P03_Process_Period = '74'
AND P09_Element_Code <> 'GR'
AND NOT EXISTS (

SELECT 'X'
From P43_Salary_Account A (nOLOCK)
where C03_Organisation_code = P21.C03_Organisation_code
and C29_Location_Code = P21.C29_Location_Code
and P06_Payroll_code = p21.P06_Payroll_code
and P03_Process_Period = p21.P03_Process_Period
and H01_Emp_Num = P21.H01_Emp_Num
and P09_Element_Code = P21.P09_Element_Code
AND P21.H01_Emp_Num = A.H01_Emp_Num
)
Select 'test'

the P21_Pay_History_Detail table has 8 718 706 records
and P43_Salary_Account table has 11 773 455 records

It's Very slow
help me please
Thanks
KK

Kristen
Test

22859 Posts

Posted - 2006-05-23 : 02:31:58

Is this any better?


if Exists (
select *
From P21_Pay_History_Detail AS P21 (nOLOCK)
     JOIN From P43_Salary_Account AS A (nOLOCK)
          ON A.C03_Organisation_code = P21.C03_Organisation_code
         and A.C29_Location_Code = P21.C29_Location_Code
         and A.P06_Payroll_code = p21.P06_Payroll_code
         and A.P03_Process_Period = p21.P03_Process_Period 
         and A.H01_Emp_Num = P21.H01_Emp_Num
         and A.P09_Element_Code = P21.P09_Element_Code 
         AND A.H01_Emp_Num = P21.H01_Emp_Num
where     P21.C03_Organisation_code = 'UNRWACON'
      AND P21.C29_Location_Code = 'CON'
      AND P21.P06_Payroll_Code = 'asp'
      AND P21.P03_Process_Period = '74'
      AND P21.P09_Element_Code <> 'GR'
)
Select 'test'

if not please post the query plan so we can see what needs indexing.

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-23 : 02:36:18

Dear Kristen. Just I am rearranging the index structure by

Create clustered index P43_Salary_Account_Indx on P43_Salary_Account (H01_Emp_Num,P09_Element_Code,P03_Process_Period,P06_Payroll_code)

Create Nonclustered index P43_Salary_Account_Indx1 on P43_Salary_Account(C29_Location_Code

So it will take time. I will keep posted.
thanks
KK

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-23 : 03:52:51

Dear Kristen.Thanks I Got it the result from same query with in5 seconds. the index is did a great work for me

Thanks for ur suggession
KK

Kristen
Test

22859 Posts

Posted - 2006-05-23 : 06:26:53

You need an index on both P21_Pay_History_Detail and P43_Salary_Account, and it needs to "cover" as many columns as sensible in:

C03_Organisation_code
C29_Location_Code
P06_Payroll_Code
P03_Process_Period

on P21_Pay_History_Detail and

C03_Organisation_code
C29_Location_Code
P06_Payroll_code
P03_Process_Period
H01_Emp_Num
P09_Element_Code
H01_Emp_Num

in P43_Salary_Account. Also, the most "selective" column should be first in the index.

It should not take 5 seconds to do an EXISTS, even on a huge database.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources