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

Transact-SQL (2000)

Query Tuning

Author

Topic

kjmraohyd
Starting Member

34 Posts

Posted - 2006-08-07 : 02:43:44

Dear All,

Is there a way to fine tune the below query? We have PKs and SKs on
all defined joins:

TIA

SELECT DISTINCT TEMPFAC.Period_ID,Source_System_Facility_ID,
Credit_Unit,TEMPFAC.Customer_ID,TEMPFAC.Credit_Unit_ID,
ISNULL(Lead_Borrower_ID, 0) AS Lead_Borrower_ID,
TEMPFAC.Facility_ID,TEMPFAC.Bid_flag,
Borrower_Name AS Customer_Name,
Type AS Type,
Borrower_Facility_IG AS IG,
ISNULL(TEMPFAC.LGD_Value_Percent, 0) AS LGD_Value_Percent,
ISNULL(TEMPFAC.LGD_Grade_Value, 0) AS LGD_Grade_Value,
TEMPFAC.Facility_Currency_Code AS Currency_Code,
ISNULL(Credit_Facility_Deemed_Risk_Amount, 0) AS Credit_Facility_Deemed_Risk_Amount,
ISNULL(Credit_Facility_Deemed_Risk_Amount_CAD, 0) AS Credit_Facility_Deemed_Risk_Amount_CAD,
ISNULL(Credit_Facility_Deemed_Risk_Amount_USD, 0) AS Credit_Facility_Deemed_Risk_Amount_USD,
ISNULL(Credit_Facility_Notional_Principal_Amount, 0) AS Credit_Facility_Notional_Principal_Amount,
ISNULL(Credit_Facility_Mark_To_Market_Amount, 0) AS Credit_Facility_Mark_To_Market_Amount,
ISNULL(Credit_facility_Outstanding_Amount, 0) AS Credit_facility_Outstanding_Amount,
ISNULL(Credit_facility_Outstanding_Amount_CAD, 0) AS Credit_facility_Outstanding_Amount_CAD,
ISNULL(Credit_facility_Outstanding_Amount_USD, 0) AS Credit_facility_Outstanding_Amount_USD,
ISNULL(Borrower_Facility_Deemed_Risk_Amount, 0) AS Borrower_Facility_Deemed_Risk_Amount,
ISNULL(Borrower_Facility_Deemed_Risk_Amount_CAD, 0) AS Borrower_Facility_Deemed_Risk_Amount_CAD,
ISNULL(Borrower_Facility_Deemed_Risk_Amount_USD, 0) AS Borrower_Facility_Deemed_Risk_Amount_USD,
ISNULL(Borrower_Facility_Notional_Principal_Amount, 0) AS Borrower_Facility_Notional_Principal_Amount,
ISNULL(Borrower_Facility_Notional_Principal_Amount_CAD, 0) AS Borrower_Facility_Notional_Principal_Amount_CAD,
ISNULL(Borrower_Facility_Notional_Principal_Amount_USD, 0) AS Borrower_Facility_Notional_Principal_Amount_USD,
ISNULL(Borrower_Facility_Mark_To_Market_Amount, 0) AS Borrower_Facility_Mark_To_Market_Amount,
ISNULL(Borrower_Facility_Mark_To_Market_Amount_CAD, 0) AS Borrower_Facility_Mark_To_Market_Amount_USD,
ISNULL(Borrower_Facility_Mark_To_Market_Amount_USD, 0) AS Borrower_Facility_Mark_To_Market_Amount_CAD,
ISNULL(Borrower_Facility_Outstanding_Amount, 0) AS Borrower_Facility_Outstanding_Amount,
ISNULL(Borrower_Facility_Outstanding_Amount_CAD, 0) AS Borrower_Facility_Outstanding_Amount_CAD,
ISNULL(Borrower_Facility_Outstanding_Amount_USD, 0) AS Borrower_Facility_Outstanding_Amount_USD,
TEMPFAC.Os_As_At_Date AS Os_As_At_Date,
Special_Exclusion_Flag AS Special_Exclusion_Flag,
Facility_Class,
Facility_Status AS Facility_Status,
Active_Facility AS Active_Facility,
Excluded

FROM TEMP_CUSTFACILITY TEMPFAC

INNER JOIN PERIOD P ON
P.Period_ID = TEMPFAC.Period_ID

INNER JOIN CUSTOMER CUST ON
CUST.Period_ID = TEMPFAC.Period_ID
AND CUST.Credit_Unit_ID = TEMPFAC.Credit_Unit_ID
AND CUST.CParty_ID = TEMPFAC.Customer_ID -- Return 1361527 (00:03:27)

INNER JOIN CPARTY_FACILITY_AVAIL CUSTFAC_AVL ON
CUSTFAC_AVL.Period_ID = TEMPFAC.Period_ID
And CUSTFAC_AVL.Credit_Unit_ID = TEMPFAC.Credit_Unit_ID
AND CUSTFAC_AVL.Facility_ID = TEMPFAC.Facility_ID
AND CUSTFAC_AVL.Bid_Flag = TEMPFAC.Bid_Flag
AND CUSTFAC_AVL.CParty_ID = TEMPFAC.Customer_ID -- Return 282717 (00:00:37)

INNER JOIN BKPT ON
BKPT.Period_ID = CUSTFAC_AVL.Period_ID
AND BKPT.Credit_Unit_ID = CUSTFAC_AVL.Credit_Unit_ID
AND BKPT.Facility_ID = CUSTFAC_AVL.Facility_ID
AND BKPT.Bid_Flag = CUSTFAC_AVL.Bid_Flag
AND BKPT.Avail_ID = CUSTFAC_AVL.Avail_ID -- Return 282717 (00:00:37)

INNER JOIN PRODUCT PROD ON
PROD.Period_ID = TEMPFAC.Period_ID
AND PROD.Credit_Unit_ID = TEMPFAC.Credit_Unit_ID
AND PROD.Facility_ID = TEMPFAC.Facility_ID
AND PROD.Bid_Flag = TEMPFAC.Bid_Flag -- Return 282717 (00:01:33)

INNER JOIN CURRENCY CURR ON
CURR.Period_ID = PROD.Period_ID
AND CURR.Credit_Unit_ID = PROD.Credit_Unit_ID
AND CURR.Facility_ID = PROD.Facility_ID
AND CURR.Bid_Flag = PROD.Bid_Flag
AND CURR.Avail_ID = PROD.Avail_ID -- Return 282717 (00:01:41)

INNER JOIN CURRENCY_PRICING CURRPRI ON
CURRPRI.Period_ID = CURR.Period_ID
AND CURRPRI.Credit_Unit_ID = CURR.Credit_Unit_ID
AND CURRPRI.Facility_ID = CURR.Facility_ID
AND CURRPRI.Bid_Flag = CURR.Bid_Flag
AND CURRPRI.Avail_ID = CURR.Avail_ID
AND CURRPRI.Currency_Code = CURR.Currency_Code -- Return 282717 (00:06:40)

INNER JOIN FACILITY_FEES FACFEES ON
FACFEES.Period_ID = TEMPFAC.Period_ID
AND FACFEES.Credit_Unit_ID = TEMPFAC.Credit_Unit_ID
AND FACFEES.Facility_ID = TEMPFAC.Facility_ID
AND FACFEES.Bid_Flag = TEMPFAC.Bid_Flag -- Return 42822 (00:01:10)

INNER JOIN FACILITY_FEE_TYPE FACFEE_TYPE ON
FACFEE_TYPE.Period_ID = FACFEES.Period_ID
AND FACFEE_TYPE.Fee_Type_ID = FACFEES.Fee_Type_ID -- Return 42822 (00:01:27)

INNER JOIN CUSTOMER_FACILITY CUSTFAC ON
CUSTFAC.Period_ID = CUST.Period_ID
AND CUSTFAC.Credit_Unit_ID = CUST.Credit_Unit_ID
AND CUSTFAC.CParty_ID = CUST.CParty_ID -- Return 42822 (00:03:33)

INNER JOIN COUNTRY C ON
C.Period_ID = CUST.Period_ID
AND C.Country_Code = CUST.Corp_Residence_Country_Code -- Return 42788 (00:09:16)

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-07 : 04:16:59

I think you mainly want to make sure your indexes are looking good. Put the various columns in, and include the ones that are used in the select clause.

Also, remove 'distinct' if possible.

And put the table aliases in front of the column names in your select query.

Having done all this... post the execution plan than comes out. This will give you clues as to where other problems might be.

Rob Farley
http://robfarley.blogspot.com

kjmraohyd
Starting Member

34 Posts

Posted - 2006-08-07 : 04:46:44

The Query Execution Plan looks good with all the indexes in place. The only hieghst cost is with the DISTINCT clause.

I remember we can force use any index WITH clause. What is the syntax with some example? This may help me to further tune this statement.

TIA

Jagan

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-07 : 05:04:54

I don't think forcing particular indexes is the right way to go here.

Can you remove the distinct clause? If you need it because you have duplicates somewhere, then spend your time changing your query so that you don't need the distinct. The reward will pay off...

Rob Farley
http://robfarley.blogspot.com

kjmraohyd
Starting Member

34 Posts

Posted - 2006-08-07 : 05:33:38

The result set with distinct clause is just 42,000 but without distinct clause it's close to 3 millions and also the time difference is just 5 minutes.

That's the reason I want to try FORCE INDEX WITH CLAUSE and see if I can gain any performance. Do you have the syntax for the same.

I remeber I have used the same long before but forgot the syntax somthing like this:

Select * from Table1 where Col1 = 100 with (index,indexname)

TIA

Jagan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 05:50:41

You can refer to the syntax from BOL on "FROM"

Select * from Table1 with (index(indexname)) where Col1 = 100

But I don't think this will give you the performance you required.

" The result set with distinct clause is just 42,000 but without distinct clause it's close to 3 millions"
You are getting duplicate result row because some of your tables are one to many relationship. Identify the relationship between the tables and use derived table on these tables. It should give you better performance.

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-08-07 : 20:37:57

I'd like to suggest working out where the extra rows are coming from. DISTINCT is certainly going to be a killer in this situation.

But without more information, we can't tell how you need to alter your query. We can't even tell which tables your select clause columns are coming from atm.

Rob Farley
http://robfarley.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources