Here are the videos discussed in the video: Optimize DATE in WHERE Clause th-cam.com/video/GGwfJ3Sj6a0/w-d-xo.html Performance: Between and Other Operators th-cam.com/video/AL1AQrta1-Q/w-d-xo.html
I think this is misleading though - between includes the lower and upper ranges. So it is the same as saying ModifiedDate > '2011-06-15' and ModifiedDate
Hello Pinal sir, thanks for your valuable videos. I have an one doubts, for the primary key perspective which one is best either INT or UNIQUEIDENTIFIER . Kindly suggested me. Thanks
Hello Sir, I have a question. How to find atleast, atmost things from a table. E.g. Write a query to find out the managers who is having atleast 10 employees? All the employees and managers belong to a sing table emp.
I have a little issue with my system. I need to calculate COGS (Cost of Goods Sales) of particular Item everytime the user posted a transaction. The problem comes when the data gets too many. I have to: 1. Find the last cost of the Item according to Date 2. Update the COGS of the Item in that transaction 3. Reupdate the COGS of the Item in next transaction if there are any. These query is so expensive. I dont know how to improve it
Would casting the column as date slow performance? Or is engine smart enough to know date is a shortened datetime? If so then you could safely use between... Ie something like cast(OrderDatetime as date) between date1 and date2
Hi sir my Database getting slow everyday and when I check the session log I find sp_OAMethod process count is more which impact the database performance how can I resolve this issue please suggest !
Hello pinal sir..thanks for the valuable video's.. currently I am preparing for SQL interview and got stuck at below question.. 1: explain execution plan 2: how to optimise SQL query\ store procedure Can you tell me how I can answer these questions
Here are the videos discussed in the video:
Optimize DATE in WHERE Clause th-cam.com/video/GGwfJ3Sj6a0/w-d-xo.html
Performance: Between and Other Operators th-cam.com/video/AL1AQrta1-Q/w-d-xo.html
Excellent! Many thanks for sharing your experience!
Glad you enjoyed it!
My thought was that BETWEEN is logically equivalent to (and parsed as) >= and
True
Man, yet another gem of a video. Thank you.
More to come!
I think this is misleading though - between includes the lower and upper ranges. So it is the same as saying ModifiedDate > '2011-06-15' and ModifiedDate
Yes between agrees both the edges but the issue in this video is with the DATEADD which creates more problems.
Hello Pinal sir, thanks for your valuable videos. I have an one doubts, for the primary key perspective which one is best either INT or UNIQUEIDENTIFIER . Kindly suggested me. Thanks
Int in most cases.
@@PinalDaveSQLAuthority Thanks for your reply. But security wise INT type is advisable ?
@@logu50 I do not see any issue with security wise... what issue do you see in terms of security.
Hello Sir,
I have a question. How to find atleast, atmost things from a table.
E.g. Write a query to find out the managers who is having atleast 10 employees? All the employees and managers belong to a sing table emp.
Without schema hard to answer this one. Sorry.
A coworker had this same issue. This person had the query giving data inconsistent with the production data.
Glad to hear that.
I have a little issue with my system.
I need to calculate COGS (Cost of Goods Sales) of particular Item everytime the user posted a transaction.
The problem comes when the data gets too many.
I have to:
1. Find the last cost of the Item according to Date
2. Update the COGS of the Item in that transaction
3. Reupdate the COGS of the Item in next transaction if there are any.
These query is so expensive.
I dont know how to improve it
This requires detailed discovery of your system.
@@PinalDaveSQLAuthority query, system spec, database spec?
Do you have any video to compare the performance of GROUP BY vs DISTINCT ?
They are same
Would casting the column as date slow performance? Or is engine smart enough to know date is a shortened datetime? If so then you could safely use between... Ie something like cast(OrderDatetime as date) between date1 and date2
Casting does not slow down anything in this scenario. You may try out with similar example.
Thank you for the excellent tip.
You're welcome!
Hi sir my Database getting slow everyday and when I check the session log I find sp_OAMethod process count is more which impact the database performance how can I resolve this issue please suggest !
Not sure of the solution to this one.
Thanks sir for reply ☺️
Hello pinal sir..thanks for the valuable video's.. currently I am preparing for SQL interview and got stuck at below question..
1: explain execution plan
2: how to optimise SQL query\ store procedure
Can you tell me how I can answer these questions
All the best for interview. Many of this questions are answered in my blog blog.sqlauthority.com just search there.
this is great.. but why would between fails to give correct results?
it's not - between INCLUDES the upper range, and is therefore the equivalent of ModifiedDate
Between includes both the ranges.
well explained sir.
Thanks for liking
But performance of between is better than operators for large data set
Hi Utkarsh, performance of between is not better, you may check earlier videos in this series, where I explain that.