Siebel Performance Tuning Guide > Tuning Siebel EIM > Troubleshooting Siebel EIM Performance >

Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Example

This topic is part of Troubleshooting Siebel EIM Performance.

The example in Table 7 illustrates the results achieved for an SQL statement with index hints and without index hints. This example was performed on the Microsoft SQL Server platform.

UPDATE dbo.S_ASSET5_FN_IF

SET T_APPLDCVRG__RID =

(SELECT MIN(BT.ROW_ID)

FROM dbo.S_APPLD_CVRG BT (INDEX = S_APPLD_CVRG_U2)

WHERE (BT.COVERAGE_CD = IT.CVRG_COVERAGE_CD AND

BT.TYPE = IT.CVRG_TYPE AND

BT.ASSET_ID = IT.T_APPLDCVRG_ASSETI AND

(BT.ASSET_CON_ID = IT.T_APPLDCVRG_ASSETC OR

(BT.ASSET_CON_ID IS NULL AND IT.T_APPLDCVRG_ASSETC IS NULL)) AND

(BT.INSITEM_ID = IT.T_APPLDCVRG_INSITE OR

(BT.INSITEM_ID IS NULL AND IT.T_APPLDCVRG_INSITE IS NULL))))

FROM dbo.S_ASSET5_FN_IF IT

WHERE (CVRG_COVERAGE_CD IS NOT NULL AND

CVRG_TYPE IS NOT NULL AND

T_APPLDCVRG_ASSETI IS NOT NULL AND

IF_ROW_BATCH_NUM = 10710001 AND

IF_ROW_STAT_NUM = 0 AND

T_APPLDCVRG__STA = 0)

SET STATISTICS PROFILE ON

GO

SET STATISTICS IO ON

GO

select

(SELECT MIN(BT.ROW_ID)

FROM dbo.S_APPLD_CVRG BT (INDEX = S_APPLD_CVRG_U2)

WHERE (BT.COVERAGE_CD = IT.CVRG_COVERAGE_CD AND

BT.TYPE = IT.CVRG_TYPE AND

BT.ASSET_ID = IT.T_APPLDCVRG_ASSETI AND

(BT.ASSET_CON_ID = IT.T_APPLDCVRG_ASSETC OR

(BT.ASSET_CON_ID IS NULL AND IT.T_APPLDCVRG_ASSETC IS NULL)) AND

(BT.INSITEM_ID = IT.T_APPLDCVRG_INSITE OR

(BT.INSITEM_ID IS NULL AND IT.T_APPLDCVRG_INSITE IS NULL))))

FROM dbo.S_ASSET5_FN_IF IT

WHERE (CVRG_COVERAGE_CD IS NOT NULL AND

CVRG_TYPE IS NOT NULL AND

T_APPLDCVRG_ASSETI IS NOT NULL AND

IF_ROW_BATCH_NUM = 10710001 AND

IF_ROW_STAT_NUM = 0 AND

T_APPLDCVRG__STA = 0)

With Hints

Table 'S_APPLD_CVRG'. Scan count 1, logical reads 394774, physical reads 0, read-ahead reads 280810.

Table 'S_ASSET5_FN_IF'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 0.

Without Hints

Table 'S_APPLD_CVRG'. Scan count 1268, logical reads 10203, physical reads 697, read-ahead reads 0.

Table 'S_ASSET5_FN_IF'. Scan count 1, logical reads 366, physical reads 0, read-ahead reads 0.

Related Topics

• Using the USE INDEX HINTS and USE ESSENTIAL INDEX HINTS Parameters
• Using USE INDEX HINTS and USE ESSENTIAL INDEX HINTS: Criteria for Passing Indexes to the Database