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
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 ...
JoeCharles's user avatar
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, ...
viblo's user avatar
  • 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), ...
lifeisajourney's user avatar
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 ...
Craig Francis's user avatar
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 ...
Nebulous's user avatar
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 ...
JM John's user avatar
  • 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: ...
Agniva De Sarker's user avatar
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....
Bijleveldje's user avatar
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 <> "" ...
fuishia's user avatar
  • 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. ...
Leone Miceli's user avatar
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 ...
DynamoRanger's user avatar
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)....
MZS's user avatar
  • 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 ...
Anthony O's user avatar
  • 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 ...
Alias_Knagg's user avatar
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 ...
xxzozoxx1's user avatar
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 ...
Rio Fajar's user avatar
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 ...
Paradox's user avatar
  • 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 ...
WesAtWork's user avatar
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 ...
saadkaul's user avatar
  • 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 ...
JM John's user avatar
  • 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`....
Marisa's user avatar
  • 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 ...
M. Garbarczyk's user avatar
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 ...
GaTechThomas's user avatar
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 ...
Archmede's user avatar
  • 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 ...
Peter Mobiel's user avatar
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 ...
nam's user avatar
  • 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/...
Dimitrios K.'s user avatar
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 ...
Leon's user avatar
  • 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 ...
nam's user avatar
  • 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, ...
mahmed nabil's user avatar
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 ...
Leon's user avatar
  • 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 ? ...
adrianTNT's user avatar
  • 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 ...
shangxiao's user avatar
  • 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), ...
Elikill58's user avatar
  • 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 ...
beginner_'s user avatar
  • 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].[...
Rishav Ghosh's user avatar
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 '', ...
Guesser's user avatar
  • 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 `...
Niyaz's user avatar
  • 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 ...
adrianTNT's user avatar
  • 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' ...
CKT's user avatar
  • 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 ...
Programming Caveman's user avatar
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 ...
Vitor Figueredo Marques's user avatar
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 = ...
Deekor's user avatar
  • 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 ...
titusfx's user avatar
  • 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 ...
Zyberzero's user avatar
  • 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 ...
shang12's user avatar
  • 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 ...
glmn's user avatar
  • 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 ...
jommi's user avatar
  • 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 ...
lyeaf's user avatar
  • 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 ...
Kashyap's user avatar
  • 149

1
2 3 4 5
66