Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
2,233
questions
0
votes
0answers
10 views
Query performance of index scans slower than parallel seq scan on Postgres
I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora.
Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
1
vote
1answer
25 views
MySQL order of execution
I am running a very long query described below.
It fetches the next needed action for each account on an automation system.
I.e.
SELECT Account.id,
(IFNULL(**Should send message query**,
...
0
votes
0answers
28 views
Select query taking too much time in postgresql
I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU):
create table stat_data (
name varchar NOT NULL,
the_date date NOT NULL,
uopen numeric,
uhigh ...
1
vote
0answers
26 views
Can oscillating number of fragment decrease FTS query performance?
I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly.
Reading ...
1
vote
1answer
26 views
Predict how much hard drive space a query might cost?
Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My ...
0
votes
0answers
18 views
How can i remove filesort for this subqueires?
This is the Query:
SELECT
A.*
FROM
messages_thread_user mtu,
LATERAL (SELECT
m.*, u.userName
FROM
messages m
JOIN
...
1
vote
1answer
20 views
Why does performing a query on a second environment, it takes forever to run, and scans hundreds of millions of rows?
I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run.
I ...
0
votes
1answer
53 views
Is IF ELSE better in this stored procedure than a Parameter? [duplicate]
I found a stored procedure in our sql server 2016 db
Create PROCEDURE XXX
@complicatedQuery bit
AS
BEGIN
SELECT some fields FROM
Table A join Table B on something
JOIN Giant Table C ...
0
votes
1answer
48 views
Part of parallel execution plan has serial portion
I have a big linq to sql query which runs in parallel mode.
It is returning results in 8 seconds and it is pretty good.
I noticed in the execution plan that the extreme right part(from where execution ...
0
votes
0answers
12 views
How does the MongoDB $filter (aggregation) stage work internally?
I was asked a question by a colleague today whether the MongoDB $filter aggregation stage is implemented efficiently.
The aggregation stage in question:
$project: {
absences: {
$filter: {...
2
votes
2answers
29 views
How to find exact string from a sentence
I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
0
votes
2answers
32 views
Why is this query sometimes slow, and what can I do to make it faster?
I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database.
...
0
votes
0answers
27 views
How can I obtain a list of our applications' SQL statements that do not make use of indexes?
We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement ...
0
votes
1answer
48 views
Why the difference in query time for these two queries?
I have a MySQL 5.7 database with a large table that has about 70,000,000 rows. There are 4 columns: Id, Time, Value, Monitor_Id. Id is the primary key, Time is indexed and Monitor_Id is a foreign key. ...
0
votes
2answers
43 views
MySql GROUP BY, ORDER BY LIMIT query taking more than 5000 seconds
I have a simple table. In which one composite index is created. We have a job which hits select statement on slave server and which takes around 5000 seconds to execute query.
Please check table and ...
1
vote
1answer
63 views
Why is parallelism not good in this query?
I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias:
Finding Key Lookups inside the Plan Cache
and from there I borrowed the ...
0
votes
1answer
41 views
`cube` query performance issues with ordering
I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the
But ...
0
votes
1answer
28 views
How to increase timing performace of a SQL query
I'm using postgresql 9.6 on the 16GB Ubuntu-PC with 4 cores & SSD with following params:
max_connections = 200
shared_buffers = 6GB
work_mem = 256MB ...
1
vote
1answer
61 views
Slow SQL query from app, fast from SQL Studio
I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS.
Here is the query :
SELECT T0.pk
FROM products T0
WHERE (( ( T0.pk IN (...
0
votes
1answer
30 views
Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql
I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query:
SELECT *
FROM category_member
ORDER BY (p::float8/q) DESC
LIMIT 10;
I am ordering ...
1
vote
1answer
50 views
Sargability question - order of variables
I have a stored procedure that has a few lines:
AND ( @StartDate IS NULL OR @StartDate <= r.ReferralDate )
AND ( @EndDate IS NULL OR @EndDate >= r.ReferralDate )
Should it be re-written as:
...
0
votes
1answer
39 views
Postgres index not being used
Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0
votes
1answer
26 views
Three WHERE slow query down
I have a table adjacency, which stores information about connected lines and their angle to each other is build by
CREATE TABLE public.adjacency
(
currentedge integer,
currentgeom geometry(...
0
votes
0answers
14 views
MySQL view extremely slow despite indexes
I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading.
The view is:
https://pastebin.com/ZgB7rr91
channel_data_60 table contains about 1,200,000 records:
...
0
votes
1answer
24 views
Query to filter a table based on a comparison applied to the same table?
Case & Problem:
Consider a corporate building with some rooms and departments that need authorization to enter.
Employees have to scan their badges to be authorized.
If the employee is inside (...
0
votes
1answer
39 views
SELECT optimization in Postgres 10
I want to select last unique rows based on time, so:
SELECT DISTINCT ON (track) *
FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
6
votes
3answers
199 views
Counting occurrence of words in table is slow
Consider these simplified tables:
CREATE TABLE dbo.words
(
id bigint NOT NULL IDENTITY (1, 1),
word varchar(32) NOT NULL,
hits int NULL
)
CREATE TABLE dbo.items
(
id bigint NOT NULL ...
1
vote
1answer
74 views
How to improve simple update statement causing blocking issues, inneficient index scan in query plan
Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, ...
3
votes
2answers
103 views
Index Seek scanning whole table dependent on parameter value
I have a query:
SELECT Id,
ColumnA,
ColumnB
FROM MyTable
WHERE ColumnA = @varA OR
ColumnB = @varB
The table is defined as
CREATE TABLE MyTable
(
Id INT IDENTITY(...
2
votes
2answers
33 views
Postgres query very slow when using index
So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records.
Here's the query ...
3
votes
1answer
65 views
Reused Nested Decision Logic - CTE vs Copying Code
Question
I have a query which outputs one column, created through a series of CASE statements. That same column is used as part of a CASE logic for a 2nd column in the same SELECT statement.
If I ...
0
votes
1answer
47 views
Cannot find slow (frozen) running query in Query Store [closed]
I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes ...
1
vote
1answer
52 views
How to make delete duplicates faster?
On a mysql table with about 1.7M rows, I tried to delete duplicates posts:
delete a FROM comment a
INNER JOIN comment a2
WHERE a.id < a2.id
AND a.body = a2.body;
The result was:
...
8
votes
2answers
135 views
Very similar queries, vastly different performance
I have two very similar queries
First query:
SELECT count(*)
FROM Audits a
JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
...
2
votes
2answers
114 views
How to optimize a MySQL joining to itself?
I need to optimize a MySQL (5.7.12) query. I've been reading the docs about optimization in MySQL docs but It's been hard for me to comprehend it. I have two tables:
vehicles
CREATE TABLE `vehicles` ...
0
votes
2answers
20 views
MySQL compare values of two large tables
On MySQL 5.7 I want to get the records from table 1 that are not present in table 2.
I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why.
SELECT * FROM t1 ...
0
votes
1answer
39 views
Why does this MySQL query take 25+ seconds?
The query is:
SELECT T.ID, T.A, T.T
FROM table1 AS T
WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much
Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T
I have a BTREE index ...
0
votes
1answer
23 views
PostgreSQL 9.6: Update on a single row table takes an unreasonable amount of time
I've got a table called source which has a primary key on its id and a “Unique Constraint” on the column name.
This is the way the table looks:
id int4
name ...
0
votes
0answers
48 views
How to calculate count of rows of Memory-Optimized Table?
Performance is important for me so I don't use select count(*) from MyTable.
I use the following query to calculate rows count:
SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [...
2
votes
2answers
84 views
Simple query returning < 10000 rows taking 3-4 seconds
I'm having some trouble diagnosing this very simple query which seems quite slow to me, any help would be much appreciated.
The table of relevance for the query at issue is defined as follows:
...
0
votes
2answers
24 views
large number of tables and postgres query performace
I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
2
votes
0answers
60 views
What's the more efficient way of selecting with multi left joins? [closed]
I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.
All tables have a id column as the primary key. student.id is varchar(64) (similar ...
1
vote
2answers
54 views
Postgres: why did this trigram index slow down regexp queries?
I have a TEXT keyvalues column in Postgres:
select * from test5 limit 5;
id | keyvalues
----+------------------------------------------------------
1 | ^ first 1 | second 3
...
1
vote
1answer
38 views
Random selection with a “complex” subset structure
This is my first question in the whole StackExchange world.
I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question.
In my ...
0
votes
2answers
131 views
same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)
I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
1
vote
1answer
33 views
30 days rolling sum as base for fee calculation. Faster solution
I need to calculate PayPal fees or an online shop to show an overview table with the fees per day. I came up with a solution playing this over several views, but it's dead slow. I takes more than a ...
3
votes
1answer
66 views
Hash Match Spills and Key Lookup causing major performance issue
I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I ...
0
votes
1answer
33 views
Very slow JOIN + ORDER BY query only in dev environment
I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query.
This query is taking 75 ...
1
vote
0answers
57 views
How does mysql optimizer works?
I have two tables t_core_blueprints,t_ci.
CREATE TABLE `t_core_blueprints` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`last_modified_on` ...
0
votes
1answer
24 views
MSSQL. Tree structure fetching with conditions
I need to make a tree structure
Table:
create table Dirs
(
Id uniqueidentifier primary key not null default (newid()),
Name nvarchar(30) not null,
...
