Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
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 ...
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 ...
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 ...
-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 ...
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 ...
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 ...
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, ...
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 ...
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: ...
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 ...
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 `...
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; ...
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). ...
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 ...
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 ...
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 ...
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 ...
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 ...

1
2 3 4 5
65