January 16, 2008 at 4:32 pm
I have following query which works fine but I dont two queries pulling max seqnumber from tables and comparing again each other. I am looking for more simpler and optimized version of it. Can someone help me out here?
I need simpler version of following thing
(select max(seqnumber) from oms_log_attribute where omunique_id = u.omunique_id and attr_key = 'S' and attr_type = 'S')
=(select max(seqnumber) from oms_status_attribute where omunique_id = l.omunique_id and state = 'C')
Fully query is here:
select distinct l.omunique_id
into #id
from oms_log_attribute l, oms_status_attribute u
where l.omunique_id = u.omunique_id
and l.channel_id = 5
and (select max(seqnumber) from oms_log_attribute where omunique_id = u.omunique_id and attr_key = 'S' and attr_type = 'S')
=(select max(seqnumber) from oms_status_attribute where omunique_id = l.omunique_id and state = 'C')
and (db_entered_datetime >= '01-MAY-2007' and db_entered_datetime < '02-MAY-2007' )
January 17, 2008 at 2:51 am
try with inner join
January 17, 2008 at 9:27 am
I see 2 options for you.
1) Use inline views
SELECT DISTINCT l.omunique_id INTO #id
FROM oms_log_attribute l
JOIN oms_status_attribute u ON l.omunique_id = u.omunique_id
JOIN
( SELECT omunique_id, MAX(seqnumber) Max_SeqNumber
FROM oms_log_attribute
WHERE attr_key = 'S' and attr_type = 'S'
GROUP BY omunique_id) l_Max
ON l_Max.Max_SeqNumber = l.seqnumber
JOIN
( SELECT omunique_id, MAX(seqnumber) Max_SeqNumber
FROM oms_status_attribute
WHERE state = 'C'
GROUP BY omunique_id ) u_Max
ON u_Max.Max_SeqNumber = u.seqnumber
WHERE l.channel_id = 5
AND (db_entered_datetime >= '01-MAY-2007' and db_entered_datetime < '02-MAY-2007' )
Or the method I personally perfer, use IN clauses
SELECT DISTINCT l.omunique_id INTO #id
FROM oms_log_attribute l
JOIN oms_status_attribute u ON l.omunique_id = u.omunique_id
WHERE l.channel_id = 5
AND (db_entered_datetime >= '01-MAY-2007' and db_entered_datetime < '02-MAY-2007' )
AND l.seqnumber IN
( SELECT MAX(seqnumber) Max_SeqNumber
FROM oms_log_attribute
WHERE oms_log_attribute.omunique_id = l.omunique_id
AND attr_key = 'S' and attr_type = 'S' )
AND u.seqnumber IN
( SELECT MAX(seqnumber) Max_SeqNumber
FROM oms_status_attribute
WHERE oms_status_attribute.omunique_id = u.omunique_id
AND state = 'C' )
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 17, 2008 at 9:33 am
Thanks Kenneth. Dont you think that IN clause will slow down my query. I need to run this query against
large amount of data.
January 17, 2008 at 9:52 am
They shouldn't. The query optimizer is pretty good. But you could try running it on a subset of your data and see how it does. In fact if I were you I would try running several versions of the query against a subset and see which version is fastest. (and make sure you get the same results of course).
In this case since your original version is also running the subquerys anyway I'm going to guess that the IN clause is actually going to be faster.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
January 17, 2008 at 10:01 am
Hey,
I agree with you on your second query option. But how they are going to compare max values
with each other in following clause. I need to compare max from l.seqnumber with max of u.seqnumber.
Would help me on this?
AND l.seqnumber IN
( SELECT MAX(seqnumber) Max_SeqNumber
FROM oms_log_attribute
WHERE oms_log_attribute.omunique_id = l.omunique_id
AND attr_key = 'S' and attr_type = 'S' )
AND u.seqnumber IN
( SELECT MAX(seqnumber) Max_SeqNumber
FROM oms_status_attribute
WHERE oms_status_attribute.omunique_id = u.omunique_id
AND state = 'C' )
January 18, 2008 at 11:23 am
I've used both the 'IN' in the WHERE clause and and the joins to derived tables. In the cases where I benchmarked them, I found using joins to derived tables was faster.
Are there cases where the 'IN' in the WHERE clause is faster?
Todd Fifield
January 18, 2008 at 11:38 am
This should be right as I am using a having clause which can support the MAX funtion as well the grouping will remove the need for distinct. My additional thoughts are db_entered_datetime, which table is it in and is it character or datetime data? Note: I commented out INTO #id so you can test.
SELECT
l.omunique_id
-- INTO
-- #id
FROM
oms_log_attribute l
JOIN
oms_status_attribute u
ON
l.omunique_id = u.omunique_id
WHERE
l.channel_id = 5
and l.attr_key = 'S'
and l.attr_type = 'S'
and u.state = 'C'
and db_entered_datetime >= '01-MAY-2007'
and db_entered_datetime < '02-MAY-2007'
GROUP BY
l.omunique_id
HAVING
MAX(l.seqnumber) = MAX(u.seqnumber)
January 18, 2008 at 11:47 am
As long as you stay with MAX or MIN - I agree with Antares' solution. All of the other aggregate functions will get wrecked by a join before you aggregate (i.e will return incorrect results).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 12:38 pm
Antares686's solution looks good although I'm somewhat uncomfortable about pulling the where clauses out of the subqueries and into the main one. It certainly makes the solution alot easier though. I would run some comparisons of the results from both queries just to be sure you are getting the same thing.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply