From the course: Generative AI for Data Engineering: ChatGPT Power Tips

Optimizing SQL queries with ChatGPT

From the course: Generative AI for Data Engineering: ChatGPT Power Tips

Start my 1-month free trial Buy for my team

Optimizing SQL queries with ChatGPT

“

- [Instructor] Have you ever written a SQL query that works, but takes forever to run? Performance matters, especially when dealing with the large data set. In this video, we are going to learn how ChatGPT can help you in optimizing that specific SQL queries. Let's just go back to our ChatGPT and see how it can help to optimize queries. Let me start a new chat. Assume that I have this SQL query, which is select start from orders where customer ID equal to 12345. Now, this query is taking a lot of time to load. Now, we want ChatGPT to optimize this query, and let's just see how ChatGPT can respond. Now, when a query is taking more time to load, there could be multiple reasons, and ChatGPT is trying to find out all those possible reasons, and the solution for those problem. For example, it could be that there is no index available on customer ID column, so it is pointing out that you can think of adding an index and that could help you to improve the performance. It could also be possible that this specific ID, 12345, has lot of the records. It could be possible that this specific table has lots of columns. What we can do is, we can limit the number of columns to select rather than selecting all the columns. That could also reduce the amount of data to be processed and our total execution time can get reduced. Maybe you can think of partitioning the table, or you could also think about optimizing the specific data types. Long story short, it gives you all the possible reasons which you can look at it and tweak one at a time. Let's take another example. Imagine that I have this query. Now, this query is kind of a subquery. Let me ask ChatGPT to optimize this query. Now, if a query is a subquery, generally subqueries are little bit having the performance issues. Now, to avoid that performance issues, it smartly uses the JOIN, which is a recommended approach as well, it also guide us to think about adding the indexes to improve the performance, it also talks about using some of the technique like EXISTS rather than using the IN clause. So the idea is, this kind of help from the ChatGPT, you get all the pointers to look towards, and that can reduce down the amount of time which you need to put in analyzing what could be the root cause. Hence, you can use ChatGPT smartly in optimizing your code to reduce down the total execution time, and improve overall performance.

Contents