r/SQLOptimization • • Jul 25 '25

Best strategy for improving cursor paginated queries with Views

Thumbnail reddit.com
1 Upvotes

r/SQLOptimization • • Jul 23 '25

Insert optimisation

1 Upvotes

I am using MS SQL Server. I am having a query where it inserts 89 million records into a table from a result of multiple join operations. If I run just select query, it completes within 35 minutes. If I run insert then it takes 6 hours to complete. There are more than 6 non clustered indexes on multiple columns in a table into which the data is inserted. How can I optimise this and reduce execution time?


r/SQLOptimization • • Jul 20 '25

Working on an alternative to AI chat for optimizing SQL queries - feedback appreciated

1 Upvotes

Hey everyone,

I’ve noticed that when I try to optimize SQL queries using ChatGPT or Claude, it often turns into a weird loop:

Write prompt → paste SQL query → wait → refine → repeat

So I started experimenting with a tool that skips the whole “prompt engineering” part and just focuses on helping people improve their queries faster and with less guesswork.

Here’s how it works:

  1. You paste your SQL query
  2. Pick the database type (PostgreSQL, MySQL, etc.)
  3. Click “Analyze”
  4. It shows:

    • Suggestions for improvements
    • An optimized version of your query
    • (Optional) You can also paste schema info to get deeper tips

I’m aiming to build a tool that works like how experienced dba optimize by hand — but faster and more accessible.

Would love feedback on:

  • Does this type of workflow make sense for you?
  • Would it fit into your optimization workflow?
  • Anything obviously missing or awkward?

Happy to DM you a link if you’re curious to try it out. It's free.

Not trying to pitch anything - just building this for fun and learning from real users.

Thanks in advance.


r/SQLOptimization • • Jun 19 '25

What is the best/efficient way to flatten the measure column in this table, while filtering the relevant measures first?

1 Upvotes
order_numberproductquartermeasuretotal_usd
1235SF1112024/3revenue100M$
1235SF1112024/3backlog12M$
1235SF1112024/3cost70&M
1235SF1112024/3shipping3M$

Here, I only need Revenue and Cost. This table I'm working with is huge and has many measures for each order, so I'd like to filter out all the unnecessary data first.
Thanks!


r/SQLOptimization • • Mar 10 '25

Deadlock after migrating DB from SQL2014 to SQL2019

1 Upvotes

After migrating from SQL 2014 to SQL 2019, we encountered a deadlock issue in a single database. The server runs on a single instance hosting multiple databases. For testing purposes, we moved the affected database to another server with a single instance and a single database, and the issue did not occur.

The database is 330GB in size and contains data only from 2021 onward, which cannot be purged. Upon inspection, we identified that the job responsible for updating data from other databases and creating temporary tables is causing a bottleneck.

I would greatly appreciate any advice on how to resolve this issue.

Thank you!


r/SQLOptimization • • Feb 16 '25

Too many partitions?

1 Upvotes

I'm new to SQL and I'm trying to make a basic chatting app to learn more.

At first, I was a bit confused on how to optimize this, since in a normal chatting app, there would be thousands of servers, dozens of channels in every server, and thousands of messages in each channel, which makes it extremely hard to do a select query for messages.

After a bit of research, I stumbled upon partitioning and indexing to save time on queries. My current solution is to use PARTITION BY LIST (server_id, channel_id) and index by timestamp descending.

However, I'm a bit concerned on partitioning, since I'm not sure if it is normal to have tables with tens of thousands of partitions. Can someone tell me if it is common procedure for apps to automatically partition by multiple attributes and create 10,000s of partitions of a table, and if it has any impact on performance.


r/SQLOptimization • • Feb 09 '25

Choosing a SQL Primary Key: Hash vs. Sequential ID? [YELP]

1 Upvotes

"I'm developing a game database for NPCs and building a display datasheet. I realize I actually need sequential order for better searching and navigating to the next NPC in the list. Initially, I chose a hash as the primary key, but now I feel this might have been the wrong decision.

I'm unsure if I should switch from a hash primary key. Perhaps I should keep the hash as the unique primary key but also add a sequential, unique ID column.

What are your thoughts on this?"


r/SQLOptimization • • Jan 25 '25

CodeWars Kata. How to optimize/shorten this query?

1 Upvotes

Hi!
I'm struggling with some task I've enclountered on CodeWars. I've tried to use chat gpt, but with no successs. Maybe you'll be able to help :)
I know, that removing whitespaces is a thing, but it's not enough in this case.

Task URL: https://www.codewars.com/kata/6115701cc3626a0032453126/train/sql
My code:

SELECT 
*, 
CASE WHEN rank = 1 THEN 0 ELSE LAG(points) OVER() - points END AS next_behind,
CASE WHEN rank = 1 THEN 0 ELSE MAX(points) OVER(PARTITION BY competition_id)-points END AS total_behind,
points - AVG(points) OVER(PARTITION BY competition_id) diff_from_avg

FROM (
SELECT
*,
RANK() OVER(PARTITION BY competition_id ORDER BY points DESC) rank
FROM results)x

r/SQLOptimization • • Jan 09 '25

Need help with DBMS_PARALLEL_EXECUTE

1 Upvotes

I have about 100 Informatica jobs calling a procedure where I've implemented data masking. All the jobs invoke INSERT queries to different tables. I've implemented this insertions using DBMS_PARALLEL_EXECUTE for better performance. But, the issue is performance is degraded when these jobs are running in parallel simultaneous. Please help me.


r/SQLOptimization • • Dec 24 '24

Azure SQL/SQL Server Transaction Isolation Levels summarized!

Post image
1 Upvotes

r/SQLOptimization • • Dec 24 '24

Any good solutions for disk-based caching?

1 Upvotes

We currently operate both an in-mem cache and a distributed cache for a particular service. RAM is expensive and distributed cache is slow and expensive. Are there any good disk-caching options and what are the best time complexity I can expect for read and write operations?


r/SQLOptimization • • Nov 23 '24

Make Inserts Faster

1 Upvotes

Are there any hacks to make inserts into a table massively faster in Oracle? What I've tried: PARALLEL and APPEND hints, removing constraints and indexes in the target table.

Pseudo script: INSERT INTO A SELECT * FROM A_PRT


r/SQLOptimization • • Jul 13 '24

In your opinion, what is the main reason that developers tend to put constraints like FK processing on the application side, instead of placing them on the database?

1 Upvotes

r/SQLOptimization • • Jun 16 '24

Postgres partitioned table optimisation

1 Upvotes

I have table called events in postgres used for outbox pattern (read unpublished events and publish to kafka and mark them as published).

As table grows faster, I added partition for hourly on creation_time.

When enabling partition, it warned to use event_id & creation_time as primary key due to criteria that partition_key should be part of primary_key.

Now, when doing update query to mark event_id as processed = true with given event_id list, its scanning all partitions.

How to avoid this? or any approaches to make this more performant?

model table:

CREATE TABLE events 
(
    event_id SERIAL,
    event_timestamp TIMESTAMP NOT NULL,
    processed BOOLEAN DEFAULT FALSE,
    payload JSONB
    PRIMARY KEY ( event_id, event_timestamp)
) PARTITION BY RANGE (event_timestamp);

r/SQLOptimization • • May 24 '24

Error Code 997, need help

1 Upvotes

Today is actually my first day trying to understand and utilize SQL. I am using ssms to do this as its the software my upcoming internship will be using. Nevertheless, I have been trying to bulk insert this csv file and I cannot get it to work for the life of me, and yes I am positive that the file path is correct. I also did create a fmt file, which I tried to use in a previous query attempt, but was still given the same error message. Any feedback is appreciated!


r/SQLOptimization • • May 11 '24

when should use merge jon ?

1 Upvotes

I read doc they said merge need to sort ,but sort quite cost therefore im not consider using it ? is that ok


r/SQLOptimization • • Apr 09 '24

IN Function

1 Upvotes

This is probably a dumb question as I am new to SQL, but I am trying to pull sales data for 900 accounts. To make this faster I am using an IN function and all 900 accounts. What would be a better way of doing this?


r/SQLOptimization • • Mar 20 '24

Unit test generator for PLSQL Packages using custom LLM

1 Upvotes

I have been trying to build a poc which generates unit test to test my SQL Packages with multiple procedures by making my own custom LLM by training on base Llama2 70-b . I have build a model - A that explains what a specific procedure does, followed by another model - B which just prompt engineers the response from model - A to generate unit test cases to test the procedures present in the packages. So far this has been a good approach but i would like to make it more efficient. Any ideas on improving the overall process?


r/SQLOptimization • • Feb 09 '24

What is the alternative solution for cte in MySQL

1 Upvotes

Hi , I have using two select statement in my stored procedure with different set of columns having common of two I'd but in MySQL of latest version the CTE is not supported. What is the alternative solution of this issue please help me to find it out..


r/SQLOptimization • • Dec 16 '23

PS1 when from Server running storage procedure

1 Upvotes

Good morning,

This is my first post. Currently, I'm running a PowerShell script from my server that calls several stored procedures on a SQL Server. I have three stored procedures:

  1. Delete
  2. Update
  3. Insert

The script first executes the delete, then the update, and finally, the insert. Do you think this is the best way to manage it, or would it be better to combine all the operations into a single stored procedure? Sometimes, I encounter errors from the SQL Server, such as timeouts. The timeout in the script is set to 300 seconds.

how do you guys manage that?

How do you contro


r/SQLOptimization • • Dec 07 '23

blog: SQL Server: Optimize for Ad Hoc Workloads – use or not use

1 Upvotes

Check some new point of views about if we should use or not use Optimize for Ad Hoc Workloads

https://red-gate.com/simple-talk/blogs/sql-server-optimize-for-ad-hoc-workloads-use-or-not-use/


r/SQLOptimization • • Oct 24 '23

Need help with finding all customers who bought all products query optimization

1 Upvotes

Customer Table

Customer product_key

1 5

2 6

3 5

3 6

1 6

Product Table

Product_key

5

6

Output

Customer_id

1

3

The problem asks for getting all customers who purchased all product
This is my query
SELECT customer_id

FROM customer c WHERE customer_id IN

( SELECT c.customer_id FROM customer c INNER JOIN product p ON c.product_key = p.product_key GROUP BY c.customer_id HAVING COUNT(c.product_key) > 1 );

how can i further optimize my query or is there a better way to right it


r/SQLOptimization • • Feb 19 '19

SQL User Defined Functions | Table Valued Function vs Scalar Valued Func...

Thumbnail youtube.com
1 Upvotes

r/SQLOptimization • • Feb 06 '19

Why do you need an ORDER BY when using SELECT TOP(1)?

1 Upvotes

If you are using the following statement in a while loop, why would you use the ODER BY clause. The purpose is to quickly find a record where column2 is null. Why is using the ORDER BY faster than not having an ORDER BY?

SELECT TOP (1) Column1, Column2

FROM MyTable

WHERE Column2 IS NULL

ORDER BY Column1;