Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,244
questions
0
votes
0
answers
28
views
Performant design for updating/creating multiple rows at high frequency
I have a postgres table with rows retrieved using an asset and timestamp column. In a short time interval (about a second) on hundreds of assets, I need to
retrieve the latest couple rows of each ...
- 101
2
votes
2
answers
34
views
Index for searching in column where the column starts with the provided value
I have a table, where one column contains a substring of an url. It's always the leftmost part of the url without scheme.
id
domain
column1
column2
1
example.com
value1
value2
2
example.org
value3
...
- 121
1
vote
2
answers
43
views
Equality Range Query in MySQL runs very slowly
Table event (id, source_id, start_time), 140k records
Index (source_id asc, start_time desc)
My query:
SELECT * FROM event where source_id in (4, 5, 3, 10)
order by start_time desc limit 100;
Run ...
- 113
0
votes
3
answers
60
views
Window function with subquery results to unexpectedly slow query plan
This is my victim query
SELECT *,
lag(quantity)
over (partition by product_id, size_id, warehouse_id order by size_id, warehouse_id, created_at ASC) -
quantity as orders
FROM ...
- 1
4
votes
1
answer
190
views
Query performance on static, large PostgreSQL table
I tried to have this as detailed as possible. Sorry about the length!
Background
I created the following partitioned table protein_snp_assoc on a PostgreSQL (version 12.13) database:
CREATE TABLE ...
- 43
6
votes
1
answer
413
views
Is it possible to estimate how much time is left for a DROP statement?
I made this query : ALTER TABLE dbname.tablename DROP COLUMN columnname and it's been running for a few hours and I wanted to know how much time would it still be going.
There are approximatively 750m ...
- 155
1
vote
0
answers
17
views
Optimal join for joining facts with scd-type-2 dimension for aggregation/reporting
I have a fact table and an scd-type-2 dimension table. I want to produce sales report by region and year.
I have working solution with a query that joins them for reporting purposes. When I run the ...
- 139
0
votes
1
answer
44
views
Does Postgres Automatically Clean Up Stuck Queries?
Take the following block from my postgres instance:
postgres=> SELECT pid, query_start from pg_stat_activity ORDER BY pid;
pid | query_start
-------+---------------------------...
- 103
0
votes
1
answer
83
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 ...
- 95
3
votes
1
answer
153
views
Stored Procedure Tuning Help
Actual plan: https://www.brentozar.com/pastetheplan/?id=SJviG_y1h
I'm looking for advice on where to start with tuning the above stored procedure. We need it to complete in as near to 1 second as ...
- 31
0
votes
0
answers
10
views
Timing postgres query within a for loop, recording or printing the results out for each iteration [duplicate]
Using the following example data:
drop table if exists tst;
create table tst(x integer);
insert into tst(x)
select x from generate_series(1, 10000000) g(x);
I wanted to create a query and re-run it ...
- 226
-1
votes
1
answer
27
views
Best indexing strategy for composite primary key with frequently queried columns and occasional additional column [closed]
I have a large table with the following composite primary key:
PRIMARY KEY(col1, col2, col3)
col1, col2, col3 are queried all the time.
Some times (maybe 10% of the time) I get queries for col1, col2,...
- 99
0
votes
0
answers
55
views
Improve query performance in SQL Server 2012
I have a table with 150 million rows with a non-clustered index on userId. I have to write a query to filter on userId and return all the rows if the productType is not SIG and return only rows ...
- 319
-1
votes
2
answers
32
views
Is a performance improvement for this query with subselect possible?
I have the following table (simplified non-sense example):
table fruits:
+-----+---------+-------------+
| id | fruit | customer_id |
+-----+---------+-------------+
| 1 | apple | 1 |
...
- 111
1
vote
1
answer
20
views
JSON colunm vs Reference key approach - Postgres
Which is a good database schema design by considering the performance and long-term large records?
Database: Postgres
I have two ways to design the table structure:
Use a JSON column
Go with a ...
- 113
0
votes
1
answer
131
views
Does a select within a where clause affect performance?
I know that we should avoid using functions in the where clause of a query wherever possible since the function will run for every row to return the proper results. However, does the same principle ...
- 121
0
votes
0
answers
43
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,
...
6
votes
1
answer
509
views
How to reduce query size with many repeated UNION subqueries?
I use Postgres 13 and have a table defined with the following DDL:
CREATE TABLE item_codes (
code bytea NOT NULL,
item_id bytea NOT NULL,
time ...
- 185
2
votes
2
answers
49
views
Straightforward inner join is slow despite using indices and ref/eq_ref types
I have a couple moderately-sized MySQL 8.0.30 InnoDB tables:
models - 1,000,000 rows
activations - 10,000,000 rows
A fairly simple INNER JOIN takes about 500ms, which seems like a lot (is it?). This ...
- 131
3
votes
1
answer
513
views
How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table
In the following post, J.D. brings up that I have a poorly performing query.
I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition)
Let's take a ...
- 205
0
votes
0
answers
25
views
PostgreSQL query times out when selecting a smaller amount of data (different query plan)
PostgreSQL 15.2. We have a table like this (simplified):
create table behavior.event_59 (
event_id integer not null primary key,
second_offset integer not null,
-- ... more columns
)
...
- 188
0
votes
0
answers
25
views
Redshift / Postgresql query multiple tables faster than Natural Full Join
I have been given a requirement to query 8(+) different Redshift tables and return some of the fields where email = provided email address.
Current implementation:
(select product_name, email, ...
- 101
1
vote
1
answer
36
views
keeping order rows saved to calculate rank and percentile
we have a large table, around 12M records, everyday at midnight,
we run a query to sort all rows by a certain criteria and save the order in a column.
the reason we do that is because we have a ...
1
vote
2
answers
79
views
MySQL with subquery becomes slow
I have 3 MySQL tables: products, orders and users.
I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
0
votes
0
answers
48
views
How to get SQLs per network consumption?
On Db2/Linux_x86 version 11.5.8.0 we have several Db2 instances.
With iptraf-ng command sysadmin has noticed that one of this Db2 instance has 20-times more output network traffic then others which is ...
- 173
0
votes
2
answers
44
views
MySQL select query slow despite having indexes
Have this rather simple SELECT on just one table:
SELECT id AS price_id
FROM prices
WHERE product_id IS NOT NULL AND product_id > 1
AND search_name_updated IS NULL
LIMIT 1000
For some reason, the ...
- 15
1
vote
1
answer
67
views
Returning array of JSON objects from related fields in PostgreSQL
I have the following schema and some data inserted into the DB.
Schema (PostgreSQL v14)
CREATE TABLE users(
id INT NOT NULL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
...
- 65
1
vote
0
answers
40
views
Oracle query taking a very long time to execute
I am trying to run a couple of reconciliation queries to identify missed deletes from our change data capture tool, but I need it to run relatively fast...
My source and target tables are identical ...
- 67
1
vote
0
answers
69
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
41
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 ...
3
votes
1
answer
105
views
Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column
I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many ...
- 33
0
votes
1
answer
117
views
Bad inner join performance on one-to-many relationship
I have a question regarding the postgres planner that I can't wrap my head around. I am trying to get the planner to look up a project ID by its unique key before running the other side of the query ...
0
votes
2
answers
115
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
1
answer
19
views
getting rows and adding a column to each with the timestamp of the next row, with postgres
I have the following object:
CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
id VARCHAR NOT NULL,
ticker VARCHAR(16) NOT NULL,
interval ...
- 251
0
votes
0
answers
36
views
Materialized view slow with Timescale
I have a table created like this:
-- enable timescale plugin
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
-- create schema
CREATE SCHEMA IF NOT EXISTS {schemaName};
-- create table
CREATE ...
- 251
1
vote
1
answer
96
views
Optimize querying 100 million records- Postgres - indexes
Currently our queries are not performant.
Table: (Item) 100 million rows.
id: uuid
user_id: bigInt
item_type_id: uuid
status: varchar
start: date
end: date
Index on user_id, item_type_id
Query:
...
- 13
0
votes
0
answers
26
views
Hierarchical query to select `children` from `children`
I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1.
id
r
children[]
0
2
{0,2467972595799031808,...
- 263
0
votes
1
answer
73
views
Why query takes ages when I filter by an indexed boolean column?
I have a query that filters on a boolean column that has index. But, the query takes ages to finish. When I don't use this filter, the query returns very quickly.
Here are the explain plans. The first ...
- 107
1
vote
1
answer
29
views
MySQL: Materializing table function take so long
Thanks for checking my question
I encountered a strange issue. the same query in same db return different actual time
here is explain analyze statement
explain analyze
SELECT COUNT(1) AS `cnt`
FROM `...
- 11
0
votes
0
answers
26
views
Which is faster between regex and string length function, in searching string of different length?
I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
- 21
0
votes
0
answers
15
views
Find the I/O cost of the following question?
Estimate the I/O cost of retrieving records from Parts table that contains price less than or equal 1000 rupees. Assume that 15% of tuples satisfy the selection criteria. There are 3300 pages in the ...
- 1
0
votes
1
answer
55
views
Different performance of SQL Server instances on same server
I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). ...
- 11
3
votes
0
answers
108
views
Why does my query take a long time to run, and why does changing the order of the joins make it quicker?
I have a query in sql server that takes close to 3 minutes to run. Interestingly enough, when I change the order of one of the joins the performance improves dramatically and it takes only about 20 ...
- 31
0
votes
1
answer
35
views
making query more efficient when there is a millions of row
My table consists of the following columns:
job_id,
event_type,
timestamp,
client_type,
role_type,
Every job_id has record of which event_type (example login, edit, delete) , what timestamp, which ...
- 1
2
votes
3
answers
313
views
How to query most recent pair of each unique data-pair?
There's a table wherein there currency exchange rates are.
fx_rates
---
id
buy_curr_code
sell_curr_code
rate
inserted_at
A table gets updated with unknown frequency: sometimes daily, sometimes ...
- 33
0
votes
0
answers
27
views
Optimising a recursive SQL query that processes several million records in BQ
I need help optimizing a recursive SQL query in BQ.
I have hierarchical data stored in a table as parent-child relationships, i.e
will be stored as
parent_item_id
child_item_id
1
1
1
2
1
3
3
4
...
- 101
0
votes
1
answer
19
views
Is it slower to pass a composite type representing a table to a function then the individual arguments?
Let's say you want to create a function
CREATE FUNCTION f(a,b,c,x,y,z) FROM t;
Is it any slower to restructure the function such that this works,
CREATE FUNCTION f(t) FROM t;
- 59.5k
0
votes
1
answer
89
views
Postgres return multiple columns from subquery
I have found older posts but I'm curious if there is any better solution for my case.
I have working code but it is really slow. I tried to return two columns in subquery but I have an error (the ...
-1
votes
1
answer
52
views
Most efficient way of search for only spaces strings
I'm consuming data from a view. The view is large (millions of rows). I need to know if a specific varchar column rows are composed completely of spaces (" ") or if it has words on it.
Right ...
- 13
2
votes
0
answers
16
views
set options for replicating slow queries in SSMS
I am investigating a slow ad-hoc query(about 45 seconds) from our application. I am not able to replicate the slowness in ssms however to investigate indexing, reads, etc. I've attempted to use the ...
- 253