Bridge Table Problem in T-SQL

View previous topic View next topic Go down

Bridge Table Problem in T-SQL

Post  lgroff1815 on Sun Feb 27, 2011 5:48 pm

I have a T-SQL statement that is giving me problems.
If the last part of the where clause is not included, runs in about 17 seconds against a 120GB DW/Star Schema SQL 2005 Enterprise
Returns about 255000 rows out of about 25-30 million rows.

If I include the last part of the where clause, it slows down to over 5 minutes (killed before it finishes) Indexes exist on all joined columns and
a covering index exists on DimDiagnosis that includes DiagnosisID, Diagnosis_Aggregate, and Diagnosis_Aggregate_Label.
Query Analyzer shows nothing.
The execution plan for the second statement adds a costly hash match compared to the first.

I realize that I can restructure the T-SQL into parts, but that is not the point for me.
I am trying to understand if this is the nature of BridgeTables, am I missing something or something else.


DimDiagnosis has 700 rows, DimDiagnosisBridge has about 126000

SELECT DISTINCT
FP.client_id ,
DD.Diagnosis_Aggregate_Label
FROM factpaid FP
INNER JOIN dbo.DimDiagnosisBridge DB ON FP.DiagnosisGroup_ID = DB.DiagnosisGroup_ID
INNER JOIN dbo.DimTime DT ON DT.Date_ID = FP.ServiceBegin
INNER JOIN DimDiagnosis DD ON DD.Diagnosis_ID = DB.Diagnosis_ID
WHERE DT.Year_Value BETWEEN 2006 AND 2010
AND FP.DataType_ID <= 3
AND FP.DataSource_ID = 2
--AND DD.Diagnosis_Aggregate = 1


lgroff1815

Posts : 1
Join date : 2011-02-27

View user profile

Back to top Go down

Re: Bridge Table Problem in T-SQL

Post  VHF on Mon Feb 28, 2011 6:04 pm

You might see if the execution plan is any different if you move the last part of the WHERE clause into the INNER JOIN on your DimDiagnosis dimension:

SELECT DISTINCT
FP.client_id ,
DD.Diagnosis_Aggregate_Label
FROM factpaid FP
INNER JOIN dbo.DimDiagnosisBridge DB ON FP.DiagnosisGroup_ID = DB.DiagnosisGroup_ID
INNER JOIN dbo.DimTime DT ON DT.Date_ID = FP.ServiceBegin
INNER JOIN DimDiagnosis DD ON DD.Diagnosis_ID = DB.Diagnosis_ID AND DD.Diagnosis_Aggregate = 1
WHERE DT.Year_Value BETWEEN 2006 AND 2010
AND FP.DataType_ID <= 3
AND FP.DataSource_ID = 2

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum