Group first, then join?
- I want to obtain grouped results from one table, and only then join it to another table in the same statement. For example:select TypeID, max(TxDate) from transactions group by TypeIDOnly then do I want to join the output of the query to a lookup table on the TypeID field to get the description of TypeID. I could join before grouping, but then I'd have to group on the description for TypeID as well, and my logic tells me that will incur an unnecessary performance hit...Is this possible? Or am I just sniffing glue?
Thanks in advance! -
Offcourse you'll have to check execution plans, but the optimizor will see trough it (I hope)
This would result in a query like this :
select P.TypeId
, max(P.TypeDescription) as TypeDescription
, max(T.TxDate) as maxTxDate
from TypeParemeter P
inner join transactions T
on P.TypeID = T.TypeID
group by P.TypeID
So you'd prefer
select P.TypeId, P.TypeDescription , T.maxTxDate
from TypeParemeter P
inner join
( select TypeID, max(TxDate) as maxTxDate
from transactions
group by TypeID ) T
on P.TypeId = T.TypeId
Check the execution plans and times !
And evaluate readability ! (you may not be the only one to support this query, so document it well if it's perpose and devision is not readable by nature)
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 and code to get the best help- 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
- Thanks, I thought you needed to join on a table, didn't know you could join on a query!Just to clarify, I did a test and the second query did perform better than the 1st one. Then i modified the 1st one to rather group on (as per you example) P.TypeDescription instead of applying the aggregate Max() to it. The 2 examples then rendered the exact same execution plan, and by implication took exactly the same time to execute!Guess the query optimizer folks are smarter than I thought...Thanks for the help!
-
I'ts always a nice adventure to predict an execution plan yourself and then compare it to the one sqlserver uses
You'll learn a lot from it.
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 and code to get the best help- 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
-
i dont think "nice adventure" is quite the term i'd use ...!!
-
I think I agree with Vinny
-
Hi,
I use temp tables. I have a report that does statistics on data and many kinds of grouping have to be implemented. Some things have to be groupped before additional processing. I am not sure about the performance. These queries have to be run once in a quarter, so I did not investigate this side of using temp tables.
Regards,Yelena Varsha
-
Well, lets just say it is a "must do" to get a feeling with your rdbms.
If you ignore execution plans, or the investigation regaring how and why your rdbms uses a certain plan, you'll miss a lot of knowlage to avoid bottlenecks in your system.
Yelena,
It didn't pop into my mind to mention temp objects to solve this query.
Also there, it's mandatory to test end check.
.... and only use it when needed .
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 and code to get the best help- 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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply