Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,254
questions
0
votes
0
answers
3
views
Best Way to search for a string containing a letter or a word in SQL Server
I have a requirement where I need to search a few columns in the table to see if the letter or word passed in as parameter is contained in any of those columns and return the first 100 rows. For ...
- 15
1
vote
2
answers
15
views
Ordering on two columns is less efficient than order on only the first, why?
I have a table that stores transactions for accounts, and I want to select out the latest transaction for a specific account. But the DB (Azure SQL) seems to fetch all transactions for the account, ...
- 437
1
vote
2
answers
58
views
Temp table with less number of rows results in Clustered Index Seek Whereas more rows result in Clustered Index Scan - SQL Server
I have the following table and data:
CREATE TABLE myTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(50),
Column2 VARCHAR(50),
Column3 VARCHAR(50),
Column4 VARCHAR(50),
...
- 393
1
vote
1
answer
52
views
Optimising a SELECT, when filtering one to many fields, using `LIKE "%value%"`
I've got a table of customer orders, on a MariaDB (MySQL) server, using InnoDB, 2GB in size, with 3 million records.
Most SELECT's use an index, and respond reasonably fast, but the admin have a ...
- 135
0
votes
1
answer
34
views
Help optimising PGSQL query
I have a slow Postgres (v15.3) query and I can't seem to get to the bottom of why. I've considered partitioning, but articles suggest that with a 10GB database partitioning isn't required and it seems ...
- 1
0
votes
1
answer
43
views
Why a select query using MD5 so slow
I have a table with thousands of links (some very long) and they store corresponding MD5 for quicker lookup.
Query 1 using MD5
SELECT dest FROM {affiliate_links_link} WHERE lid = :lid
Query 2 using ...
- 13
2
votes
1
answer
65
views
MySQL showing randomly poor performance with nested loop antijoin
I have a very simple query which is randomly performing poorly in production. I have the EXPLAIN ANALYZE outputs for both the good case and the bad case.
MySQL version is 8.0.31.
This is the query:
...
- 121
0
votes
0
answers
33
views
Trying to optimize an aggregrate query for a big table in postgres
I'm trying to retrieve to get a top 3 of players from a big database containing their stats.
To do this i want to sum the kills they got in all of their games and select the 3 that have the most kills....
0
votes
1
answer
48
views
Can anyone please suggest the things which are bad with this SQL query
I saw this query somewhere as a bad example which took their application down.
select oldurl,
newurl,
id,
dateadd
from jos_sh404sef_urls
where newurl <> ""
...
- 11
1
vote
0
answers
39
views
PostgreSQL 14 blocked
I have a fairly serious problem with PostgreSQL, it happens at intervals of a few days that some tables on my PostgreSQL instance crash, or rather this is my feeling.
Linked queries are very slow.
...
- 11
0
votes
1
answer
75
views
SQL to check if a certain time duration occurs within a particular period of the day
I'm working with data that has a start datetime and end datetime column for each event which can be a short period of time
(Few hours) up to a number of days in duration.
I'm trying to establish a way ...
- 13
0
votes
2
answers
54
views
Improving stats for a join to avoid Nested Loops with LIMIT
I have a main fact table t1 with ~6.9m rows, a second dimension table t2 with 1329 rows, and a third table t3 with ~6.9m rows (1-1 with t1, separate because it's produced by a different batch process)....
- 103
0
votes
1
answer
47
views
When using a CTE, why do I need to use SELECT twice in order to filter on values from an auxiliary statement?
WITH aux_results AS (
SELECT id
FROM vendors
)
SELECT c.id
FROM customers AS c
WHERE c.vendor IN (SELECT * FROM aux_results);
Here is a naive and simple example of a CTE. The ...
- 109
1
vote
4
answers
76
views
Can this batched insert be optimized with a #TempTable?
I have this task of importing rows from a source table to a destination table while doing some mapping of columns on the way. The rows are identified by a GUID, and only rows not already present ...
- 165
0
votes
0
answers
16
views
Memory of server never drops data of sql server [duplicate]
I have this problem that occurred in one of the servers our System works in
the SQL server consumption of memory grows and grows until it gets 96% of memory
the system makes a request to the database ...
1
vote
2
answers
57
views
Simple query with two WHERE clauses uses inferior index scan
I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
- 21
0
votes
0
answers
22
views
Architecture of tracking the most recent date a decimal became negative [closed]
I have a column X which is a decimal data type. I would like to track the last date that X became negative. For example, X goes from 10 to -10 on Jan.1 and then to -20 on Jan.2. The last date X ...
- 121
3
votes
3
answers
159
views
Impact of index on a "status" field with one (guaranteed) change
Introduction
I have a PostgreSQL table setup as a queue/event-source.
I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for ...
- 33
12
votes
2
answers
697
views
Unable to execute query and not even able to generate Estimated execution plan
I am working on SQL Server 2019.
I have a table dbo.AllDates where I have all dates from 1990 to 2050. I have another table dbo.ActualExchangeRates where I have actual exchange rates for certain ...
- 123
1
vote
2
answers
91
views
Speeding up Select Query
I have the following query which needs to run up to 40-50 times on some pages.
SELECT lid, dest, rebuild FROM {store_links_link}
WHERE source = :url;
Source column is URLs and the problem is that ...
- 13
1
vote
2
answers
38
views
MySQL - Create a Covering Index where the Sorting Option Columns are Identical to the Select Payload Columns
Something to note up front, I am using Magento 2 so if the query looks a little strange, that's why.
I have the following query and am trying to optimize the indexes for the table;
SELECT `main_table`....
- 13
1
vote
1
answer
70
views
Worst performance multiple left join big tables after restore DB
Problem
Database: PostgreSQL 13.8 (Debian 13.8-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
After migrate from DigitalOcean to strong Dedicated Servers ...
0
votes
1
answer
59
views
SARG-able date conversion
I just came across some SQL Server queries that have the following non-SARG-able predicate in their WHERE clauses:
AND ((CONVERT(DATETIME, SWITCHOFFSET(T.TimeStamp, DATEPART(TZOFFSET, T.TimeStamp AT ...
- 747
1
vote
1
answer
54
views
MySQL explain plan doesn't show key lookup
I have a query that's really slow when I select a lot of columns but greatly speeds up when I only select the columns present in the index the query is using. This makes me believe that the query is ...
- 165
1
vote
0
answers
46
views
Compile time of update query is high on sqlserver 2019 vs sqlserver 2016
A dev team announced performance degradation after migrating their database from SQLServer 2016 to SQLServer 2019. So I started tracing the database using SQL profiler.
It turns out duration, cpu ...
- 11
1
vote
2
answers
64
views
How to interpret the stats of index usage? [closed]
Question: How do we interpret the stats shown in the diagram below? The stats seem to indicate bad index designing, how can it be improved. I realize this a sort of broad question since it depends on ...
- 257
0
votes
2
answers
60
views
What's the most performant condition when dealing with multiple OR?
Let's assume a translation table:
Column
Type
🔑 id
bigint
key
text
locale
varchar(10)
value
text
with a unique index on (key, locale).
I have a bulk endpoint where I want to get multiple key/...
- 103
2
votes
0
answers
56
views
Why is it so fast to create an index on a table?
While optimizing my query in Postgres, I noted that creating an index is always very fast, less than 1 second on a table with millions of rows.
But if there's no index, a query would take a few ...
- 261
3
votes
2
answers
307
views
How to gather Index usage information
Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
- 257
-1
votes
1
answer
25
views
Ignoring Index, default to sorting issue [closed]
SELECT
count(
DISTINCT concat(
country,
col1,
fcol1,
mcol1,
col2,
fcol2,
mcol2,
col3,
...
- 99
0
votes
1
answer
77
views
How to efficiently get absolute value of a time interval in Postgresql?
I have a huge table in Postgresql-11 like following:
CREATE TABLE my_huge_table(
tick_time timestamp(6) with time zone NOT NULL,
brok_time timestamp(6) with time zone,
trade_day date NOT ...
- 261
1
vote
2
answers
38
views
How to optimize this query or these many indexes?
What am I doing wrong here?
I will post the table structure and indexes first, then the query that is slow, and many others that are very fast (instant).
How to optimize that one query to run faster ?
...
- 185
0
votes
1
answer
49
views
PostgreSQL: Unwanted materialize node within nested loop join
I have a peculiar situation where PostgreSQL is choosing to materialize rather than use a particular index I've created for a query where I join a generate_series() against a table storing streamed ...
- 101
4
votes
1
answer
77
views
How to fix performance issue with row_number and multiple schema?
After receiving answer to How can I get a valid rank counter?, I adapted to my own system. But now, I have a performance issue. All of my requests are very fast (less than 0.0005s for most of them), ...
- 203
1
vote
1
answer
59
views
PostgreSQL: Unexpected query behavior (slow) with addtional AND clause
I'm querying two tables via an inner join.
table 1 has a gin index used in where clause (for full text search, ts_vector)
table 2 has a gist index from an extension
Using just these two indexes in a ...
- 213
2
votes
2
answers
1k
views
How can I optimize the stored procedure and reduce table scans?
I have a stored procedure which identifies any changes in candidate information from the last SSIS job run. Then it proceeds to save the changes in a sync table.
CREATE PROCEDURE [dbo].[...
- 171
0
votes
2
answers
54
views
Improving performance of large SQL table for visitors stats
I have a table for visitor stats as follows:
CREATE TABLE `stats` (
`key` int(11) NOT NULL AUTO_INCREMENT,
`u_id` int(11) NOT NULL,
`app` varchar(20) NOT NULL,
`type` varchar(20) DEFAULT '',
...
- 113
-1
votes
2
answers
45
views
Are there any differences relating to query performance between >= and <= vs. Between clause?
Are there any performance-related differences between those two queries?
SELECT * FROM `v1` WHERE `siteId`=1008 AND `date`>='2022-10-01' and `date`<='2023-02-01';
SELECT * FROM `v1` WHERE `...
- 218
0
votes
1
answer
120
views
Can this slow sysbench mysql result be "normal" for consumer hardware?
I have 4 different MySQL 8.0.30 servers on Rocky Linux 9, in one I replaced NVME SSD hoping it will perform better.
I made an image with "Macrium Reflect" software and copied exact same ...
- 185
-2
votes
2
answers
89
views
Why the order of indexed columns in ORDER BY affect performance?
I'm trying to improve performance of the following query which takes a 1+ minutes to execute:
SELECT *
FROM test
WHERE ( created_at < '2023-3-31 06:10:20.871' )
AND ( ( id > '2a95048f' ...
- 101
0
votes
4
answers
260
views
Performance issues after upgrading from MySQL 5.7 to MySQL 8.0
Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT ...
0
votes
0
answers
15
views
Best way to model load test for performace requirements of database
For instance, imagine this requirement for a certain query in the database:
The database needs to handle L of those queries a year.
a% of the queries need to have less than 30ms
What is the best way ...
1
vote
1
answer
71
views
Query with smaller subset to scan taking exponentially longer?
I have a large table, extrinsics, almost 90GB in size, containing data from multiple blockchains.
I have a query which takes almost 17 minutes to run:
select * from public.extrinsics
where chain_id = ...
- 125
0
votes
0
answers
37
views
What are the time complexity of MongoDB operations? (Or where I can find it)
I'm newbie in MongoDB, I'm deciding how to make the schema. And I found that people usually don't separate models in mongo, join everything in the same record. So in order to decide if I will or not ...
- 101
2
votes
2
answers
127
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
...
- 123
1
vote
3
answers
76
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 ...
- 133
0
votes
4
answers
121
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
265
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
436
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
1
answer
172
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 ...
- 149