Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
22 questions from the last 365 days
4
votes
3
answers
1k
views
Is it better to add conditional logic to a heavily used stored procedure or create a separate one for a new use case?
We have a core stored procedure that's a critical part of our system and gets called thousands of times every hour to handle important operations. Now, there's a new application being introduced that ...
- 821
2
votes
2
answers
166
views
Evaluating File Data from FILESTREAM-Enabled vs. Non-FILESTREAM-Enabled Columns in a Single Table
Originally, I was working with .NET 9 and a Blazor web app for admin and a Flutter app for external end-user, but for this context, I constrained it to SQL Server 2017, to evaluate it first internally....
- 205
0
votes
0
answers
45
views
How can I optimize this query for large datasets, especially when filtering by hundreds of device IDs? [duplicate]
Actually, here the issue is not with like operator. For each deviceid. I have 30 lack records like that I'm sending 100 device IDs. so, this is causing issue for slowness, and it is well indexed. can ...
- 11
2
votes
0
answers
29
views
How can I use multicolumn indexes to efficiently find the most recent record for EACH unique id (Postgres)? [duplicate]
I have a large temporal table (>20 million rows) that contains data over time on about 15k unique objects. I can't figure out how to efficiently query "get the most recent record for each ...
- 21
0
votes
1
answer
70
views
What happens if I insert a row in the middle of an sqlite table `WITHOUT ROWID`?
WITHOUT ROWID is an sqlite optimization. Doc states, it is making the primary key of the table to a clustered index.
As far I know, clustered index means that the physical ordering of the rows will ...
- 2,137
1
vote
0
answers
118
views
Why is my query significantly slower from MariaDB 11.0.0 compared to version 10.x?
I have a query that works fine on MariaDB 10.4 and later versions of 10.x, but after upgrading to MariaDB 11.4, it has become significantly slower. After some testing, I found that this change ...
- 11
2
votes
0
answers
81
views
How to make PostgreSQL take advantage of SSD RAID?
I'm using a PG13 server as a feature DB.
There is no else job on the server but this PG instance and a GPU based machine learning process.
There is no online transaction, and the data lossing or ...
- 413
1
vote
1
answer
190
views
Why all Postgresql workers are waiting for a single process?
We are using PostgreSql-13 as our core server, and encountered a performance bottleneck.
The hardware includes 2 CPUs(AMD EPYC9754 with 128 core 256 threads of each), 128GB memory, hardware RAID0 ...
- 413
4
votes
1
answer
472
views
Index scan when more than 35 correlated subqueries are used with default cardinality estimation
Recently, we updated the compatibility level of our SQL Server from 2012 to 2016, but after updating the compatibility level we ran into performance issues when a lot of sub queries are used. ...
- 143
0
votes
1
answer
63
views
Optimizing a count of how many times a tag has been used to tag something
I have a MySQL setup with configs that can be tagged with any number of tags. I'm attempting to optimize a query that returns the total number of times a tag has been used to tag a config.
tags
id, ...
- 1
0
votes
0
answers
61
views
Repeated CTEs vs Recursive CTE
I'd like to ask which of the following two functions is better for retrieving a hierarchical list of child items from a table. Here are the two function definitions:
Version 1 (Recursive CTE):
CREATE ...
- 189
0
votes
1
answer
136
views
How to efficiently implement vertical partitioning in PostgreSQL for a large table?
I have a large table (table_A) in PostgreSQL with the following characteristics:
51 columns
500 million records
Approximate size: 200GB
I frequently perform CRUD operations, but these operations are ...
2
votes
1
answer
401
views
Are there two levels of constant folding?
Are there two levels of constant folding, one in the conversion tree and one in the simplification phase?
If I run the following query
SELECT
P.[ProductID]
FROM
Production.Product AS P
WHERE
...
- 167
1
vote
3
answers
2k
views
How to Ensure a Query Runs in Parallel in PostgreSQL?
I have a PostgreSQL 16 database setup with the following configuration:
-- DB Version: 16
-- OS Type: linux
-- DB Type: oltp
-- Total Memory (RAM): 64 GB
-- CPUs num: 32
-- Connections num: 200
-- ...
0
votes
1
answer
94
views
How to speed up this query with multiple indexes?
How can I speed up this below query ?
I have a ~large table url_meta 90 million records, 38 GB data, 6 GB index
Main unique id of each row is url_hash (a md5 truncated to 16 chars)
Then I created a ...
- 206