Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
601
questions with no upvoted or accepted answers
8
votes
1
answer
140
views
Why does only one version of these procedures get blocked under moderate load?
The table:
CREATE TABLE [dbo].[Session] (
[SessionId] UNIQUEIDENTIFIER NOT NULL,
[CID] INT NULL,
[DEST] VARCHAR (50) NOT NULL,
[...
- 316
8
votes
0
answers
2k
views
How to measure the memory usage of a Mongodb aggregation?
I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
5
votes
0
answers
447
views
Postgresql row-level security generates different query plan from manually inserted WHERE clause
(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
- 163
5
votes
1
answer
7k
views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
- 61
4
votes
0
answers
1k
views
PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice
This is the story if a nightmare... I have this table:
Table "subscriptions"
Column | Type | Collation | Nullable | ...
- 553
4
votes
1
answer
2k
views
Slow postgres query that uses quick sort instead of hash-aggregate
I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic.
I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
- 1,074
4
votes
1
answer
2k
views
Why does postgres choose to sort instead of scanning the index?
I have the following table:
# \d service
Table "public.service"
Column | Type | Modifiers
-------------+----------+-----------
customer_id | integer | not null
date | date ...
user109810
4
votes
1
answer
730
views
Optimizing intersect query in MySQL
I'm facing a performances problem in our MariaDB server.
We have a table linking users to pages, a user belongs to 1+ pages.
I have a page that have users and I want to get the 10 best pages based ...
- 141
4
votes
2
answers
599
views
poor and/or unreliable InnoDB performance
I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped.
I have a database containing four InnoDB tables. The largest ...
- 273
3
votes
0
answers
198
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
3
votes
0
answers
153
views
Slower delete performance in MySQL8 compared to MySQL5.7
I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7
Apologies in advance if this is a little fluffy at this stage and I've ...
3
votes
0
answers
230
views
Execution Plan changes between good and bad query plans (Azure SQL Server)
I've done my best to follow Why is my query suddenly slower than it was yesterday?
The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I ...
- 175
3
votes
0
answers
72
views
Is there any way to optimize this query (generated by Drupal)?
I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases.
The query:
SELECT
node.nid,
node.created,
node....
- 1,182
3
votes
2
answers
1k
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
- 131
3
votes
0
answers
487
views
MariaDB - Query to find unused indexes
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
- 69
3
votes
0
answers
785
views
Why is PostgreSQL not using composite Index on JSONB Fields?
Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:
SELECT id from table where (j1->>'f1')::int = 1 AND (...
- 131
3
votes
1
answer
2k
views
What are the methods to paginate a table 100 rows at a time?
Let's say we have a table with a large number of records. Now to show all the data using pagination in a basic framework using a MySQL query, we can use limits to get a subset of rows:
SELECT * FROM ...
- 131
3
votes
0
answers
588
views
How to optimize this recursive postgresql query?
I've been searching a lot on this topic but I'm stuck at optimizing a query.
I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
- 131
3
votes
0
answers
1k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 246
3
votes
0
answers
81
views
What is a cost of search for records in a table with an equality predicate on a non key attribute A and a static hash organization?
To speed up the search for records in a table with an equality predicate on a non key attribute A and selectivity factor fs, is preferable:
A sequential organization on a key attribute ?
A static ...
3
votes
1
answer
2k
views
SELECT Query is slow when run remotely in MySQL
When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then ...
- 31
3
votes
0
answers
98
views
Understanding Database Query Cost Optimzation
I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
- 39
3
votes
0
answers
110
views
Performance differs on same query against two (almost) identical tables?
I'm running these two queries in SQL Server 2012:
SELECT tpua.Field1,
CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2,
CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
- 545
3
votes
0
answers
83
views
Tagged questions
I have similar to StackExchange network that utilises tagged questions.
In my web application Users also can find questions by tags. The query below returns a set of tagged questions:
SELECT ...
- 151
3
votes
0
answers
1k
views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
- 274
3
votes
4
answers
282
views
Optimizing slow query
For a few days now, i'm trying to optimize this query.
But still I haven't found the right solution in how to fix this.
The contents table contains about 20 million records. As the connect table ...
- 31
3
votes
1
answer
5k
views
How can we improve this PIVOT operation? ( SQL Server 2008 R2 )
We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.
select top 100 *
from (
select codCliente
,...
- 7,344
3
votes
1
answer
307
views
query optimization of a left join
I have the following query written into the slow log (mysql-slow.log).
I don't have much idea how to optimize this query. I already have indexes for:
mode,
f.user_id... etc
but doesn't seem to be ...
- 143
3
votes
1
answer
321
views
GROUP BY very slow when returning many rows
I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
- 325
3
votes
2
answers
248
views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
- 2,169
3
votes
1
answer
1k
views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
- 425
3
votes
1
answer
136
views
How can I improve my query performance?
I'm building a statistic application based on Django & PostgreSQL with focus on performance and maintainability.
The problem is, that data is distributed among multiple relatively large tables.
...
- 475
3
votes
2
answers
145
views
Optmizing ORDER BY
I'm new to MySQL and have written the following query (see below).
PROBLEM:
The query returns accurate data but takes about 40 seconds to execute.
However, When I remove the ORDER BY part, it takes ...
- 131
2
votes
1
answer
83
views
random_page_cost and memoize plan relation
Postgres 15.2. I have the following simple query:
SELECT mark.last_modified FROM mark
INNER JOIN element ON mark.element_id = element.id
INNER JOIN model ON element.model_id = model.id
WHERE ...
- 135
2
votes
1
answer
95
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
2
votes
0
answers
71
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 ...
- 285
2
votes
0
answers
17
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 ...
- 255
2
votes
1
answer
101
views
PostgreSQL imbalanced index query optimization
I am having performance issues with my query because the index is heavily imbalanced. I have a column family_id on a table. The table has about 150 million records. This family_id column identifies ...
- 121
2
votes
0
answers
143
views
deadlock on too many requests and DoS occurred, Why?
I ran into a strange monitoring DBA error "Deadlock" when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.)
Why did this occur?
I add the Deadlock XML Too:
...
- 121
2
votes
1
answer
204
views
Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?
I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet.
The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
- 809
2
votes
0
answers
276
views
Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX
Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
- 433
2
votes
0
answers
188
views
Why does SQL Server 2019 stored proc get Adaptive Join on Development Server, but not on Production Server?
I have a stored proc with one select statement that gets a plan w/ Adaptive Joins on my development server, but not on my production server. What could explain that? I'm not much interested in ...
2
votes
2
answers
477
views
Slow query for multiple data at same timestamp
Hi everyone,
I'm currently facing a problem to query efficiently my database to retrieve all rows at same timestamp.
I will explain,
I have two table of data using TimescaleDb Hypertable with ...
- 31
2
votes
1
answer
255
views
How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)
I think I have quite simple question, but I couldn't find the answer anywhere.
I'm using MySQL and I have a simple table:
id
timestamp
groupId
costA
costB
costC
...
1
2022-02-01 19:45
1
5,13
3,20
30,...
- 21
2
votes
0
answers
184
views
How to determine the Tipping Point of which Physical Join Operator gets used to process a query?
I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
- 34k
2
votes
0
answers
689
views
SQL Server - Self join on table is very slow
Working with Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)
We have a single table, which is planned to contain about 1.5 - 2, maybe max 3 million records at a time. Records will ...
- 133
2
votes
0
answers
33
views
subquery for history average is slower then expected
I have a MySQL table which contains historic product values. A new record per product is updates every minute. The timestamp field is an INTEGER field with a unix timestamp. There is an index on the ...
- 121
2
votes
1
answer
1k
views
information_schema.columns slow performance
we have system that quite a lot and often asks database about:
SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, ...
- 421
2
votes
1
answer
823
views
Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?
I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
- 21
2
votes
2
answers
858
views
Optimising MySQL count() with INNER JOIN (550k rows)
I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
- 121