99% Cost Improvement in This SQL - See How It’s Done

[@sdmagic]

You never disappoint! The plan goes liked this: You send and I receive an email -> I click the link and watch the video -> I learn A LOT! Thank you so very much.

[@DatabaseStar]

Thanks a lot! I'm glad you liked the video.

[@ThanhNguyen-qw7us]

Really really good video!!!!! Please make other videos about optimizing queries. It's really close to what's actually needs to be considered when working in the real tasks.

[@DatabaseStar]

Thanks a lot, I'm glad you enjoyed it!

[@gauravmanchanda3433]

Great demonstration Ben. Very helpful video.

[@DatabaseStar]

Glad it was helpful!

[@AlexanderWeurding]

Great video! Thanks for sharing!

[@DatabaseStar]

Thanks!

[@angelbear_og]

Nice, you made that really easy to understand!

[@DatabaseStar]

Thanks, I'm glad it was easy to understand!

[@CometHajjar]

Very nice and informative, Thank you

[@DatabaseStar]

Glad it was helpful!

[@shiwanginishishulkar4744]

Interesting video. I was just curious to understand how query costs are getting calculated here. If it's not mili second or second - then what this number actually mean?

[@DatabaseStar]

The Cost number is an arbitrary number, which means it doesn't represent any length of time or anything else like disk reads. It's a calculation that can be used to compare against other plans on the database.

[@michelchaghoury9629]

nice video, is there a similar feature (Execution plan and graph representation) in Oracle Database ?

[@DatabaseStar]

Yes there is. In Oracle SQL Developer, you can see the execution plan, but it's text based and not a chart or image like this one. Other IDEs will have different ways of viewing the plan.

[@michelchaghoury9629]

@@DatabaseStar can you please make a video on oracle execution plan how to read it and how know and understand about key improvements?

[@DatabaseStar]

Sure, good idea!

[@tomservo75]

I wish I were able to get that tool you're using for the flow chart but I can't find it in SQL Server (assuming that's where you're doing this). Is there ever a case to add multiple columns to an index?

[@DatabaseStar]

Yeah it's a good visualisation. This was done in MySQL Workbench which is using MySQL. SQL Server Management Studio has a similar visualisation, but it's not quite the same. I plan on creating a video like this using SQL Server so you may find that more useful. Adding multiple columns to an index is helpful in some scenarios. One would be if you have an Order By clause with multiple columns, you may want to add those columns to an index as it may improve the performance of the query.

[@alpsavasdev]

I did not understand how creating index on a temporary table would improve the query. If this operation is conducted every time this query runs, doesn't it increase the cost?

[@DatabaseStar]

Good point. Yes it would increase the cost as it’s run each time. If it runs slower overall compared to the original method than it may not help. Otherwise a permanent table could be another way to improve it where the index is kept.

[@danieltsvetkov695]

Great video! Thanks

[@DatabaseStar]

Thanks!