SQL query - which version do you prefer? [modified]
-
These two queries return the same result set(different results as different fields). A colleague wrote the first query and I wrote the second. Now I think the first one looks just plain ugly, however the execution plan is not too dissimilar to the second query, which I wrote. Is there any rationality to my response of Urghhhh? My colleague's version:
select sat.salesnumber, pjq.pickjobnumber from (select salesnumber, rownumber, invoiceaccount from salestable where dataset = 'wtl' and ltrim(salesnumber) = '331299') as sat join (select * from pickjobtrans) as pjt on sat.rownumber = pjt.picksalesrecid join (select * from pickjobqueue) as pjq on pjt.pickjobnumber = pjq.pickjobnumber group by sat.salesnumber, pjq.pickjobnumberMy version:
select distinct pjq.accountnumber,pjq.status from pickjobqueue pjq right join pickjobtrans pjt on pjq.pickjobnumber = pjt.pickjobnumber right join salestable sal on pjt.picksalesrecid = sal.rownumber where ltrim(sal.salesnumber) = '331299'You always pass failure on the way to success.
-
These two queries return the same result set(different results as different fields). A colleague wrote the first query and I wrote the second. Now I think the first one looks just plain ugly, however the execution plan is not too dissimilar to the second query, which I wrote. Is there any rationality to my response of Urghhhh? My colleague's version:
select sat.salesnumber, pjq.pickjobnumber from (select salesnumber, rownumber, invoiceaccount from salestable where dataset = 'wtl' and ltrim(salesnumber) = '331299') as sat join (select * from pickjobtrans) as pjt on sat.rownumber = pjt.picksalesrecid join (select * from pickjobqueue) as pjq on pjt.pickjobnumber = pjq.pickjobnumber group by sat.salesnumber, pjq.pickjobnumberMy version:
select distinct pjq.accountnumber,pjq.status from pickjobqueue pjq right join pickjobtrans pjt on pjq.pickjobnumber = pjt.pickjobnumber right join salestable sal on pjt.picksalesrecid = sal.rownumber where ltrim(sal.salesnumber) = '331299'You always pass failure on the way to success.
The one that runs faster :)
#region signature my articles #endregion
-
These two queries return the same result set(different results as different fields). A colleague wrote the first query and I wrote the second. Now I think the first one looks just plain ugly, however the execution plan is not too dissimilar to the second query, which I wrote. Is there any rationality to my response of Urghhhh? My colleague's version:
select sat.salesnumber, pjq.pickjobnumber from (select salesnumber, rownumber, invoiceaccount from salestable where dataset = 'wtl' and ltrim(salesnumber) = '331299') as sat join (select * from pickjobtrans) as pjt on sat.rownumber = pjt.picksalesrecid join (select * from pickjobqueue) as pjq on pjt.pickjobnumber = pjq.pickjobnumber group by sat.salesnumber, pjq.pickjobnumberMy version:
select distinct pjq.accountnumber,pjq.status from pickjobqueue pjq right join pickjobtrans pjt on pjq.pickjobnumber = pjt.pickjobnumber right join salestable sal on pjt.picksalesrecid = sal.rownumber where ltrim(sal.salesnumber) = '331299'You always pass failure on the way to success.
I think having a column called salesnumber which is actually a char datatype is a bit of a db design coding horror too :)
-
These two queries return the same result set(different results as different fields). A colleague wrote the first query and I wrote the second. Now I think the first one looks just plain ugly, however the execution plan is not too dissimilar to the second query, which I wrote. Is there any rationality to my response of Urghhhh? My colleague's version:
select sat.salesnumber, pjq.pickjobnumber from (select salesnumber, rownumber, invoiceaccount from salestable where dataset = 'wtl' and ltrim(salesnumber) = '331299') as sat join (select * from pickjobtrans) as pjt on sat.rownumber = pjt.picksalesrecid join (select * from pickjobqueue) as pjq on pjt.pickjobnumber = pjq.pickjobnumber group by sat.salesnumber, pjq.pickjobnumberMy version:
select distinct pjq.accountnumber,pjq.status from pickjobqueue pjq right join pickjobtrans pjt on pjq.pickjobnumber = pjt.pickjobnumber right join salestable sal on pjt.picksalesrecid = sal.rownumber where ltrim(sal.salesnumber) = '331299'You always pass failure on the way to success.
I am not a fan of SELECT *, It returns too much data, taking up resources along the way. That being said, my only other complaint is the lack of formatting, but that is just code style. I find the extra formatting makes it easier to find the part I want to work on. SELECT DISTINCT pjq.accountnumber, pjq.status FROM pickjobqueue pjq RIGHT JOIN pickjobtrans pjt ON (pjq.pickjobnumber = pjt.pickjobnumber) RIGHT JOIN salestable sal ON (pjt.picksalesrecid = sal.rownumber) WHERE LTRIM(sal.salesnumber) = '331299' Hogan
-
I am not a fan of SELECT *, It returns too much data, taking up resources along the way. That being said, my only other complaint is the lack of formatting, but that is just code style. I find the extra formatting makes it easier to find the part I want to work on. SELECT DISTINCT pjq.accountnumber, pjq.status FROM pickjobqueue pjq RIGHT JOIN pickjobtrans pjt ON (pjq.pickjobnumber = pjt.pickjobnumber) RIGHT JOIN salestable sal ON (pjt.picksalesrecid = sal.rownumber) WHERE LTRIM(sal.salesnumber) = '331299' Hogan
I take your point on formatting.
snorkie wrote:
I am not a fan of SELECT *, It returns too much data, taking up resources along the way
I am in the same camp as you here, however when I looked at the execution plan both queries have very similar execution plans. In the end neither was faster. My position is more that it just looks lazy using those select* statements. I also find a beauty in being able to write code that is short and concise. Maybe I am being pedantic and need to let go... -- modified at 15:28 Tuesday 6th November, 2007
You always pass failure on the way to success.
-
I think having a column called salesnumber which is actually a char datatype is a bit of a db design coding horror too :)
Yes - it's a Mircosoft XAL database. It would make more sense space-wise as well as a varchar, in this case, will take more space than an int type which only takes 4 bytes. Microsoft bought the database from Navision who bought it from Daamgard before it was a SQL database. Let that be a warning to database design - get it wrong at the beginning and you can be left with problems for the life of the product.
You always pass failure on the way to success.
-
I take your point on formatting.
snorkie wrote:
I am not a fan of SELECT *, It returns too much data, taking up resources along the way
I am in the same camp as you here, however when I looked at the execution plan both queries have very similar execution plans. In the end neither was faster. My position is more that it just looks lazy using those select* statements. I also find a beauty in being able to write code that is short and concise. Maybe I am being pedantic and need to let go... -- modified at 15:28 Tuesday 6th November, 2007
You always pass failure on the way to success.
GuyThiebaut wrote:
I am in the same camp as you here, however when I looked at the execution plan both queries have very similar execution plans. In the end neither was faster.
To a large extent, the execution plan is immaterial here. What matters more is the amount of data that is returned over the network. If you only need one column, why put more load on what may already be a busy network by returning 20?
Deja View - the feeling that you've seen this post before.
-
GuyThiebaut wrote:
I am in the same camp as you here, however when I looked at the execution plan both queries have very similar execution plans. In the end neither was faster.
To a large extent, the execution plan is immaterial here. What matters more is the amount of data that is returned over the network. If you only need one column, why put more load on what may already be a busy network by returning 20?
Deja View - the feeling that you've seen this post before.
Select *....just say no. It is a lazy approach and as Pete points out you can end up creating a lot of network traffic for no benefit.
-
I am not a fan of SELECT *, It returns too much data, taking up resources along the way. That being said, my only other complaint is the lack of formatting, but that is just code style. I find the extra formatting makes it easier to find the part I want to work on. SELECT DISTINCT pjq.accountnumber, pjq.status FROM pickjobqueue pjq RIGHT JOIN pickjobtrans pjt ON (pjq.pickjobnumber = pjt.pickjobnumber) RIGHT JOIN salestable sal ON (pjt.picksalesrecid = sal.rownumber) WHERE LTRIM(sal.salesnumber) = '331299' Hogan
snorkie wrote:
I am not a fan of SELECT *,
I support you. Even in the case of selecting all the columns of the table (only remote combinations of applications), I prefer having them enumerated instead of *. I feel it is more readable and friendly too not to mention about the significant gains in terms of performance. For that matter, I think, even C# takes that stand. Like Java, where java.lang.* no longer works for namespace inclusions in managed code. Isn't it?
Vasudevan Deepak Kumar Personal Homepage Tech Gossips
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT. -
Select *....just say no. It is a lazy approach and as Pete points out you can end up creating a lot of network traffic for no benefit.
not to mention the possibility of some adding a big field to the end of the table (blob for instance) and your code suddenly grinding to a halt. I won't use select * even if i want every field as you have no idea what changes may come later Russ