Question on a Case Statement
-
I would like to know why adding a where clause similar to the case criteria improves performance significantly. I dont understand why this makes a difference. Since I'm using case to to determine what action to take place why would it make a difference if add a where clause similar to the case check?
Example Code - Before adding new where criteria (Our real query similar to this takes over 1 minute to run)
Select
EarnestMoneyBalace =
CASE
WHEN ContractID = 2
THEN
(SELECT SUM(amount)
FROM Table1 (nolock)
WHERE table1_id = a.legal_Id_code
AND Table1_SubAccount = '123')
Else
(SELECT SUM(amount)
FROM table1 (nolock)
WHERE table1_id = a.legal_Id_code
AND Table1_SubAccount IN ('456', '789')
END
From contracttable a
Example code after adding addition criteria (After adding an additional criteria to the where clause our real query runs in under 6 seconds).
Select
EarnestMoneyBalace =
CASE
WHEN ContractID = 2 THEN (
SELECT SUM(amount)
FROM Table1 (nolock)
WHERE table1_id = a.legal_Id_code
AND Table1_SubAccount = '123' And Contractid = 2)
 
Else (
SELECT SUM(amount)
FROM table1 (nolock)
WHERE table1_id = a.legal_Id_code
AND Table1_SubAccount IN ('456', '789') and contractid <> 2)
END,
From ContractTable a
-
There are several possibilities as to why the query run time improves.
1. The degree of selectivity of the field ContractId is very high. This makes the query inside THEN clause runs faster.
2. The degree of selectivity of the field ContractId is very high. This makes the query inside THEN clause to return very few rows.
-
I understand what you're saying. What i dont get is the "Case When ContractID = 2" acts the same as
Case when Contractid = 2
-
It's not about performance.
The logic of your queries is completely wrong.
You are receiving wrong results no matter how fast. Unless you have only 2 different ContractId's.
_____________
Code for TallyGenerator -
R u sure there no 'ContractID' in Table1 ???
If this is the case.... the reason is... obvious!
------------
When you 've got a hammer, everything starts to look like a nail... -
Currently, we have only 2 contractid's. The contractid determines which account we use to sum transactions on. The contractid exist in only 1 table. it's used to categorized the records in that table.
-
Could you post the schemas for ContractTable and table1?
My inital guess is that the subquery that you are using within the CASE statement is not constrained on the outer queries ContactId without you explicitly joining this table via a correlated subquery.
A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query (it constrains the subquery). [definition provided by Gregory A. Larsen]
That may be where a previous post was heading when they asked if ContactId was also found within the table1 listing?
Please post schemas so that we may better assist.
-
Here's a mock up of the tables. Keep in mind the ContractTable will have approximately 1200 records and the transactiontable will have over a million records.
-- drop table #contracttable
-- drop table #transactiontable
create table #ContractTable
(
Legal_ID_Code int
, contract_id int
 
Create table #TransactionTable
(
transaction_id int
, legal_id_code int
, transaction_amount float
, transaction_account int
 
-- Populate #ContractTable
Insert Into #ContractTable
values (1, 1)
Insert Into #ContractTable
values (2, 1)
Insert Into #ContractTable
values (3, 1)
Insert Into #ContractTable
values (5, 2)
Insert Into #ContractTable
values (6, 2)
Insert Into #ContractTable
values (7, 2)
-- Populate #TransactionTable
Insert into #TransactionTable
Values (1, 1, 10, 1)
Insert into #TransactionTable
Values (2, 1, 10, 1)
Insert into #TransactionTable
values (3, 1, 10, 1)
Insert into #TransactionTable
Values (4, 2, 10, 1)
Insert into #TransactionTable
Values (5, 2, 10, 1)
Insert into #TransactionTable
Values (6, 2, 10, 1)
Insert into #TransactionTable
Values (7, 2, 10, 1)
Insert into #TransactionTable
Values (8, 5, 10, 2)
Insert into #TransactionTable
Values (9, 6, 10, 2)
Insert into #TransactionTable
Values (10, 6, 10, 2)
Insert into #TransactionTable
Values (11, 6, 10, 2)
-- This query is similar to the one that takes over 1.5 minutes to run
Select #contracttable.legal_id_code
, dollaramount =
case
When #contracttable.contract_id = 2
Then (Select sum(transaction_amount) from #transactiontable where #contracttable.legal_id_code = #transactiontable.legal_id_code and transaction_account = 2)
Else
(select sum(transaction_amount) from #transactiontable where #contracttable.legal_id_code = #transactiontable.legal_id_code and transaction_account = 1)
END
From #contracttable
-- This query is similar to the one that takes less than 10 seconds after adding an additional where clause to the case statements
Select #contracttable.legal_id_code
, dollaramount =
case
When #contracttable.contract_id = 2
Then (Select sum(transaction_amount) from #transactiontable where #contracttable.legal_id_code = #transactiontable.legal_id_code and transaction_account = 2 and contract_id = 2)
Else
(select sum(transaction_amount) from #transactiontable where #contracttable.legal_id_code = #transactiontable.legal_id_code and transaction_account = 1 and contract_id <> 2)
END
From #contracttable
-
Also it's possible that by adding the new clause the query optimizer is now using a different, better suited index for the subquery...
Best regards
karl -
That's what i was thinking. The only difference i could see in the execution plans is that the use of the additional where criteria eliminated the a HASH Match/Join step. I guess im just trying to understand why the additional criteria makes such a difference.
-
I agree with Karl & John....
I thing that the query optimizer has the answer. Using execution plan you can see how... and (hopefully) why.
------------
When you 've got a hammer, everything starts to look like a nail... -
Well, it looks like it was an index issue. I ran the query statement through the tuning wizard and found that i did not have an index on the columns. I added the index to a test box and reran the query. It completed in under 4 seconds and didnt use a Hash Match/Join table.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply