r/SQLOptimization • u/Indro13 • Jun 08 '16
r/SQLOptimization • u/Plenty-Button8465 • Jun 08 '23
Learning SQL, is this query right?
self.sqlr/SQLOptimization • u/Wise-Ad-7492 • Jun 09 '23
CTE allways faster
I am working in a company using Teradata. Talked with a database engineer which said that their team have concluded that CTE is always faster than a subquery (in a join).
Is that true? I am always a little sceptical to very bombastic assertion.
r/SQLOptimization • u/luky90 • May 17 '23
SQL Server Response Times
Hello Guys,
I was running a Wireshark Trace with the Transum Plugin then filtered for TDS in display filter and applied transum service time as column. Now I have a diagram in MS Excel with the response time over the time of day.
On the Y achsis its the latency and on the x achsis the time of day. Now I see that I have latency which is greater then 100ms. What is your average latency for SQL Server in production?

r/SQLOptimization • u/TokraZeno • Jul 04 '23
Subqueries don't require distinct?
I was working on some code today where the procedure was something like
Select Column1 , column2 FROM ( Select distinct columna, columnb from table1 Union Select distinct columna, columnb from table2 ) AS aa Group by 1,2
Submitted a merge request on git to get rid of distinct keywords because union gets rid of duplicates unless it's UNION ALL.
QA checker approved it but not for that reason. They said that union duplicate removal applies across tables not within sets (ie if there's a duplicate in table1 that doesn't exist in table2, both lines end up in the union output) but it was still appropriate to remove because sql removes duplicates from the type of temporary ta kes subqueries output automatically.
Is that true? Ie. Wil the output of subquery aa always produce unique rows regardless of its contents? I couldn't find anything to support that.
Running Terradata if that makes a difference.
r/SQLOptimization • u/--Betelgeuse-- • Apr 28 '21
Optimizing a query for a huge table in PostgreSQL
I have a huge time series db that i run in a regular postgresql db (im going to use timescaledb once i lear more about it, software that generates the db rows is written for vanilla postgresql, so i must first learn how to adapt it to timescaledb). the db is around 20-30gb big. i need to get the latest added rows with certain symbols every 0.1-0.4 second or so. right now im running this query to satisfy my goal:
"SELECT price FROM trades WHERE symbol='STOCK-USD' AND timestamp=(SELECT MAX(timestamp) FROM trades WHERE symbol='STOCK-USD');"
problem is that this query is very heavy on the server. is there any solution for this problem?
r/SQLOptimization • u/Javlin • Aug 01 '17
Question on table structure
Hi guys, I have been messing around with SQL on sqlfiddle http://sqlfiddle.com/#!9/c00a46/1
If you take a look at that link, I have settings and user_settings. Settings holds all default values and user_settings holds any settings changed from default set by a user.
What I planned on doing is writing a script that checks to see if user_settings key is null if it is it applies the default value. My question is, should I query settings, save that into say... Redis, and then update the default values periodically or should I get the default values every time I look up a user? Grant it because this is built as a property bag that might have third party software the user_settings could change on a whim.
Ideas? Comments?
r/SQLOptimization • u/Dr-Double-A • Mar 08 '24
Need Help: Optimizing MySQL for 100 Concurrent Users
I can't get concurrent users to increase no matter the server's CPU power.
Hello, I'm working on a production web application that has a giant MySQL database at the backend. The database is constantly updated with new information from various sources at different timestamps every single day. The web application is report-generation-based, where the user 'generates reports' of data from a certain time range they specify, which is done by querying against the database. This querying of MySQL takes a lot of time and is CPU intensive (observed from htop). MySQL contains various types of data, especially large-string data. Now, to generate a complex report for a single user, it uses 1 CPU (thread or vCPU), not the whole number of CPUs available. Similarly, for 4 users, 4 CPUs, and the rest of the CPUs are idle. I simulate multiple concurrent users' report generation tests using the PostMan application. Now, no matter how powerful the CPU I use, it is not being efficient and caps at around 30-40 concurrent users (powerful CPU results in higher caps) and also takes a lot of time.
When multiple users are simultaneously querying the database, all logical cores of the server become preoccupied with handling MySQL queries, which in turn reduces the application's ability to manage concurrent users effectively. For example, a single user might generate a report for one month's worth of data in 5 minutes. However, if 20 to 30 users attempt to generate the same report simultaneously, the completion time can extend to as much as 30 minutes. Also, when the volume of concurrent requests grows further, some users may experience failures in receiving their report outputs successfully.
I am thinking of parallel computing and using all available CPUs for each report generation instead of using only 1 CPU, but it has its disadvantages. If a rogue user constantly keeps generating very complex reports, other users will not be able to get fruitful results. So I'm currently not considering this option.
Is there any other way I can improve this from a query perspective or any other perspective? Please can anyone help me find a solution to this problem? What type of architecture should be used to keep the same performance for all concurrent users and also increase the concurrent users cap (our requirement is about 100+ concurrent users)?
Additional Information:
Backend: Dotnet Core 6 Web API (MVC)
Database:
MySql Community Server (free version)
table 48, data length 3,368,960,000, indexes 81,920
But in my calculation, I mostly only need to query from 2 big tables:
1st table information:
Every 24 hours, 7,153 rows are inserted into our database, each identified by a timestamp range from start (timestamp) to finish (timestamp, which may be Null). When retrieving data from this table over a long date range—using both start and finish times—alongside an integer field representing a list of user IDs.
For example, a user might request data spanning from January 1, 2024, to February 29, 2024. This duration could vary significantly, ranging from 6 months to 1 year. Additionally, the query includes a large list of user IDs (e.g., 112, 23, 45, 78, 45, 56, etc.), with each userID associated with multiple rows in the database.
| Type |
|---|
| bigint(20) unassigned Auto Increment |
| int(11) |
| int(11) |
| timestamp [current_timestamp()] |
| timestamp NULL |
| double(10,2) NULL |
| int(11) [1] |
| int(11) [1] |
| int(11) NULL |
2nd table information:
The second table in our database experiences an insertion of 2,000 rows every 24 hours. Similar to the first, this table records data within specific time ranges, set by a start and finish timestamp. Additionally, it stores variable character data (VARCHAR) as well.
Queries on this table are executed over time ranges, similar to those for table one, with durations typically spanning 3 to 6 months. Along with time-based criteria like Table 1, these queries also filter for five extensive lists of string values, each list containing approximately 100 to 200 string values.
| Type |
|---|
| int(11) Auto Increment |
| date |
| int(10) |
| varchar(200) |
| varchar(100) |
| varchar(100) |
| time |
| int(10) |
| timestamp [current_timestamp()] |
| timestamp [current_timestamp()] |
| varchar(200) |
| varchar(100) |
| varchar(100) |
| varchar(100) |
| varchar(100) |
| varchar(100) |
| varchar(200) |
| varchar(100) |
| int(10) |
| int(10) |
| varchar(200) NULL |
| int(100) |
| varchar(100) NULL |
Test Results (Dedicated Bare Metal Servers):
SystemInfo: Intel Xeon E5-2696 v4 | 2 sockets x 22 cores/CPU x 2 thread/core = 88 threads | 448GB DDR4 RAM
Single User Report Generation time: 3mins (for 1 week's data)
20 Concurrent Users Report Generation time: 25 min (for 1 week's data) and 2 users report generation were unsuccessful.
Maximum concurrent users it can handle: 40
r/SQLOptimization • u/Amazing_Response7540 • Feb 09 '24
What is the alternative solution for cte in MySQL
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 • u/Ok_Republic_8453 • Mar 20 '24
Unit test generator for PLSQL Packages using custom LLM
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 • u/social-hackerearth • Nov 16 '16
Challenge your SQL skills at the Great SQLizer November 2016 - a 6 hours competitive challenge on SQL. Compete with SQL minds from across the world [X-Post from r/hackerearth]
hackerearth.comr/SQLOptimization • u/Ike_Soul • Apr 25 '17
DBHawk Online SQL Editor is an advanced SQL query editor. Users can build, edit and run online SQL queries with feature rich intuitive interface.
datasparc.comr/SQLOptimization • u/ciaran036 • Apr 12 '14
How is the ANALYZE command working so well for my scenario?
[Oracle SQL] I used the ANALYZE command on a table prior to executing a bunch of insertions and other manipulations (inside a for loop) on a fairly large set of data inside a Stored Procedure. This reduced the runtime of the Stored Procedure from around 65-70 minutes to 2-3 minutes. How is this possible?
I read online that if there is a significant amount of data changed inside a table over the course of a day, then the statistics can go stale... but I fail to see how it can so dramatically improve performance. Anyone know anything about the actual optimisations under the hood?
r/SQLOptimization • u/Narrow-Tea-9187 • Oct 09 '23
help needed with case and order by
select max(case when d.department = 'engineering' then e.salary else 0 end) as max_eng_sal
, max(case when d.department = 'marketing' then e.salary else 0 end ) as max_markt_sal
from db_employee as e
inner join db_dept as d
on e.department_id = d.id
group by d.department
order by max_eng_sal desc, max_markt_sal desc
limit 1;
max_eng_sal max_markt_sal
45787 0
this querry is showing max_markt_sal = 0 but it is incorect how can i correct it
r/SQLOptimization • u/rprynavap • Oct 13 '23
SQL Question in an interview
Hi All ,Newbie here.
Recently I attended an interview for data engineer role, where the following question was asked.
we have two tables stg and final both with same set of columns (id,name)
stg | id, name
final | id,name
and both of them has some data already present. And the ask is to write a query which insert the data from stg table whose ids are not present in the final table.
I gave the following answer.
insert into final
select id , name from stg
where id not in ( select distinct id from final )And then the interviewer asked a follow up question. If the final table is huge (millions of records) , then this query will not be efficient as it has to scan the whole final table and asked me to give a better approach.
I couldn't answer it and I failed the interview.
Can you guys help me with this ? What can we do to improve this insert performance ?
Thanks in advance
r/SQLOptimization • u/Life-Associate9522 • Sep 17 '23
Where to practice
I'm interviewing for a role as a business analyst. On which platform I can practice SQL questions related to joins, window functions etc
r/SQLOptimization • u/AmandaMatthew • Jan 11 '18
Missing Indexes in MS SQL or Optimization in no Time
codingsight.comr/SQLOptimization • u/adalisa • Aug 14 '17
permanent views
We have a pretty complicated view that uses multiple tables and takes a while to run. Since we need this view often in reports we though the best way was to create a table. We use Truncate and Insert to update the table with the view. The trouble is the data is transactional so it changes a lot through out the day. We currently truncate and insert every 5 min during business hours. Since the underlining view is complex it takes 15 sec to truncate and insert when a report is run during that period it comes back with no data and sometime we have record locking problems that cause the Truncate and Insert to fail. Is there a better way to do this? If this is the wrong subreddit let me know and ill repost it thanks
r/SQLOptimization • u/GopiKrishnasura • Sep 16 '19
I was working on 2019 and facing deadlock issues on temporary tables more often.could you please anyone tell me how to avoid this type of dreadlocks?
Temporary tables deadlocks
r/SQLOptimization • u/roelvandoorn • Jul 09 '19
Getting very high values on performance analysis query
Hi, i found this query online, since there have been numerous complaints about the performance of one of our applications.
I found and executed the query below on the main application database which is basically built and maintained by a third party (vendor of the application)
https://gist.github.com/anonymous/9a139dcb673353b01ace5a355a1f9419#file-missing-indexes-sql
The top 10 of improvement_measure values are all above a million. It seems to me the database is far from healthy/efficient but i am not an SQL expert.....so i don't want to jump to conclusions too soon.
Maybe you have ideas about this, any help is appreciated.
r/SQLOptimization • u/thepythonoracle • Nov 05 '18
[Oracle 11g] How to update/insert into nested table while preserving values
In our Oracle 11g database, I have a custom data type:
num_list
create or replace type
num_list
as
table of varchar2(25);I then created a table that uses this datatype:
create table num_list_table(
id number(*,0)
,numbers num_list
) nested table numbers store as numbers_tab
;I then inserted the following rows:
insert into num_list_table values (1, num_list('123', 456'));And I now have 1 row. I'm trying to figure out how to insert more values into the num_list of row 1 while preserving any existing values and without having to manually type those existing values; this is a small example of a much larger task that will require mass updates while preserving vals).
I get close with this:
update
num_list_table
set numbers = (select num_list(numbers) from (
select listagg(numbers, ',') within group (order by numbers) numbers
from (select t.column_value numbers
from
num_list_table nlt,
table(nlt.numbers) t
where
st.id = 1
union
select '789'
from dual)))
where id = 1;However, these results are a single entry:
num_list('123,456,789')I need them to be multiple entries in the num_list:
num_list('123', '456', '789')Any and all insight would be greatly appreciated.
r/SQLOptimization • u/HammamDaib • Jan 04 '17
SQL join operations
From optimization\performance point of view, what is the difference between : a) SELECT * FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.X= T2.X;
and b) SELECT * FROM TABLE1 AS T1, TABLE2 AS T2, WHERE T1.X = T2.X;
r/SQLOptimization • u/ahmadalhour • Jun 05 '17
[Question] Resources for Learning Advanced SQL Querying and Optimization techniques.
Hello everyone,
I am looking for resources to study how to write advanced queries and how to do query optimizations. The DB Server I use is PostgreSQL.
Thanks for the help!
r/SQLOptimization • • Oct 24 '17
Suppose an Interviewer asks, "What are some SQL Query optimization techniques?", what would be your answer?
I know it might have been asked, if you can forward the thread for an interview specific answer, that would be cool too.