June 8, 2021 at 5:42 am
Hi,
How can I replace the below query with JOINS instead of subquery Or Use EXISTS instead of IN in the subquery and TRY both the scenarios and see execution time. Please suggest.
CREATE PROCEDURE dbo.spTest
(@Org_ID INT,
@Year INT,
@TestContributions MONEY=NULL OUTPUT --Does this mean NULL is the output?
)
AS BEGIN
SELECT @TestContributions=sum(T_Amount)
FROM dbo.TbTest
WHERE T_TType_ID in (4, 13, 27)AND T_TState_ID in (4)AND T_Year=@Year AND T_Account_ID in(SELECT Account_ID
FROM dbo.TbTestTwo
WHERE Account_Type_ID IN
(1, 3)AND Account_Closed IS NULL AND Account_ID IN
(SELECT IndividualAccount_Account_ID
FROM dbo.tbTestFour
WHERE IndividualAccount_Individual_ID in
(SELECT OrgIndividual_Individual_ID
FROM dbo.tbTestThree
WHERE OrgIndividual_Org_ID=@Org_ID)))
END
June 8, 2021 at 8:49 am
@TestContributions MONEY=NULL OUTPUT
you can find the value afterwards in @TestContributions
if you don't specify @TestContributions it will assume the default value NULL
Can you post the DDL of the tables so it is easily reproduced on other systems (instead of guessing). You might be missing some indexes
It looks it tries to sum contributions of accounts with specific types and states and years
and that the account belongs to a type that is not closed and is an invidual account belonging to the requested organisation
WHERE in (SELECT ...) -> WHERE EXISTS ( SELECT * WHERE X.field=outerquery.field)
Joins SELECT ... FROM dbo.tbTestThree Org inner join dbo.tbTestFour Indiv on Org.OrgIndividual_Individual_ID=Indiv.IndividualAccount_Account_ID
Exists is usually faster than joins because it only checks if data is present
June 8, 2021 at 10:53 am
I would write it using EXISTS instead of INs. It's easier if you use table aliases.
Then look at increasing the performance by adding indexes.
CREATE PROCEDURE dbo.spTest
(
@Org_ID INT,
@Year INT,
@TestContributions MONEY = NULL OUTPUT --Does this mean NULL is the output? (NULL is the default)
)
AS
BEGIN
SELECT @TestContributions = SUM(t.T_Amount)
FROM dbo.TbTest t
WHERE t.T_TType_ID IN(4, 13, 27)
AND t.T_TState_ID IN(4)
AND t.T_Year = @Year
AND EXISTS(SELECT *
FROM dbo.TbTestTwo t2
WHERE t2.Account_Type_ID IN(1, 3)
AND t2.Account_Closed IS NULL
AND t2.Account_ID = t.T_Account_ID
AND EXISTS(SELECT *
FROM dbo.tbTestFour t4
WHERE t4.IndividualAccount_Account_ID = t2.Account_ID
AND EXISTS(SELECT *
FROM dbo.tbTestThree t3
WHERE t3.OrgIndividual_Org_ID = @Org_ID
AND t3.OrgIndividual_Individual_ID = t4.IndividualAccount_Individual_ID
)
)
);
END;
June 8, 2021 at 11:50 pm
I would write it using EXISTS instead of INs. It's easier if you use table aliases.
I always use EXISTS instead of IN. Best case - it performs better, worse case - it performs the same.
-- Itzik Ben-Gan 2001
June 9, 2021 at 4:42 am
Thanks for the help, I recreated the SP with Exist, however, I am not sure why you used this " t2.Account_ID = t.T_Account_ID" in the above-updated query? can you please elaborate or mention some comments above the code... and also do we need to mention any type of JOIN? I looked at the execution plan for the updated query and it is giving a red symbol to one of the operators stating 'Join type is missing' Not sure if that is a problem.
June 9, 2021 at 4:44 am
Also, why do we need to do SELECT * FROM dbo.tbTestThree t3 -- Cant I select only the required column and still use EXIST?
June 9, 2021 at 4:58 am
I think I got why we are joining the table using =. Sorry for multiple posts.!
June 9, 2021 at 4:59 am
You don't need to return any columns inside the EXISTS clause. You could just write
WHERE EXISTS ( SELECT 1 FROM MyTable....)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply