<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Rajesh Balamohan on Medium]]></title>
        <description><![CDATA[Stories by Rajesh Balamohan on Medium]]></description>
        <link>https://medium.com/@rajeshbalamohan?source=rss-b15106b3dd60------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*YCA-oz_kBRMuT0Nl.</url>
            <title>Stories by Rajesh Balamohan on Medium</title>
            <link>https://medium.com/@rajeshbalamohan?source=rss-b15106b3dd60------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 23:14:51 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@rajeshbalamohan/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Translating Natural Language into SQL: GPT-4 vs Claude 3 Showdown]]></title>
            <link>https://blog.waii.ai/translating-natural-language-into-sql-gpt-4-vs-claude-3-showdown-603ba054c04f?source=rss-b15106b3dd60------2</link>
            <guid isPermaLink="false">https://medium.com/p/603ba054c04f</guid>
            <category><![CDATA[text-to-sql]]></category>
            <category><![CDATA[claude]]></category>
            <category><![CDATA[gpt]]></category>
            <category><![CDATA[openai]]></category>
            <category><![CDATA[llm]]></category>
            <dc:creator><![CDATA[Rajesh Balamohan]]></dc:creator>
            <pubDate>Fri, 15 Mar 2024 07:00:20 GMT</pubDate>
            <atom:updated>2024-03-15T07:00:20.328Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AV1Yo_JtCw9xK5sFCuZRgQ.jpeg" /></figure><p>Anthropic recently released the Claude 3 model (<a href="https://www.anthropic.com/news/claude-3-family">Introducing the next generation of Claude Anthropic</a>), boasting impressive benchmarks compared to other LLMs. At <a href="http://waii.ai/">Waii.ai</a> we are particularly interested in one aspect of the model: Natural language to SQL translation.</p><p>Despite multiple attempts by small and large models to change this, GPT-4 (1106 model) has been the best ranking model in this space in our testing since its release. We are thus curious about how Claude 3 compares to the incumbent.</p><p>For this evaluation, we&#39;ve selected a number of different datasets, including <a href="https://yale-lily.github.io/spider">Spider</a> and <a href="https://www.tpc.org/tpcds/">TPC-DS</a> datasets as we have done before (See: <a href="https://medium.com/querymind/gpt-4s-sql-mastery-2cd1f3dea543">GPT-4 SQL mastery</a>, <a href="https://medium.com/querymind/gpt-4-in-winter-sql-land-tackling-snowflake-schemas-f1fc4626262a">GPT-4 tpcds</a>). Spider is renowned for its comprehensive SQL query generation challenges across various domains. TPC-DS is gold standard for evaluating decision support systems. Our goal is to see how well each model understands and translates natural language into SQL queries, using accuracy and query generation time as our key metrics.</p><h3>Evaluation Metrics</h3><ul><li><strong>Accuracy:</strong> We gauge how accurately the LLMs create SQL queries that align with the expected structure and logic. By comparing the generated queries with ground truth query, we measure the models&#39; precision. A higher accuracy rate reflects a model&#39;s ability to comprehend and translate complex instructions from natural language to SQL. Additional information about the metric can be found in <a href="https://www.cidrdb.org/cidr2024/papers/p74-floratou.pdf">CIDR paper</a> and <a href="https://github.com/archerfish-bench/benchmark">archerfish-benchmark</a></li><li><strong>Query Generation Time:</strong> This represents the time taken by the LLM to generate the SQL.</li></ul><h3>Results Overview:</h3><h3>Simple Queries:</h3><p>We chose a subset of spider as our testbed for simpler queries. Spider is a well-known benchmark, it has a variety of different databases, and is readily available. Datasets typically only have few tables, and the golden queries are relatively simple (few joins, few aggregates, etc). We sampled 384 queries from a variety of schemas, including <strong>battle_death</strong>, <strong>concert_singer</strong>, <strong>employee_hire_evaluation</strong>, <strong>flight</strong>, <strong>museum_visit</strong>, <strong>orchestra</strong>, <strong>poker_player</strong>, <strong>singer</strong>, <strong>tvshow</strong>, and <strong>voter</strong>. This ensures a comprehensive assessment across different domains.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/426/1*y71t4Wj4lfduLuvD_8Iv3g.png" /></figure><p>Graph clearly shows that Claude 3&#39;s performance is remarkably similar to that of GPT-4&#39;s 1106 model. This is the first time that we’ve seen a contender coming this close to GPT-4 performance. That’s a significant milestone. After examining the inaccuracies in the queries generated by Claude 3, we categorized the 26 errors out of the 384 queries into the following buckets:</p><ul><li>Incorrect interpretation of fields</li><li>Confusion over AND/OR conditions</li><li>Errors in understanding join conditions</li><li>Omissions of necessary filters</li></ul><p>Let&#39;s dive into a specific instance where Claude 3 misinterpreted the fields, leading to incorrect join conditions.</p><pre>List unique name and date, the battle that has lost the ship named &#39;Lettice&#39; and the ship named &#39;HMS Atalanta&#39;<br><br>Generated query:<br>================<br><br>WITH SHIP_IDS AS<br>	(SELECT DISTINCT ID<br>		FROM BATTLE_DEATH.SHIP<br>		WHERE NAME IN (&#39;Lettice&#39;,&#39;HMS Atalanta&#39;) ),<br>	BATTLE_IDS AS<br>	(SELECT DISTINCT DEATH.CAUSED_BY_SHIP_ID AS SHIP_ID<br>		FROM BATTLE_DEATH.DEATH<br>		WHERE DEATH.CAUSED_BY_SHIP_ID IN<br>				(SELECT SHIP_IDS.ID<br>					FROM SHIP_IDS) )<br>SELECT DISTINCT BATTLE.NAME,<br>	BATTLE.DATE<br>FROM BATTLE_DEATH.BATTLE<br>INNER JOIN BATTLE_IDS ON BATTLE.ID = BATTLE_IDS.SHIP_ID</pre><p>In this query, Claude 3 incorrectly joined <strong>battle.id</strong> with <strong>battle_ids.ship_id</strong>, which are not related fields. Waii automatically provides information to LLM on performing joins. The accurate join condition for this LLM generated query, should link the <strong>ship</strong> table to the <strong>battle</strong> table through <strong>ship.lost_in_battle</strong> and <strong>battle.id</strong>, as accurately depicted in the ground truth query.</p><pre>Ground truth query:<br>===================<br><br>SELECT DISTINCT T1.NAME,<br>	T1.DATE<br>FROM SPIDER_DEV.BATTLE_DEATH.BATTLE AS T1<br>JOIN SPIDER_DEV.BATTLE_DEATH.SHIP AS T2 ON T1.ID = T2.LOST_IN_BATTLE<br>WHERE T2.NAME = &#39;Lettice&#39; INTERSECT<br>	SELECT T1.NAME,<br>		T1.DATE<br>	FROM SPIDER_DEV.BATTLE_DEATH.BATTLE AS T1<br>	JOIN SPIDER_DEV.BATTLE_DEATH.SHIP AS T2 ON T1.ID = T2.LOST_IN_BATTLE WHERE T2.NAME = &#39;HMS Atalanta&#39;</pre><h3>Query Generation time:</h3><p>Claud 3 had ~28% higher query generation time compared to GPT-4.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/420/1*7zXWYPYnpC-slwybs2liTQ.png" /></figure><h3>Medium Queries:</h3><p>In order to raise the complexity a bit, we picked a ‘raw’ dataset: tv_data. This dataset captures information about movies, television series and the people working on them. The overall number of tables and columns in this set is fairly small, but this dataset’s main characteristic is that much of the data resides in heavily nested json fields. We looked at a sample of 29 queries to see how well Claude 3 would manage them. In this test, Claude 3 successfully handled 68.97% of the queries, while GPT-4 had a success rate of 93.1%</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/425/1*zpfwp0JPl_0OsikjAgum7Q.png" /></figure><p>We categorized the errors from Claude 3 into the following buckets:</p><ul><li>related to join condition</li><li>related to where clause/condition</li><li>related to misunderstanding of question</li><li>related to misinterpretation of complex field</li></ul><p>Let us take one of the example queries where it failed in Claude 3.</p><pre>List all movies directed by &quot;David Slade&quot; along with their runtime.<br><br>Generated query:<br>================<br>SELECT &quot;Asset Title&quot;,<br>	&quot;Runtime&quot;<br>FROM CINE_TELE_DATA.MOVIES<br>WHERE &#39;David Slade&#39; IN<br>		(SELECT VALUE<br>			FROM CINE_TELE_DATA.MOVIES<br>			CROSS JOIN LATERAL FLATTEN(INPUT =&gt; MOVIES.&quot;Directors&quot;))</pre><p>The generated query searches for &#39;David Slade&#39; using an uncorrelated subquery. This results in all rows incorrectly being returned from movies table when there is a match for one row. Ground truth query is given below.</p><pre>Ground truth query:<br>==================<br><br>SELECT &quot;Asset Title&quot;,<br>		&quot;Runtime&quot;<br>	FROM CINE_DB.CINE_TELE_DATA.MOVIES AS M<br>	CROSS JOIN LATERAL FLATTEN(INPUT =&gt; &quot;Directors&quot;) AS F WHERE F.VALUE ILIKE &#39;%David Slade%&#39;</pre><h3>Query Generation Time:</h3><p>From query generation perspective, Claude 3 took <strong>64%</strong> higher time than GPT-4 model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/423/1*svsYK24pEfCByXjP4O2W6Q.png" /></figure><h3>Complex Queries:</h3><p>For the most complex queries we used the TPCDS dataset. The database is laid out as a well-modeled star schema, and typical queries require 5-10 joins, multiple subqueries, aggregates and window functions.</p><p>In our examination of the TPCDS dataset, we closely compared Claude 3&#39;s query generation accuracy against GPT-4. We evaluated 32 queries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/1*-DTNlGXsNcuyZdGbq9ybng.png" /></figure><p>We analyzed the results and here are the buckets in which Claude 3 generated queries had issues with:</p><ul><li>Misinterpretations of the question leading to incorrect SQL</li><li>Omissions of dimension tables, resulting in inaccurate queries</li><li>Missing filters</li><li>Errors in group by statements and joins</li></ul><p>Let us discuss an example that illustrates one such query.</p><pre>Generate a report listing the top 100 unique items, including their IDs, descriptions, and current prices, which were in the inventory during a specific 60-day period starting from &#39;2000-05-25&#39;. These items should have been sold in store sales channel and should have been manufactured by specific manufacturers (129, 270, 821, 423) with item&#39;s price in the range of 62 and 92. Quantity of these items in the inventory should be between 100 and 500. Sort distinct results by item&#39;s id. Display item&#39;s id, item description and its current price. Show only 100 rows.</pre><pre><br>Generated query:<br>================<br><br>WITH ITEM_FILTERED AS<br>	(SELECT I_ITEM_ID,<br>			I_ITEM_DESC,<br>			I_CURRENT_PRICE,<br>			I_ITEM_SK<br>		FROM SAMPLE_TPCDS.ITEM<br>		WHERE I_MANUFACT_ID IN (129,270,821,423)<br>			AND I_CURRENT_PRICE BETWEEN 62 AND 92 ),<br>	INVENTORY_FILTERED AS<br>	(SELECT INVENTORY.INV_ITEM_SK,<br>			INVENTORY.INV_QUANTITY_ON_HAND<br>		FROM SAMPLE_TPCDS.INVENTORY<br>		WHERE INVENTORY.INV_DATE_SK BETWEEN 2451699 AND 2451759<br>			AND INVENTORY.INV_QUANTITY_ON_HAND BETWEEN 100 AND 500 ),<br>	STORE_SALES_FILTERED AS<br>	(SELECT STORE_SALES.SS_ITEM_SK<br>		FROM SAMPLE_TPCDS.STORE_SALES)<br>SELECT I.I_ITEM_ID,<br>	I.I_ITEM_DESC,<br>	I.I_CURRENT_PRICE<br>FROM ITEM_FILTERED AS I<br>INNER JOIN INVENTORY_FILTERED AS INV ON I.I_ITEM_SK = INV.INV_ITEM_SK<br>INNER JOIN STORE_SALES_FILTERED AS SS ON I.I_ITEM_SK = SS.SS_ITEM_SK<br>ORDER BY I.I_ITEM_ID<br>LIMIT 100;</pre><p>Claude 3 generated query failed to incorporate the <strong>date_dim</strong> table and its related filters in the query. It uses surrogate keys that mistakenly represent the year 2000, rather than the intended 60-days period starting from specific date. This is not too surprising given the training set. Many benchmarks have been written based on TPCDS with fixed surrogate keys and this pattern is used here. Waii explicitly instructs the model to do the date computation a certain way, but that was ignored by Claude. It seems that GPT-4 still has a slight edge in instruction following.</p><p>There is also an issue on grouping, but that can be fixed by adding distinct keyword to the generated query. But missing the date_dim causes query output to be different than ground truth query which is given below.</p><pre>Ground truth query:<br>===================<br><br>SELECT I_ITEM_ID,<br>	I_ITEM_DESC,<br>	I_CURRENT_PRICE<br>FROM TWEAKIT_PERF_DB.SAMPLE_TPCDS.ITEM,<br>	TWEAKIT_PERF_DB.SAMPLE_TPCDS.INVENTORY,<br>	TWEAKIT_PERF_DB.SAMPLE_TPCDS.DATE_DIM,<br>	TWEAKIT_PERF_DB.SAMPLE_TPCDS.STORE_SALES<br>WHERE I_CURRENT_PRICE BETWEEN 62 AND 62 + 30<br>	AND INV_ITEM_SK = I_ITEM_SK<br>	AND D_DATE_SK = INV_DATE_SK<br>	AND D_DATE BETWEEN &#39;2000-05-25&#39; AND &#39;2000-07-24&#39;<br>	AND I_MANUFACT_ID IN (129,270,821,423)<br>	AND INV_QUANTITY_ON_HAND BETWEEN 100 AND 500<br>	AND SS_ITEM_SK = I_ITEM_SK<br>GROUP BY I_ITEM_ID,<br>	I_ITEM_DESC,<br>	I_CURRENT_PRICE<br>ORDER BY I_ITEM_ID<br>LIMIT 100</pre><h3>Query Generation Time:</h3><p>W.r.t average query generation time for more complex queries: Claude 3 takes ~25.2% more query generation time than GPT-4.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/425/1*FmnkKVt_cjmEwd8HF6wDTw.png" /></figure><h3>Conclusion:</h3><p>In summary, Claude 3 shows strong natural-language-to-sql performance, compared to most other LLMs we looked at in the past. Congratulations to the Anthropic team, this is a remarkable achievement.</p><p>The reigning champion, GPT-4 still has an advantage, in both more complex queries as well as overall generation speed. However, the margin is closing and for simpler SQL workloads Claude 3 is a viable alternative.</p><p>Having models like Claude 3 and GPT-4 is essential for pushing forward the boundaries of what&#39;s possible with natural language to SQL processing with LLMs. It not only drives improvements in model accuracy and efficiency but also provides users with a variety of tools that better fit their specific needs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=603ba054c04f" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/translating-natural-language-into-sql-gpt-4-vs-claude-3-showdown-603ba054c04f">Translating Natural Language into SQL: GPT-4 vs Claude 3 Showdown</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Effortless Performance Analysis with Waii’s Text-to-SQL Platform]]></title>
            <link>https://blog.waii.ai/effortless-performance-analysis-with-waiis-text-to-sql-platform-8ec2eb42aa64?source=rss-b15106b3dd60------2</link>
            <guid isPermaLink="false">https://medium.com/p/8ec2eb42aa64</guid>
            <category><![CDATA[text-to-sql]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[gpt]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Rajesh Balamohan]]></dc:creator>
            <pubDate>Thu, 28 Sep 2023 01:37:01 GMT</pubDate>
            <atom:updated>2025-03-04T23:08:02.507Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1023/1*4TJRveThzTPUlJeFNONkoQ.jpeg" /></figure><p>As a performance engineer, I often have to analyse metrics across various components to gain valuable insights on answering performance questions. In the context of Snowflake warehouse, a lot of performance related data is available in the form of views — from query_history to warehouse_load_history and access_history. Documentation on accessing these views are well covered in <a href="https://community.snowflake.com/s/article/Solution-Grant-access-to-specific-views-in-SNOWFLAKE-ACCOUNT-USAGE-to-custom-roles">snowflake community article</a>.</p><p>I set up these snowflake views in my SNOWFLAKE_PERF_COPY database in one of my internal clusters. But instead of slogging through writing lines of SQL code to mine these invaluable views, I decided to get the help of <a href="https://www.waii.ai/">Waii’s text-to-SQL platform</a>. My goal was simple: ask simple to complex performance questions in plain, natural language and let the system come up with relevant SQL.</p><p>Snowflake has various aspects of performance with respect to data loading, transformation, and reporting queries. For this blog, I will primarily focus on dissecting non-data load queries within one of my internal clusters.</p><h3>Slow query insights</h3><p>My adventure begins with a fundamental question: which queries have been consuming the most amount of time in the data warehouse? Snowflake’s native “query_history” already has this covered with its comprehensive details and nifty filters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3FUwxi66FnlS3xZIbs_mcQ.png" /></figure><p>However, from a performance perspective, I wanted to go beyond slow queries and dive into the tables they frequently accessed along with table sizes and cache utilisation. This information would be very useful, when there are lots of tables present in the system and we need to quickly nail down the few databases of interest. I posted this question to Waii:</p><p>“<em>Over the past two weeks, identify the 200 queries with the longest runtime. For these queries, provide insights into the frequently accessed tables, including the number of times each table was accessed and the users who ran these queries. Additionally, report the average runtime of these queries rounded to one decimal place, along with the associated warehouses where they were executed. Include the size of these tables and the average amount scanned from cache. Present the top 200 rows sorted by average runtime and include a breakdown of successful and failed queries.”</em></p><pre>WITH filtered_query_history AS (<br>    SELECT *<br>    FROM snowflake_perf_copy.perf.query_history<br>    WHERE<br>        start_time &gt;= DATEADD(DAY, -14, CURRENT_DATE)<br>),<br><br>top_200_queries AS (<br>    SELECT<br>        query_id,<br>        user_name,<br>        warehouse_name,<br>        total_elapsed_time,<br>        execution_status,<br>        percentage_scanned_from_cache<br>    FROM filtered_query_history<br>    ORDER BY<br>        total_elapsed_time DESC<br>    LIMIT 200<br>),<br><br>joined_access_history AS (<br>    SELECT<br>        t.query_id,<br>        t.user_name,<br>        t.warehouse_name,<br>        t.total_elapsed_time,<br>        a.base_objects_accessed,<br>        execution_status,<br>        percentage_scanned_from_cache<br>    FROM top_200_queries AS t<br>    INNER JOIN snowflake_perf_copy.perf.access_history AS a<br>        ON t.query_id = a.query_id<br>),<br><br>flattened_access_history AS (<br>    SELECT<br>        j.user_name,<br>        j.warehouse_name,<br>        j.total_elapsed_time,<br>        f.value[&#39;objectName&#39;] AS table_name,<br>        execution_status,<br>        percentage_scanned_from_cache<br>    FROM joined_access_history AS j<br>    CROSS JOIN LATERAL FLATTEN(input =&gt; j.base_objects_accessed) AS f<br>)<br><br>SELECT<br>    f.table_name,<br>    COUNT(*) AS num_accesses,<br>    ARRAY_AGG(DISTINCT f.user_name) AS users,<br>    ROUND(AVG(f.total_elapsed_time), 1) AS avg_runtime,<br>    COUNT(CASE WHEN f.execution_status = &#39;SUCCESS&#39; THEN 1 END) AS successful_queries,<br>    COUNT(CASE WHEN f.execution_status = &#39;FAIL&#39; THEN 1 END) AS failed_queries,<br>    AVG(f.percentage_scanned_from_cache) AS avg_percentage_scanned_from_cache,<br>    ROUND(tsm.active_bytes / 1024 / 1024 / 1024, 1) AS table_size_gb<br>FROM flattened_access_history AS f<br>INNER JOIN snowflake_perf_copy.perf.table_storage_metrics AS tsm<br>    ON CONCAT(tsm.table_catalog, &#39;.&#39;, tsm.table_schema, &#39;.&#39;, tsm.table_name) = f.table_name<br>GROUP BY<br>    f.table_name,<br>    tsm.active_bytes<br>ORDER BY<br>    avg_runtime DESC<br>LIMIT 200</pre><p>Initially, Waii overlooked table sizes and missed adding it, but a subtle hint, <em>“Add table size from table_storage_metrics,”</em> did the trick of including table size details. It was able to join different views and interestingly was able to handle complex data type like “base_objects_accessed” in access_history table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o-CRrSOB8DvImhX3LNARxQ.png" /></figure><h3>Workload mysteries</h3><h4>Hourly query execution pattern</h4><p>I wanted to analyse query execution patterns on an hourly basis throughout the week to identify workload anomalies. Snowflake provides a workload graph for warehouses.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qH3NtaMh1jjjQCJ4yHR1wQ.png" /></figure><p>It would be great to have a heapmap/gridmap to show the number of queries executed by hour and day.</p><p>Question to Waii</p><p><em>For the last 1 week, can you identify any patterns in query execution times, number of queries executed categorised by the hour of the day and day of the week? The report should present this data with each day of the week as columns, spanning from Sunday to Saturday.</em></p><pre>WITH filtered_query_history AS (<br>    SELECT *<br>    FROM snowflake_perf_copy.perf.query_history<br>    WHERE<br>        start_time &gt;= DATEADD(DAY, -7, CURRENT_DATE)<br>),<br><br>query_hour_day AS (<br>    SELECT<br>        EXTRACT(HOUR FROM start_time) AS hour_of_day,<br>        EXTRACT(DAYOFWEEK FROM start_time) AS day_of_week,<br>        COUNT(query_id) AS num_queries<br>    FROM filtered_query_history<br>    GROUP BY<br>        EXTRACT(HOUR FROM start_time),<br>        EXTRACT(DAYOFWEEK FROM start_time)<br>)<br><br>SELECT<br>    hour_of_day,<br>    SUM(CASE WHEN day_of_week = 1 THEN num_queries ELSE 0 END) AS sunday,<br>    SUM(CASE WHEN day_of_week = 2 THEN num_queries ELSE 0 END) AS monday,<br>    SUM(CASE WHEN day_of_week = 3 THEN num_queries ELSE 0 END) AS tuesday,<br>    SUM(CASE WHEN day_of_week = 4 THEN num_queries ELSE 0 END) AS wednesday,<br>    SUM(CASE WHEN day_of_week = 5 THEN num_queries ELSE 0 END) AS thursday,<br>    SUM(CASE WHEN day_of_week = 6 THEN num_queries ELSE 0 END) AS friday,<br>    SUM(CASE WHEN day_of_week = 7 THEN num_queries ELSE 0 END) AS saturday<br>FROM query_hour_day<br>GROUP BY<br>    hour_of_day<br>ORDER BY<br>    hour_of_day</pre><p>I used this data to generate a report using Google Chart. It is important to consider aspects such as response time and the amount of bytes scanned for further analysis. However, as a starting point, I focused on exploring the number of executed queries.</p><p>Queries executed on each hour of the day throughout the week:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/818/1*KdsSH4XDCUjOQJ_rG87YXQ.png" /></figure><h3>Tracking database growth over time</h3><p>It’s a commonly recognised fact that in production environments, certain databases have the potential to have unforeseen growth, leading to delays in query execution. This led me to ask Waii about analysing the database growth over time.</p><p><em>What is the database growth over time? How has storage usage grown week on week for the last 8 weeks, and which databases have contributed the most to this growth?</em></p><pre>WITH weekly_storage_usage AS (<br>    SELECT<br>        database_name,<br>        DATE_TRUNC(&#39;WEEK&#39;, usage_date) AS week,<br>        SUM(average_database_bytes) / NULLIF((<br>            1024 * 1024 * 1024<br>        ), 0) AS storage_gb<br>    FROM snowflake_perf_copy.perf.database_storage_usage_history<br>    GROUP BY<br>        database_name,<br>        DATE_TRUNC(&#39;WEEK&#39;, usage_date)<br>),<br><br>week_on_week_growth AS (<br>    SELECT<br>        database_name,<br>        week,<br>        storage_gb,<br>        LAG(storage_gb) OVER (PARTITION BY database_name ORDER BY week) AS prev_storage_gb<br>    FROM weekly_storage_usage<br>)<br><br>SELECT<br>    database_name,<br>    week,<br>    ROUND((<br>        storage_gb - prev_storage_gb<br>    ), 1) AS growth_gb<br>FROM week_on_week_growth<br>WHERE<br>    week &gt;= DATEADD(WEEK, -8, CURRENT_DATE)<br>ORDER BY<br>    growth_gb DESC nulls last</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*guROBHa7C0sZwzL-75gXLg.png" /></figure><h3>In search of inefficient grouping operators</h3><p>I wanted to analyse the efficiency of common group/aggregation/filters executed by those top 200 slow queries for fine-tuning them later. Snowflake provides additional statistics about each query through get_query_operator_stats table function (<a href="https://docs.snowflake.com/en/sql-reference/functions/get_query_operator_stats#label-get-query-operator-stats-operator-statistics">Reference</a>). This function accepts query_id as input. Since joining the table function with “query_history” was not straight forward, I wanted to have a small stored procedure to populate these statistics in a separate table. I experimented with Waii, and it generated the following stored procedure. Although there were a few minor compilation issues initially, they were easily resolved.</p><pre>CREATE OR REPLACE TABLE snowflake_perf_copy.perf.q_op_stats AS <br> SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS(&#39;dummy_query_id&#39;)) WHERE 1 = 0;<br><br>CREATE OR REPLACE PROCEDURE ITERATE_QUERIES() RETURNS STRING LANGUAGE JAVASCRIPT AS<br>  $$ var query = `WITH top_queries AS (SELECT query_id <br>    FROM SNOWFLAKE_PERF_COPY.PERF.QUERY_HISTORY <br>     WHERE <br>      execution_status = &#39;SUCCESS&#39; AND query_type = &#39;SELECT&#39; <br>      AND start_time &gt;= DATEADD(day, -14, CURRENT_DATE) <br>    ORDER BY total_elapsed_time DESC LIMIT 200) SELECT query_id FROM top_queries`; <br>  var stmt = snowflake.createStatement({sqlText: query}); <br>  var result_set = stmt.execute(); <br>  while (result_set.next()) { <br>   var query_id = result_set.getColumnValue(1); <br>   var insert_query = `INSERT INTO snowflake_perf_copy.perf.q_op_stats SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS(&#39;${query_id}&#39;))`; <br>   var insert_stmt = snowflake.createStatement({sqlText: insert_query}); <br>   insert_stmt.execute(); <br>  } <br>  return &#39;Done&#39;; $$;<br>  <br>CALL ITERATE_QUERIES();</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zkL1QQW1ZXR3_CEZG_G-vg.png" /></figure><p>With this dataset, I wanted to identify high cardinality grouping operators. These are operators that emit 80% of the input rows to output — a potential clue for optimising query performance. 80% is not a hard number, it can vary from system to system. In certain cases, identifying these common groupings can lead to deeper understanding of disk spills in slow queries. Administrators can also explore creating materialised views in certain cases to avoid these expensive groupings across queries.</p><p>Question to Waii:</p><p><em>Can you display inefficient high cardinality grouping operators, specifically those where the output rows exceeded 80% of the input rows? Please provide the query text, operator attributes, and overall execution percentage, rounded to two decimal places. Additionally, indicate whether there were any local disk spills for these queries, and sort the results by overall execution percentage.</em></p><pre>WITH high_cardinality_grouping_operators AS (<br>    SELECT<br>        q.query_id,<br>        q.operator_attributes,<br>        q.operator_statistics,<br>        q.execution_time_breakdown,<br>        qh.query_text<br>    FROM snowflake_perf_copy.perf.q_op_stats AS q<br>    INNER JOIN snowflake_perf_copy.perf.query_history AS qh<br>        ON q.query_id = qh.query_id<br>    WHERE<br>        q.operator_type = &#39;Aggregate&#39;<br>        AND (<br>            CAST(q.operator_statistics[&#39;output_rows&#39;] AS FLOAT)<br>            / NULLIF(CAST(q.operator_statistics[&#39;input_rows&#39;] AS FLOAT), 0)<br>        ) &gt; 0.8<br>)<br><br>SELECT<br>    query_text,<br>    operator_attributes,<br>    ROUND(CAST(execution_time_breakdown[&#39;overall_percentage&#39;] AS FLOAT) * 100, 2) AS overall_percentage,<br>    CASE<br>        WHEN CAST(execution_time_breakdown[&#39;local_disk_io&#39;] AS FLOAT) &gt; 0<br>            THEN &#39;Yes&#39;<br>        ELSE &#39;No&#39;<br>    END AS local_disk_spill<br>FROM high_cardinality_grouping_operators<br>ORDER BY<br>    overall_percentage DESC</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/732/1*sNNqs_30__xcIZSzyCJ3Mw.png" /></figure><h3>Conclusion:</h3><p>Though there are lot of performance questions when looking at optimising a system, in this blog I explored whether few of these performance questions can be expressed via natural language to gain insights via Waii’s text-to-SQL platform. Overall, it effortlessly generated SQL queries for most questions and in few cases, required gentle nudges in the form of hints.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8ec2eb42aa64" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/effortless-performance-analysis-with-waiis-text-to-sql-platform-8ec2eb42aa64">Effortless Performance Analysis with Waii’s Text-to-SQL Platform</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[AI vs Human: Query performance]]></title>
            <link>https://blog.waii.ai/ai-vs-human-query-performance-b0405a41f92a?source=rss-b15106b3dd60------2</link>
            <guid isPermaLink="false">https://medium.com/p/b0405a41f92a</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[gpt]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Rajesh Balamohan]]></dc:creator>
            <pubDate>Thu, 31 Aug 2023 23:45:56 GMT</pubDate>
            <atom:updated>2023-11-09T23:45:27.160Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1016/1*9vxS7ohsePRCH4wT-LnGVw.png" /></figure><p>In this blog we deep dive the question of whether queries generated by <a href="https://www.waii.ai/">Waii</a> (a text-to-SQL platform) can outperform manually crafted queries. Buckle up.</p><h3>15-second primer: Database engines and query optimizers</h3><p>Database optimization has been a fruitful topic of research ever since the inception of SQL in the 70s. Modern database engines should be able to find optimal execution plans for queries regardless of how they are written out in SQL. This is especially important in the analytics space where suboptimal plans can cause queries to run for hours and waste significant resources.</p><p>Here are some examples of the techniques used to find those optimal execution plans:</p><ul><li>Automatic join reordering</li><li>Aggressive predicate pushdown</li><li>Automatic use of materialized views</li><li>Removal of unnecessary joins</li><li>Column pruning</li></ul><p>This leads to an interesting question: Do AI-generated queries perform better, worse or the same as queries crafted by humans in the presence of an optimizer?</p><h3>Query under spotlight:</h3><p>For the purpose of this blog, we will narrow our focus to query-9 of the TPC-DS benchmark. TPC-DS benchmark is the most widely used industry benchmark for evaluating decision support systems and data warehouses with respect to query speed. And as you will see query-9 tells us a lot about what the current state of the art is.</p><p><strong>Query 9: Business question &amp; SQL</strong></p><p>Query 9 corresponds to these instructions: “<em>Categorize store sales transactions into 5 buckets according to the number of items sold. Each bucket contains the average discount amount, sales price, list price, tax, net paid, paid price including tax, or net profit</em>”</p><p>Here is the resulting SQL query:</p><pre>select case when (select count(*) from store_sales<br>                  where ss_quantity between 1 and 20) &gt; 74129<br>            then (select avg(ss_ext_discount_amt) from store_sales<br>                  where ss_quantity between 1 and 20)<br>            else (select avg(ss_net_paid) from store_sales<br>                  where ss_quantity between 1 and 20) end bucket1 ,<br>       case when (select count(*) from store_sales<br>                  where ss_quantity between 21 and 40) &gt; 122840<br>            then (select avg(ss_ext_discount_amt) from store_sales<br>                  where ss_quantity between 21 and 40)<br>            else (select avg(ss_net_paid) from store_sales<br>                  where ss_quantity between 21 and 40) end bucket2,<br>       case when (select count(*) from store_sales<br>                  where ss_quantity between 41 and 60) &gt; 56580<br>            then (select avg(ss_ext_discount_amt) from store_sales<br>                  where ss_quantity between 41 and 60)<br>            else (select avg(ss_net_paid) from store_sales<br>                  where ss_quantity between 41 and 60) end bucket3,<br>       case when (select count(*) from store_sales<br>                  where ss_quantity between 61 and 80) &gt; 10097<br>            then (select avg(ss_ext_discount_amt) from store_sales<br>                  where ss_quantity between 61 and 80)<br>            else (select avg(ss_net_paid) from store_sales<br>                  where ss_quantity between 61 and 80) end bucket4,<br>       case when (select count(*) from store_sales<br>                  where ss_quantity between 81 and 100) &gt; 165306<br>            then (select avg(ss_ext_discount_amt) from store_sales<br>                  where ss_quantity between 81 and 100)<br>            else (select avg(ss_net_paid) from store_sales<br>                  where ss_quantity between 81 and 100) end bucket5<br>from reason<br>where r_reason_sk = 1</pre><p>And here is the query generated from the instructions above by the Waii for Query 9. It is interesting to note that the generated query is a lot simpler to read and to maintain.</p><pre>SELECT<br>    MAX(CASE WHEN bucket = &#39;Bucket1&#39; THEN value END) AS bucket1,<br>    MAX(CASE WHEN bucket = &#39;Bucket2&#39; THEN value END) AS bucket2,<br>    MAX(CASE WHEN bucket = &#39;Bucket3&#39; THEN value END) AS bucket3,<br>    MAX(CASE WHEN bucket = &#39;Bucket4&#39; THEN value END) AS bucket4,<br>    MAX(CASE WHEN bucket = &#39;Bucket5&#39; THEN value END) AS bucket5<br>FROM (<br>    WITH bucket_counts AS (<br>        SELECT<br>            CASE<br>                WHEN ss_quantity BETWEEN 1 AND 20<br>                    THEN &#39;Bucket1&#39;<br>                WHEN ss_quantity BETWEEN 21 AND 40<br>                    THEN &#39;Bucket2&#39;<br>                WHEN ss_quantity BETWEEN 41 AND 60<br>                    THEN &#39;Bucket3&#39;<br>                WHEN ss_quantity BETWEEN 61 AND 80<br>                    THEN &#39;Bucket4&#39;<br>                WHEN ss_quantity BETWEEN 81 AND 100<br>                    THEN &#39;Bucket5&#39;<br>            END AS bucket,<br>            COUNT(*) AS sales_count,<br>            AVG(ss_ext_discount_amt) AS avg_discount_amt,<br>            AVG(ss_net_paid) AS avg_net_paid<br>        FROM snowflake_sample_data.tpcds_sf10tcl.store_sales<br>        GROUP BY 1<br>    )<br>    SELECT<br>        bucket,<br>        CASE<br>            WHEN (<br>                bucket = &#39;Bucket1&#39; AND sales_count &gt; 74129<br>            )<br>            OR (<br>                bucket = &#39;Bucket2&#39; AND sales_count &gt; 122840<br>            )<br>            OR (<br>                bucket = &#39;Bucket3&#39; AND sales_count &gt; 56580<br>            )<br>            OR (<br>                bucket = &#39;Bucket4&#39; AND sales_count &gt; 10097<br>            )<br>            OR (<br>                bucket = &#39;Bucket5&#39; AND sales_count &gt; 165306<br>            )<br>                THEN avg_discount_amt<br>            ELSE avg_net_paid<br>        END AS value<br>    FROM bucket_counts<br>) AS subquery</pre><p>The hand-written query and the generated query are semantically equivalent. Even though they look very different, they produce the same result and could be used interchangeably in any report or dashboard.</p><p>We ran both these queries in 10 TB TPCDS dataset in a popular data warehousing platform. We disabled result-set caching for fair comparison. Each query was run 3 times, and the average is shown here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/690/1*6XPQninnn85qGzm5I1WV3g.png" /></figure><p>Waii generated query is roughly <strong>2.5x</strong> better in response time compared to handwritten query. What is going on?</p><h3>Deciphering the results: Deep Dive on why AI query was 2.5x better</h3><p>Let’s look at the query plan below to understand the execution details: In the handcrafted query, &quot;store_sales&quot; table had to be scanned multiple times over 15 stages. This resulted in substantial I/O and processing costs, ultimately contributing to runtime delays. This approach demanded processing of over <strong>1+ TB</strong> of data across multiple stages.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zc7JckL43nomTnkUkluntQ.png" /></figure><p>In contrast, the AI-generated query pushed &quot;count&quot; and &quot;sum&quot; aggregations to an earlier stage in the query. Consequently, the &quot;store_sales&quot; table was just scanned once. The system processed the data once and propagated the aggregations upstream in the processing layer. The resulting processing load was a mere <strong>158+ GB</strong>, a significant reduction compared to the handwritten query&#39;s <strong>1+ TB</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Vaz9eBYHTeYBKGesUDvaSw.png" /></figure><p>Even though we said earlier that database optimizers should always produce the best plans in reality that’s not the case. Due to the large search space and varying optimization methods across systems, achieving the best plan quickly becomes a challenge for database optimizers. Combining uncorrelated subqueries is a technique that the database we ran on doesn’t use (yet) and as a result it matters how the SQL is actually written, something that practitioners are very aware of. Note that different SQL engines have their own unique query rewriting techniques as integral part of their optimization process.</p><h3>Conclusion:</h3><p>Waii’s text-to-SQL capabilities use an array of AI techniques to understand natural language queries and generate executable SQL queries for databases. It can significantly boost the productivity of business analysts and data engineers. We have also observed that AI generated TPCDS queries often provide a significant improvement over hand-written ones and in this blog we showed an example of why that is the case. There is a lot more work that needs to be done to understand how widely applicable this is and we’re looking forward to spending more time on it.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b0405a41f92a" width="1" height="1" alt=""><hr><p><a href="https://blog.waii.ai/ai-vs-human-query-performance-b0405a41f92a">AI vs Human: Query performance</a> was originally published in <a href="https://blog.waii.ai/">Waii</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>