Unleashing SQL Sorcery: Increasing Performance and Complexity with GitHub Copilot

[Guest subhojitbasak]

Blog 2

 

 

 

So, you're ready to journey further into the realm of SQL development, armed with the magical GitHub Copilot. As a young SQL sorcerer, you've experienced the power of context-aware code suggestions. But now, let's delve into the realm of complex queries and optimize their performance. This is the second installment of our blog series where we'll discover how GitHub Copilot can help you tackle intricate SQL challenges while enhancing query efficiency.

 

This is the second installment of our blog series. You can access the first blog here. We have also created a video to simplify things for you, which you can watch as a prerequisite for this blog.

 

 

Navigating Complexity with Context

 

 

 

 

In our previous blog, we introduced you to the concept of using comments as tokens to provide context to Copilot. While Copilot is a wizard at generating simple queries, it might stumble when handling more intricate tasks, like joining multiple tables, optimizing queries, and navigating complex database structures.

 

 

 

Let's conjure an example: Instead of joining two tables, we make a prompt that tasks copilot to operate on multiple tables performing multiple joins. Let’s see if it can generate a correct answer. Please note that this is a continuation of our previous blog, hence we continue the same query where we have already performed the brain dump approach to Copilot.

 

 

[image: Complex query using multiple JOINS]

 

 

 

In this example Copilot could return code suggestions correctly for this complex scenario. However, it may not be the case for every complex prompt that it encounters. Perfecting the art of natural language prompts to ensure that Copilot can generate the correct query is also a learning curve.

 

I would suggest you try your own complex scenarios to see if Copilot is able to give you the right answer to it.

 

 

 

*Beyond SELECT: Boosting Performance with Copilot

 

 

 

 

Embrace GitHub Copilot's abilities to enhance not only the complexity of your queries but also their efficiency. Your journey as a SQL developer is filled with opportunities to transform ordinary queries into enchanting solutions, all thanks to the mystical powers of Copilot.

 

Your query, once a tangled incantation, now yields to the treasures you seek. But for that, it is important for you to know the kind of optimization you are looking for. A few examples of query optimization are listed below. For readers who are veterans in SQL development, can skip the first example:

 

Example 1: Adding Index to Query to increase performance

 

 

Continuing the WideWorldImporters Database, considering a scenario where we must retrieve data from a multiple-condition query frequently and the dataset is large. In such cases, if there is an index, it helps retrieve data faster.

 

 

[image: Query before indexing]

 

 

 

 

[image: After prompting for index, GitHub Copilot prompts the query with the index.]

 

 

 

Indexed queries, in such scenarios, can perform faster and better than non-indexed ones.

 

Example 2: Optimizing Functions

 

 

There are situations when a query running on large database performs inefficiently because of a function.

 

 

[image: Code Snippet of the query before optimization]

 

 

 

 

[image: Query Stats for the non-optimized query]

 

 

 

Once we let Copilot optimize the query, we notice that it brings down the number of iterations to ~half.

 

 

[image: Code Snippet of the query post optimization using GitHub Copilot]

 

 

 

 

[image: Query Stats for the optimized query]

 

 

 

 

 

 

Example 3: Removing table variable reference to optimize a query

 

 

Table variables can at times generate queries with high time complexity.

 

 

[image: Code Snippet of the query before optimization]

 

 

 

 

[image: Query Stats for the non-optimized query]

 

 

 

GitHub Copilot helps you to optimize this by removing the table variable and rewriting the query. The time difference depends on the size of the table and number of rows that are affected.

 

 

[image: Code Snippet of the query post optimization using GitHub Copilot]

 

 

 

 

[image: Query Stats for the optimized query]

 

 

 

Example 4: Removing cursor to optimize a query

 

 

 

 

When we prompt GitHub Copilot to optimize this query, it smartly removes cursor and uses JOIN and WHERE clause to generate the query. This modified query is time optimized.

 

 

 

 

[image: Example of the query before optimization]

 

 

 

 

 

 

[image: Example of an optimized query]

 

 

 

The query after optimization ran 22% faster than the first one. For this example, we have used the WideWorldImporters Database. The number of rows affected was almost half in the optimized query. For a significantly large database, this optimization can make a huge difference.

 

Embark on the Quest

 

 

 

 

Unlock GitHub Copilot's full potential within Azure Data Studio by installing the latest release and integrating the extension from the marketplace. Explore the depths of Copilot's magic in Azure Data Studio and GitHub Copilot's official documentation.

 

 

 

In the next installment of our series, we'll delve into advanced tips and tricks, unraveling even more secrets to optimize your SQL prowess. Until then, may your queries be optimized, and your database be ever responsive!

 

 

 

Vote of Thanks: A special mention to Tim Chapman, who helped us in working on complex examples and use cases of SQL Query.

 

Continue reading...