November 20, 2009 at 2:35 am
Hi guys hope someone can help.
I have a query that gets data to be inserted in a datawarehouse. The query is doing a table scan on the main table. I have tried quite a few combinations and diff indexes. The main table does not have a clustered index (problem 1). It does a sort that takes 83% and this is what I need to eliminate.
Current indexes on T1 (Main Table) -
Pk idx1 col's: Binary_key (Nonclustered)
This is the structure of the query
Select
Case when T1.Address_Number = 0 AND T1.Document_Type in ('JI','JO','T4','TE') THEN Cast(T1.Company As Float) ELSE T1.Address_Number END As BBBEE_Address_Number,
1 As Counter,
T1.Document_Type,
T1.GL_Offset,
T1.Supplier_Invoice_Number,
IsNull(T18.Date_BK,1) As Invoice_Date,
T1.Account_ID ,
Case when isnumeric(substring(serial_Number,2,24)) = 0 OR len(serial_Number) = 0 THEN 0 else Cast(substring(Serial_Number,2,len(Serial_Number)) As int) END As Asset_Item_Number,
Isnull( T7.Time_BK ,1 ) as Time_BK,
IsNull(T8.Business_Unit_BK,1) as Business_Unit_BK,
T1.Ledger_Type as Ledger_Type,
T1.Company as Company,
T1.Address_Number as Address_Number,
Case when isnumeric(T1.Sub_Ledger )= 0 OR T1.Sub_ledger_type != 'W' then 0 else Cast(T1.Sub_Ledger As Float) end as Sub_Ledger,
IsNull(T16.Date_BK,1) as GL_Date,
IsNull(T17.Date_BK ,1) as Batch_Date,
T1.Reconciled_code,
T1.Batch_Type,
SUM(T1.Amount) As Amount,
SUM(T1.Calculated_AC) As Calculated_AC,
getDate() As modify_Date_Stamp
From
T1 with (nolock)
Left Outer Join
T2
on
T1.company = T2.Company
Left Outer Join
T7
on
T7.Fiscal_Pattern_BK = T2.Fiscal_Date_Pattern and
T7.Fiscal_Period_End_Date = T1.Period_End_Date and
T7.Fiscal_Period = T1.Period_Number
Left Outer Join
T8
on
LTrim(RTrim(T8.Business_Unit_Code)) = LTrim(RTrim(T1.Business_Unit))
Left Outer Join
T16
on
T1.Capture_Date = T16.Calendar_Date AND
T2.Fiscal_Date_Pattern = T16.Fiscal_Pattern_BK
Left Outer Join
T17
on
T1.Batch_Date = T17.Calendar_Date AND
T2.Fiscal_Date_Pattern = T17.Fiscal_Pattern_BK
Left Outer Join
T18
on
T1.Invoice_Date = T18.Calendar_Date AND
T2.Fiscal_Date_Pattern = T18.Fiscal_Pattern_BK
Where
T1.GL_Posted_code = 'p' AND
T7.Fiscal_Year > 2007 AND
Cast(T1.Account_Id As int) > 2394039
Group by
Case when T1.Address_Number = 0 AND T1.Document_Type in ('JI','JO','T4','TE') THEN Cast(T1.Company As Float) ELSE T1.Address_Number END,
T1.Document_Type,
T1.GL_Offset,
T1.Supplier_Invoice_Number,
IsNull(T18.Date_BK,1),
T1.Account_ID ,
Case when isnumeric(substring(serial_Number,2,24)) = 0 OR len(serial_Number) = 0 THEN 0 else Cast(substring(Serial_Number,2,len(Serial_Number)) As int) END,
Isnull( T7.Time_BK ,1 ) ,
IsNull(T8.Business_Unit_BK,1) ,
T1.Ledger_Type ,
T1.Company ,
T1.Address_Number ,
Case when isnumeric(T1.Sub_Ledger )= 0 OR T1.Sub_ledger_type != 'W' then 0 else Cast(T1.Sub_Ledger As Float) end ,
IsNull(T16.Date_BK,1) ,
IsNull(T17.Date_BK ,1) ,
T1.Reconciled_code,
T1.Batch_Type
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
November 20, 2009 at 5:58 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply