April 24, 2017 at 12:00 am
Comments posted to this topic are about the item Should I Type * or 1 With the EXISTS Logical Operator
April 24, 2017 at 7:31 am
You definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1".
I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".
April 24, 2017 at 7:40 am
sequelgarrett - Monday, April 24, 2017 7:31 AMYou definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1".
I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".
And this is assuming everyone has a US keyboard.
April 24, 2017 at 9:33 am
v.malyutin - Sunday, April 23, 2017 10:00 PMComments posted to this topic are about the item Should I Type * or 1 With the EXISTS Logical Operator
In the article, you write: "I think that #1 and #3 are both affected by a large number of columns. That's why I am surprised a lot."
This is not surprising to me. You tested them after dropping the clustered index, on the heap.The plan you got for that case includes a table scan. And scanning a table with 1,000 integer columns takes more time, because the row size is bigger and hence less rows fit on the page.
April 24, 2017 at 9:36 am
Luis Cazares - Monday, April 24, 2017 7:40 AMsequelgarrett - Monday, April 24, 2017 7:31 AMYou definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1".
I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".
And this is assuming everyone has a US keyboard.
You can be sure, everyone has. And numbers are Arabic. Not sure about the *. But, layout is the same in each culture.
April 24, 2017 at 9:48 am
Hugo Kornelis - Monday, April 24, 2017 9:33 AMv.malyutin - Sunday, April 23, 2017 10:00 PMComments posted to this topic are about the item Should I Type * or 1 With the EXISTS Logical OperatorIn the article, you write: "I think that #1 and #3 are both affected by a large number of columns. That's why I am surprised a lot."
This is not surprising to me. You tested them after dropping the clustered index, on the heap.The plan you got for that case includes a table scan. And scanning a table with 1,000 integer columns takes more time, because the row size is bigger and hence less rows fit on the page.
I re-created all things before ostress running. Anyway, the key is that both of queries have been tested on the same data and metadata. Thanks.
April 24, 2017 at 9:49 am
v.malyutin - Monday, April 24, 2017 9:36 AMLuis Cazares - Monday, April 24, 2017 7:40 AMsequelgarrett - Monday, April 24, 2017 7:31 AMYou definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1".
I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".
And this is assuming everyone has a US keyboard.
You can be sure, everyone has. And numbers are Arabic. Not sure about the *. But, layout is the same in each culture.
No, layout is different in each country. My personal laptop is mexican and my work laptop is american. I usually have typos due to the different layouts. That happens as well in Mexico because we have 2 different layouts: mexican and latinamerican.
I've also used a french keyboard which needs Shift to enter numbers.
April 24, 2017 at 9:55 am
By the way, thank you for writing about this.
I actually had a conversation on the same subject with a co-worker today (and with another one some days ago). It's good to have proof of someone else testing it. However, one of my favorite tests is when I use WHERE EXISTS( SELECT 1/0 FROM Table....) or today I used SELECT COUNT(1/0) FROM Table.
Everyone is amazed with that construct.
April 24, 2017 at 10:43 am
Luis Cazares - Monday, April 24, 2017 9:55 AMBy the way, thank you for writing about this.
I actually had a conversation on the same subject with a co-worker today (and with another one some days ago). It's good to have proof of someone else testing it. However, one of my favorite tests is when I use WHERE EXISTS( SELECT 1/0 FROM Table....) or today I used SELECT COUNT(1/0) FROM Table.
Everyone is amazed with that construct.
Luis, you beat me to it. A common discussion here at work was precisely that count(1) or exists(select 1 ...) are faster / better than the asterisk versions. In the case of COUNT(WHATEVER) you can see that in the execution plan the engine actualy executes COUNT(*). In the case of EXISTS (SELECT WHATEVER ....) the engine doesn even consider the expressions in the select clause in the output list, only the columns in the predicate. So they are all the same.
But for me, the correct form should be the ones with asterisk. In this context the asterisk doesn't mean all the columns, instead it means all the rows.
v.malyutin
Thank you for taking the time to test that there are no diferences in execution time with either construct. Although I disagree with your comment about * being a problem in this context. If a person reviewing my code can't tell the difference, they shouldn't be reviewing any code.
April 24, 2017 at 10:49 am
You might get a kick out of tongue-in-cheek blog post I wrote about this subject several years ago:
http://bradsruminations.blogspot.com/search/label/SELECT%20*
April 24, 2017 at 11:07 am
Luis Cazares - Monday, April 24, 2017 9:55 AMIf a person reviewing my code can't tell the difference, they shouldn't be reviewing any code.
I totally agree!
April 24, 2017 at 11:41 am
What about select null, did you test that?
April 24, 2017 at 12:22 pm
vasim-622610 - Monday, April 24, 2017 11:41 AMWhat about select null, did you test that?
No, I did not. But I think whatever you typed the execution plan is going to be the same. Only the WHERE matters.
April 24, 2017 at 2:53 pm
I wonder if this would matter in MS Access. Up to now I've continued my use of SELECT TOP 1 * that I've used since using it in Access (which required using an Exists function I found in an Access 97 book).
In any case, I will be using SELECT * from now on. My own view is that it reads better than SELECT 1. If the second were demonstrably faster, I would use it, but if it's a tie, I'll go for aesthetics.
April 24, 2017 at 3:03 pm
RonKyle - Monday, April 24, 2017 2:53 PMI wonder if this would matter in MS Access. Up to now I've continued my use of SELECT TOP 1 * that I've used since using it in Access (which required using an Exists function I found in an Access 97 book).In any case, I will be using SELECT * from now on. My own view is that it reads better than SELECT 1. If the second were demonstrably faster, I would use it, but if it's a tie, I'll go for aesthetics.
Can you show me up an execution plan from Access? I remember for sure MS Access 97 could not provide you with execution plan, but I am not sure about newest version.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply