When writing the WHERE clause of a SQL statement, it is important to be aware of the mixing of AND's with OR's. In the example below, the SQL was taking an extremely long time to execute.
WHERE (dbo.ER100_ACCT_ORDER.ER100_EVT_ID = 36232) AND (dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000003') AND (dbo.ER101_ACCT_ORDER_DTL.ER101_EXT_CHRG >= 0.00) OR
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000004') OR
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000005') OR
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000009') AND (dbo.ER101_ACCT_ORDER_DTL.ER101_PHASE = '1')
The SQL was taking a long time to execute because of the () nesting. When this was used, SQL interpreted it as the first 3 conditions combined by the AND's. Then next RES_CODE conditions individually and the last RES_CODE with PHASE as a separate condition.
The below SQL statement delivers the same results but will execute much more quickly:
WHERE
(dbo.ER100_ACCT_ORDER.ER100_EVT_ID = 36232) AND
(dbo.ER101_ACCT_ORDER_DTL.ER101_PHASE = '1') AND
(dbo.ER101_ACCT_ORDER_DTL.ER101_EXT_CHRG >= 0.00) AND
(
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000003') OR
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000004') OR
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000005') OR
(dbo.ER101_ACCT_ORDER_DTL.ER101_RES_CODE = '24830-000009')
)
Now it looks at the EVENT and PHASE and CHRG and IF the RES_CODE is one of the 4 choices.
Comments
0 comments
Please sign in to leave a comment.