Can this query be optimized
-
Hi.......... the query that i have made calls the same table with condition 'processing_date <= @BalanceDate' in the inner query. This is taking a good amout of time. Is there someway that this query maybe optimized by removing the 'processing_date <= @BalanceDate' from the inner query.
SELECT
DISTINCT A.teller_id, teller_name
from
branch_teller_st A, teller_name B
WHERE
A.blt = @blt AND A.team_type = @TeamType AND A.team_num = @TeamNum
AND
A.teller_id = B.teller_id AND A.processing_date <= @BalanceDate
AND
A.teller_id not in (SELECT teller_id from branch_teller_st WHERE inactive_teller = 'I' AND A.processing_date <= @BalanceDate)
GROUP
BY A.teller_id, B.teller_name
I know this is a sort of vague question, but any help is welcome. Thanks in advance. -- Sameer
-
The first thing I see is that NOT IN is going to kill your performance. It's a pretty terrible statement.
You're better of doing a left anti-semi join (I think that's the technical term for it) to exclude data. It works like this:
SELECT A.*
FROM tblA A
LEFT JOIN tblB B
ON A.id = B.id
WHERE B.id IS NULL
That would get you all the rows from tblA where id was not in tblB.
-
Thanks Aaron.
I am using the same table out in the inner query. So will the left join help.
Thanks for the reply.
Sameer
-
Sameer,
Here's an example of a derived table used in the OUTER JOIN. This should work for you, but I have not been able to test it as I do not have your schema/data. This should help a bit, but you'll also want to check on the indexes on the columns involved in your query here. Make sure the JOINed columns have an index on them (Teller_ID) as well as the table A columns in the WHERE clause. Check the exectution plan and see what indexes SQL Server wants to use and create a new index on Team_Type, Team_Num, ProcessingDate if necessary.
SELECT DISTINCT A.Teller_ID,
Teller_Name
FROM Branch_Teller_st A
INNER JOIN Teller_Name B
ON A.Teller_ID = B.Teller_ID
LEFT JOIN (SELECT Teller_ID FROM Branch_Teller_st WHERE Inactive_Teller = 'I' AND Processing_Date <= @BalanceDate) C
ON A.Teller_ID = C.Teller_ID
WHERE A.blt = @blt
AND A.Team_Type = @TeamType
AND A.Team_Num = @TeamNum
AND A.Processing_Date <= @BalanceDate
AND C.Teller_ID IS NULL
GROUP BY A.Teller_ID, B.Teller_Name
-
SELECT
DISTINCT a.Teller_ID,
b.Teller_Name
FROM Branch_Teller_st AS a
INNER JOIN Teller_Name AS b ON b.Teller_ID = a.Teller_ID
WHERE a.blt = @blt
AND a.Team_Type = @TeamType
AND a.Team_Num = @TeamNum
AND a.Processing_Date < DATEADD(DAY, 1, @BalanceDate)
AND a.Inactive_Teller <> 'I'
N 56°04'39.16"
E 12°55'05.25" -
maybe this solution is also worth comparing to the other plans and execution times
SELECT
-- distinct is not needed because of same group by !
a.Teller_ID,
b.Teller_Name
FROM Branch_Teller_st AS a
INNER JOIN Teller_Name AS b
ON b.Teller_ID = a.Teller_ID
WHERE
a.blt = @blt
AND a.Team_Type = @TeamType
AND a.Team_Num = @TeamNum
AND a.processing_date <= @BalanceDate
and not exists (
SELECT *
from branch_teller_st x
WHERE x.inactive_teller = 'I'
and x.Teller_ID = a.Teller_ID
-- is this last line needed in this correlated part ?
-- maybe should be a predicate on the branch_teller_st object
AND a.processing_date <= @BalanceDate )
-- may use indexes if availabye to support grouping
GROUP BY a.teller_id, b.teller_name
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sameer, scratch my example and go with Peter's. In my haste to get an example out, I overlooked how simple this one was to eliminate the unwanted rows in the WHERE clause. Leave it to Mr. Simple....thanks Peter.
-
Thanks all for the help.
But John, Peter's solution is not giving me the desired result. I too thought in the same way as Peter, but the result is different. For example i want only 16 rows and Peter's output is giving me 150 rows. Do anyone know why this is?
And John your solution i am working out. I have got around 50 times performance gain with the query and after adding indexes. But i am still confused why Peter's solution doesnt work. Any wild ideas?
Thanks all for the help :- Sameer
-
there is also the matter of :
... AND A.teller_id not in (SELECT teller_id from branch_teller_st WHERE inactive_teller = 'I' AND A.processing_date <= @BalanceDate) ...
this is symanticaly not the same as
... where inactive_teller <>'I' ... (depending on the uniqueness of teller_id)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Which values can inactive_teller have, besides "I"?
Can it be NULL?
N 56°04'39.16"
E 12°55'05.25" -
Peter: It has 'I' , 'M' and null.
Thanks :- Sameer
-
If you run your original query WITH table prefix in the IN part, like this
SELECT
DISTINCT A.teller_id, b.teller_name
from branch_teller_st A, teller_name B
WHERE A.blt = @blt AND A.team_type = @TeamType AND A.team_num = @TeamNum
AND A.teller_id = B.teller_id AND A.processing_date <= @BalanceDate
AND A.teller_id not in (SELECT q.teller_id from branch_teller_st as q WHERE q.inactive_teller = 'I' AND A.processing_date <= @BalanceDate)
GROUP BY A.teller_id, B.teller_name
do you get the same result?
I am just thinking that you have prefixed a where filter within the IN clause with an outer table reference.
N 56°04'39.16"
E 12°55'05.25" -
Hi Peter,
We get 5 records after running this query. We are actually migrating data from Informix to SQL. So we also are not that sure with what the ouput should be. 5 records is quite good one. I think we missed that.
Thanks for pointing it out. Still the performance issue stays. :- Sameer
-
Slightly Differerent than Peters
However, I have to ask, are you sure your's gives you what you really want?
SELECT DISTINCT a.Teller_ID,
b.Teller_Name
FROM Branch_Teller_st AS a
INNER JOIN Teller_Name AS b ON b.Teller_ID = a.Teller_ID
WHERE a.blt = @blt
AND a.Team_Type = @TeamType
AND a.Team_Num = @TeamNum
AND a.Processing_Date < DATEADD(DAY, 1, @BalanceDate)
AND NOT(a.Inactive_Teller = 'I' AND A.processing_date <= @BalanceDate)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply