Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022. The Coursera Plus membership gets you access to unlimited courses and unlimited certifications! imp.i384100.net/Ke51on
As a 10 year+ SQL DBA and Dev... a difference of a quarter second (0.250) versus a 7 thousandth of a second (0.007) process time in an execution plan means that count(*) for raw row count is indeed faster when you start scaling. Even running against a clustered index, count(1) is going to be marginally slower. count(*) is your winner. Imagine multiple millions of rows with a count(1) with just a PK index. I shudder at the thought.
The time difference is just due to some fluctuation. The query optimizer is smart enough to figure out COUNT(*) and COUNT(1) are equivalent and execute them in exactly the same way. Besides, Taking 0.25s to scan a 60000 rows table is ridiculously slow. (I tried it on my machine as well)
that's ridiculous, the plans are the same, meaning the same computations are done, meaning the time difference is because of you running it on your laptop where you run multiple different programs e.g. you are recording this video, so in essence - count(1) or count(*) perform exactly the same !
It all depends on the table size and it’s structure and number of CPUs. On my PC , table with one column and 500k rows count(*) and count(1) take approximately the same time, count(column) takes longer. Table with 3 columns and 500k rows - count(*) is slightly faster than count(1) , count(column) takes double time. The timing may be different for different version of database. I am sure we would see significant difference in the old versions such as SQL 2008. Count(1), count(*) and count(column) fetched rows differently and this caused the execution variation.
I have seen/heard select count(*) from tableA returns count of all rows in table regardless of whether a row has all null or some null values, on the other hard select count(columnA) from tableA returns the number of records that do not have null in columnA. But what if we use a group by? I have seen no info on this. are they equivalent?
I've been writing SQL for a long time, and I like to think I'm very good at it. Never in my life have I even considered typing "count(1)", and I clicked on this in a big WTF moment. Not gonna lie. My time was wasted.
I deal with tables that big on a regular basis. If I need to know the unfiltered count (roughly), I'll check the table stats, not run a count query. :)
@@TimGautier that's a good one.. the stats will be ultra fast too. The one issue is if your stats are somewhat stale they could be considerably off, in which case count_big works well on those very large tables. Side comment, If you're doing something with columnstore indexes then your counts and aggregates will be much much faster too, albeit CPU intensive sometimes.
wow... the execution performance depends of many many things... on a local database so small as that one table youre working on you will never have a good sample... on remote databases with millions of rows those queries will differ significantly
This video is misleading. If you add a value between the parentheses it will assign that values to every row and return the count. It will ALWAYS be slower than count *. They will return the same result but are NOT THE SAME. Your timing demonstrates that
@@newsun9382 No i dont have channels. And btw dont bring this to off topic, with me being Indian. We are commenting on the technical topic. Keep Nation, And other topics off the thread.
@@shirishnamdeo1775 No I will not keep nation, nationality out of any thing. Indians, thought they form bulk of the IT world, are net consumers of such videos and articles. They are very good at criticising though. If you take out the nationality part, all I said was to be more polite and encouraging. We dont have to be @$$ #0l#$ all the time. Take a break sometimes.
Is no here going to call this fraud video? She says no difference between * and 1 yet the * had .007 and 1 had .275 ? That is a huge significance. I would take this course for free 0 trust…
Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.net/Ke51on
"There is no difference" - effectively demonstrates live that count(1) took 40x longer to execute than count(*)
Doing a count on a table with only 10 rows is so trivial that you'll see wild variations in speed just due to other processes running on the system.
The tables I work isually have millions of rows, its good to know these things
Great and clear explanation on Count(1), * plus ColumnName. Thanks!
Glad it was helpful!
Nice explanation... Thank you for your time and effort.
Thank you
As a 10 year+ SQL DBA and Dev... a difference of a quarter second (0.250) versus a 7 thousandth of a second (0.007) process time in an execution plan means that count(*) for raw row count is indeed faster when you start scaling. Even running against a clustered index, count(1) is going to be marginally slower. count(*) is your winner. Imagine multiple millions of rows with a count(1) with just a PK index. I shudder at the thought.
I have learnt count(1) is quicker than count(*), you mean that is wrong?
make your own test, you'll be surprised.
The time difference is just due to some fluctuation.
The query optimizer is smart enough to figure out COUNT(*) and COUNT(1) are equivalent and execute them in exactly the same way. Besides, Taking 0.25s to scan a 60000 rows table is ridiculously slow. (I tried it on my machine as well)
that's ridiculous, the plans are the same, meaning the same computations are done, meaning the time difference is because of you running it on your laptop where you run multiple different programs e.g. you are recording this video, so in essence - count(1) or count(*) perform exactly the same !
Seems like due to cache was cold for count(1)
Thank you for the explanation. I'm sharing the video with a friend. He wanted to know the syntax difference of COUNT. 😁☺️
0:47 - Every Body knows that it gets same result
2:01 - On bigger Tables
4:20 - * Took less time
It all depends on the table size and it’s structure and number of CPUs.
On my PC , table with one column and 500k rows count(*) and count(1) take approximately the same time, count(column) takes longer.
Table with 3 columns and 500k rows - count(*) is slightly faster than count(1) , count(column) takes double time.
The timing may be different for different version of database. I am sure we would see significant difference in the old versions such as SQL 2008. Count(1), count(*) and count(column) fetched rows differently and this caused the execution variation.
Same count questions has been asked in 24th April 2021 in tiger analytics
Thanks for sharing, but I don't see you show Count(colname) cost how many sec.
Did I miss something?
Hi, Can you make video on long running stored procedures. Daily its running fyn but one day its taking long time so how we can debug it
It's like soo coool explanations, you are making everything so easy by perfect usage of all clauses, functions etc., of SQ language
Thank you
Good Stuff, All are very useful. Plz provide more videos...
Thank you.
I have seen/heard select count(*) from tableA returns count of all rows in table regardless of whether a row has all null or some null values, on the other hard select count(columnA) from tableA returns the number of records that do not have null in columnA. But what if we use a group by? I have seen no info on this. are they equivalent?
I have the same doubt
@@RakeshKumar-dq3db yes me too
Group by will elimates the duplicates it will fetch only unique records from coloumns
@miguel Petrarca , if you use a group by with a count (columnname) , the resulting aggregation Will only account for non-null records
So great explaination. Thank a lot
Glad it was helpful!
You're an amazing teacher.
Thank you 🙏
Thank you very much for the wonderful explanation.
Thank you 🙏
Well explained.Appreciate
Glad it was helpful 🙏
Great content !!!!
Thank you
Yestarday I had some clases and a question similar to this one was asked to the professor, I understood your answer way better than his, thank you!
Glad it was helpful 👍 Thanks
count(*) was 40 times faster than count(1) in the example.
this is a big difference when we work in production.
just like hbase and hive
Thank you for the class ❤️
Glad it was helpful 🙏
if we have NULL in all columns and do COUNT(*), will that row be counted?
Yes, Count(*) will count null values.
Very good explanation, thank you!!
Thank you
If we say select distinct(column )will the null value row will also be shown? Since it is distinct.
yes it would show you NULL also
I've been writing SQL for a long time, and I like to think I'm very good at it. Never in my life have I even considered typing "count(1)", and I clicked on this in a big WTF moment. Not gonna lie. My time was wasted.
Thanks for sharing valueble videos mam :),Liked,shared,subscribed :)
Thank you
Its helpful for me
Glad it was helpful.
It's funny that in the Oracle database is count(1) and count(*) exactly the same... They have the same execution plan. 🙂
Thank you.
When do we use count(1) in our queries and what's it's purpose
Waiting for another interesting video
Thank you. More videos coming soon!
The way ur explanation is good. I need a query which explain sales aging by 6 months qty totals from table. Could u please help.
Thank you
These are a few videos on sales. Please see if these help.
th-cam.com/video/rGapnGwEWIU/w-d-xo.html
th-cam.com/video/CDGwVXknZXI/w-d-xo.html
my interview question for count would be:. How would I perform a count on a table with 30 billion rows? (Hint, bigint comes into play).
That is a great question. Please post your approach as well for this scenario.
I deal with tables that big on a regular basis. If I need to know the unfiltered count (roughly), I'll check the table stats, not run a count query. :)
@@TimGautier that's a good one.. the stats will be ultra fast too. The one issue is if your stats are somewhat stale they could be considerably off, in which case count_big works well on those very large tables.
Side comment, If you're doing something with columnstore indexes then your counts and aggregates will be much much faster too, albeit CPU intensive sometimes.
wow... the execution performance depends of many many things... on a local database so small as that one table youre working on you will never have a good sample... on remote databases with millions of rows those queries will differ significantly
Would there be any difference in prequel?
In prequel, the time is the same but the count is negative.
If count (1) and count (*) are same in every aspect.. what is the need of count(1)?
You are right. Both serve the same purpose. It is just a way of writing.
Person who have lot of time can use the count(1) 😅
This video is misleading. If you add a value between the parentheses it will assign that values to every row and return the count. It will ALWAYS be slower than count *. They will return the same result but are NOT THE SAME. Your timing demonstrates that
very good explanation, everyone can easily understand.
Thanks so much!
Wonderful ❤️
Thank you
Thanks
Thank you
Great
Thank you
Wrong, You executed then count(*) two times, intially it took 5 seconds.. check your video yourself.
You could have put it in a softer way. BTW, what's you channel? Do you have one? Indians should learn gratitude.
@@newsun9382 No i dont have channels.
And btw dont bring this to off topic, with me being Indian.
We are commenting on the technical topic.
Keep Nation, And other topics off the thread.
@@shirishnamdeo1775 No I will not keep nation, nationality out of any thing. Indians, thought they form bulk of the IT world, are net consumers of such videos and articles. They are very good at criticising though.
If you take out the nationality part, all I said was to be more polite and encouraging. We dont have to be @$$ #0l#$ all the time. Take a break sometimes.
Is no here going to call this fraud video? She says no difference between * and 1 yet the * had .007 and 1 had .275 ? That is a huge significance. I would take this course for free 0 trust…
SQL-in sadə yolla izahı
Now compare SUM(1) vs COUNT(4) 😂
please charge your laptop its giving my anxiety
Count(1) can be faster than count(*) if column 1 is a primary key column. Cmiiw
Count(*) is always faster than count(1)
calculating efficiency on 10rows database? aha, what pronouns do you use?