Very interesting recommendation I am beginner about the data engineering stuff and database development. I'll consider the examples to optimize any query.
Interesting and helpful information. Thank you, Philipp I am not sure if you answered this, but speaking of built-in functions in the WHERE condition, what would be the best way to write a query to find all rows where the [Order Date] is in the future? The most natural way to do it would be "...WHERE [Order Date] > Date()". The engine is not processing anything on the table data using a function so that shouldn't be the performance hit you were discussing at around 19:00-20:00, but I'm wondering if it has to look up today's date for every row then is there a more efficient way to write this?
WHERE [Order Date] > Date() is perfectly fine. A function without arguments is always evaluated only once per query *even if it is in the output column list*!
It's just a "simple" Access form to enter the SQL and another form displaying the results. I'll check whether I can make this publicly available. - Will take some days...
I have found with mysql table (MariaDB), the selection-on Is Null is not very efficient. It is better to use (Compagny "") there, only 4 Text-Fields of course. Can you confirm it?
I don't use MySQL much at the moment, so I can't confirm. Your criteria example suggests NOT NULL criteria. This might also prevent index usage. Also NULL values might be excluded from the index. - Just guesses.
Thanks. Indexing as an element to performance was new to me in the 50krecord world I live in.
I like that tool that you created for testing the queries performance and the story at the end with the DBA guy was a lesson well learned.
Excellent video Phil! Nice coverage of fundamentals and insights on how the DB Engine 'thinks'. Keep the videos coming.
Great video, but the best part was the tongue-in-cheek comment around the .23 mark!
😆
Thank you, John!
the idea behind the MadMax criterias is very inspiring. I had a similar solution by chance, here the type of solution is shown again very well.
Very interesting recommendation I am beginner about the data engineering stuff and database development. I'll consider the examples to optimize any query.
Yes, please do. If these examples apply to your real live queries, you'll see huge performance gains.
Interesting and helpful information. Thank you, Philipp
I am not sure if you answered this, but speaking of built-in functions in the WHERE condition, what would be the best way to write a query to find all rows where the [Order Date] is in the future? The most natural way to do it would be "...WHERE [Order Date] > Date()". The engine is not processing anything on the table data using a function so that shouldn't be the performance hit you were discussing at around 19:00-20:00, but I'm wondering if it has to look up today's date for every row then is there a more efficient way to write this?
WHERE [Order Date] > Date() is perfectly fine.
A function without arguments is always evaluated only once per query *even if it is in the output column list*!
@@codekabinettcomen That's good to know. Thanks for the info!
How you make sql editor from?
It's just a "simple" Access form to enter the SQL and another form displaying the results.
I'll check whether I can make this publicly available. - Will take some days...
I have found with mysql table (MariaDB), the selection-on Is Null is not very efficient. It is better to use (Compagny "") there, only 4 Text-Fields of course. Can you confirm it?
I don't use MySQL much at the moment, so I can't confirm.
Your criteria example suggests NOT NULL criteria. This might also prevent index usage. Also NULL values might be excluded from the index. - Just guesses.
I want to learn
That's a great goal! I wish you a lot of success.