Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
1,965
questions
0
votes
1
answer
221
views
How to Get a PAGELATCH_SH in sql SERVER tempdb
I am learning memory optimization in SQL server . On the way I learned about PAGELATCH WAIT .But I don't know how it happens and I don't know how to Test it with optimisation .
Can someone give me an ...
- 89
1
vote
1
answer
79
views
PostgreSQL: The actual rows number of Materialized Merge Join Node does not inherit the inner node
I found a plan to perform strangely. The actual rows number of Materialized Merge Join Node does not inherit the inner node, but is consistent with the parent node. The visualization of the plan is ...
- 11
0
votes
1
answer
689
views
Query using VIEW very slow when the same query is fast using WITH
Given this view
CREATE OR REPLACE VIEW public."SellMeta"
AS WITH wh AS (
SELECT "WWHH".id,
"WWHH".url,
"WWHH".name,
...
- 101
0
votes
1
answer
68
views
Help me in optimising the query which takes almost 50 seconds
Please help me optimise the following query running on MySQL 8.0.28 Community Edition.
explain select id,
created_at,
data,
deleted_at,
entity_id,
...
0
votes
0
answers
44
views
Designing a scaleable social photo gallery in Postgres
I’m trying to design a scaleable approach to modeling a social photo gallery and user profiles.
Every profile will have 1 gallery.
My current idea is the following:
Photo table (id, photourl, fKey ...
- 101
1
vote
0
answers
172
views
Optimizing UPDATE multiple tables with specific commit size
I'm trying to optimize my procedure for updating multiple tables with huge amount of data. (millions of rows).
The commit size is set to 50.000 to fit memory capacity.
Here is what I have now. The ...
- 11
0
votes
1
answer
351
views
Postgresql perform sequential scan instead of index scan for indexed column
I'm currently using PostgreSQL 14.0 for windows
My target table has a 900k+ rows and my query is using single column filter (status_id).
The column status_id is indexed via btree (default)
When i ...
0
votes
2
answers
542
views
MariaDB - Mysterious Delay at Each Query
I have 2 tables on MariaDB (10.5.16-MariaDB-log) where I query table A, and then loop over the roughly 4000 results and for each one do a query on table B. (This is inherited code and this will be ...
- 11
0
votes
0
answers
64
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
- 1
1
vote
2
answers
216
views
Lots of "key value" joins causes SQL Server query to exponentially slow down
I am migrating some data that's stored "key value" style, from an attributes table that uses an Object ID and an Attribute Type as a Clustered Index (I've also tried as a nonclustered index):...
- 133
0
votes
1
answer
158
views
Does it make sense to enlarge "shared_buffers" for writing Postgresql?
I'm using Postgresql-11, with a traditional HDD as the physical storing device.
I need to optimize the performance for writing.
I have changed the wal_buffers from -1 to 512MB, that means that ...
- 315
1
vote
1
answer
114
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
- 111
3
votes
2
answers
264
views
Tuning Postgresql for writing of a non-critical DB
The DB is being used to store market quotes, so that losing data of one day is acceptable.
All writing operations are performed at a fixed time of a day, serially, by single connection.
The ...
- 315
0
votes
1
answer
67
views
How to tell if a MySQL query speed is bottlenecked by storage or CPU speed?
Using MySQL 8.0.30 on Rocky Linux 9
For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram ...
- 195
0
votes
0
answers
47
views
Optimize SQL Query
Im having trouble with the performance of this query, especificallly with the case statements. How would yo optimize it?
SELECT a.t,
a.c,
a.id,
a.id_r,
a.s,
a.n,
a.n_s,
a.c,
a.co,
a....
- 1
0
votes
0
answers
110
views
Better way to store stock levels for products?
I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:
The fields of stock are location_id, product_id, and quantity, which is great... until I add a new ...
- 111
1
vote
1
answer
270
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
0
votes
1
answer
2k
views
Difference between with recompile and Option(recompile)
I've read online that with recompile will recompile the whole procedure whereas Option(recompile) will only the recompile a specific statement that it is used on. If a certain statement in the ...
- 11
2
votes
2
answers
433
views
SQL Server - performance for certain partition is slow until restart
On production environment, randomly having strange issue.
There are tables with days as partition (1,2..31). There is only last 5 day data in tables, there are also tables which doesn't have ...
- 41
1
vote
0
answers
29
views
Multipile queries vs a long query with joins in my situation
I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
- 11
2
votes
1
answer
123
views
LEFT JOIN not using index when `column1` < `value` AND `column2` > value
I'm trying to LEFT JOIN a table to another table where a column value is between two column values in the second table, and it is not using an index when doing this seemingly simple query. It has ...
- 131
1
vote
2
answers
705
views
Very slow DROP INDEX, ADD PRIMARY KEY index
This is probably related to another question I had, but not sure.
So my table url_meta is 25 GB including data and indexes, 70 million records.
Data 18.3 GiB
Index 7.3 GiB
Overhead 5.0 ...
- 195
0
votes
1
answer
257
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
- 195
1
vote
1
answer
269
views
effectiveness of creating index for ORDER BY column when used with many WHERE clauses
Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
- 11
0
votes
1
answer
617
views
What is the most efficient way to check whether a BYTEA column contains data, in PostgreSQL?
We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA.
In various ...
- 1
1
vote
1
answer
183
views
Occasionally Need to OPTIMIZE TABLE after table creation -- MariaDB 10.6.11
I transfer tables from one server to another by running the CREATE TABLE statement without any indexes. Then I batch insert all of the data into the table. Then I run an ALTER TABLE to add the indexes....
- 131
2
votes
1
answer
119
views
MYSQL query slow on sorting
I came across an issue, i have the following query:
SELECT
DISTINCT
p.id,
p.title,
p.description
FROM
(
SELECT
`post_id`,
`s_type`
FROM
`search`
WHERE ...
- 21
0
votes
1
answer
63
views
Enforce query plan for queries with expensive predicates/UDFs
I have a question on how to enforce the query optimizer to use a certain execution plan. Lets assume we have two tables, t1 (25k rows) and t2 (100k rows). Also note that all the rows in t1 will find ...
- 3
0
votes
1
answer
89
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
0
votes
1
answer
75
views
my.cnf parameter optimization
Looking for an optimized my.cnf configuration. And more importantly, I am actually looking for the relation between these parameter/system variable and my system's memory. So, if server's memory ...
0
votes
2
answers
362
views
Query planner mis-estimating row counts, uses sub-optimal join strategy
Running Postgres 13.8 on Ubuntu 20, I have a complicated-looking, yet relatively straightforward query. For some reason, the optimizer is wrong about the expected row count of the joins and is using a ...
- 103
0
votes
2
answers
90
views
Why don't Postgresql use a index for a query?
I have a huge table as following:
CREATE TABLE ticks8888 (
tick_time timestamp(6) with time zone NOT NULL,
trade_day date NOT NULL,
other_col FLOAT8 NULL,
CONSTRAINT ticks8888_pkey PRIMARY ...
- 315
0
votes
1
answer
203
views
Oracle Edition-based redefinition(EBR) use case
We have some pretty high-load service in oltp database built using Oracle's procedures, functions, views and etc.
Whenever we want to update our code base, it's usually throws an error like
ORA-...
0
votes
0
answers
187
views
Postgres profiling fetching time
I am storing high-frequency price data in Postgres. The table is indexed and the query correctly uses the index, which I can confirm by EXPLAIN ANALYZE result:
Index Scan using ...
-1
votes
1
answer
463
views
SQL optimization for XML Path [duplicate]
SELECT distinct STUFF(
(SELECT distinct ',' + hg.CODE
FROM SP_HD hg
Inner join Purchase.SP_DT on GOODS_RECEIPT_ID = hg.ID
WHERE PO_DT_ID = d.PO_DT_ID
FOR XML PATH(''), TYPE
)....
- 57
3
votes
2
answers
446
views
Should I test against warm cache or cold cache in sql server?
I'm tuning a query which runs slow the first time and fast on the subsequent runs. I understand that it is because SQL Server is using cold cache (reading from disk) the first time and using warm ...
- 87
0
votes
1
answer
78
views
Testing strategies when tuning a stored procedure
I'm tuning a stored procedure that is used by the first page that loads whenever a user logs into our website and it's very frequently used. When I run the procedure the first time it runs in about 10 ...
- 25
0
votes
1
answer
292
views
Pagination solution increases the logical reads but decreases execution time
I'm working on a solution for pagination and I came across this article by Aaron Bertrand: https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch.
The article suggests a CTE ...
- 439
0
votes
1
answer
105
views
Optimizing query that finds closest unique records
I've been trying out different queries to get the best performance when querying hierarchical data on Postgres 13.6.
Fiddle: https://dbfiddle.uk/060f_h96
Explain: https://explain.dalibo.com/plan/...
0
votes
0
answers
50
views
What's the best/fastest way to divide a database into smaller one (MySQL)
I have a single database with all my clients and I need to divide it into multiple "little" databases each with one client for post-treatment purposes.
(I have a "clients" table ...
8
votes
1
answer
2k
views
Why am I getting a sort when I have an index?
Azure SQL Database.
I have a table from which I need to get the first and most recent rows for Col1 and Col2 based on CreateDate.
CREATE TABLE dbo.table1 (
Id INT IDENTITY(1,1) ...
- 441
1
vote
1
answer
2k
views
When do you need to run Analyze Table on MariaDB?
We have migrated a few large databases from earlier version of 10.1.22 to 10.6.7 - the latest provided by ubuntu repo for 22.04. All was well for a few weeks and then we started seeing very slow ...
- 11
1
vote
1
answer
260
views
Force Index does not work on aws mysql
I have a table sales with a compound index on (user_id, eastern_date, state)
CREATE TABLE sales (
id int not null auto_increment,
user_id int not null,
eastern_date date not null,
...
- 113
0
votes
1
answer
100
views
MySQL add key to a table that is in use
I am running MySQL server v5.7.18 on CentOS 7.
One of the tables is used as operations log for a web API, and has grown to 20 million rows.
Users search this table from a front end app which runs a ...
- 103
3
votes
1
answer
875
views
How to optimize a recursive query in Postgresql?
I have a recursive query that takes too long - 30+ ms where doing the individual queries to extract the same data manually takes < 0.12 ms. So we're talking 250x as long.
I have the following ...
- 177
2
votes
1
answer
964
views
Is it faster to have one stored procedure with multiple if checks before one insert, or to split it out into multiple stored procedures
I am trying to optimize the inserting of some data into our database, but I'm not too sure if this stored procedure would be more efficient if it was split out into multiple stored procedures.
I have ...
- 121
5
votes
2
answers
3k
views
Select * Order by vs Select column Order by performance
I have the following tables
Create Table dbo.product
(
productId varchar(100) primary key,
productStatus varchar(100),
productRegion varchar(100),
productCreated datetime,
productUpdated datetime
)
...
- 439
0
votes
1
answer
164
views
PostgreSQL seq scan instead of primary key
I have a table with emails:
email
- id : numeric, primary key
- id_in_target : text, the ID as stored in Google/MS, indexed
- in_reply_to : nullable, text, references id_in_target in case of a ...
- 103
4
votes
2
answers
600
views
Poorly performing stored procedure using cached data while testing
I have a stored procedure that takes about 15 seconds to run the first time, and the subsequent runs take 1 to 2 seconds. If I wait for an hour and run it again then it takes 15 seconds again.
I'm ...
- 439
1
vote
1
answer
2k
views
Query optimisation to remove nested loop
I have below query which is having high Sub-tree cost. I have created appropriate Indexes for for the query which are getting seek but, still the sub-tree cost in high.
however, I can see nested Loop (...
- 33