Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
2,273
questions
1
vote
3answers
96 views
Slow query in production, bad execution plan or bad index?
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
...
0
votes
1answer
41 views
How to make PG query faster even when it is using indices
I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question ...
1
vote
1answer
23 views
Optimize postgresql query with multiple subqueries
I'm trying to optimize this query taking between 3-4 minutes (it's running on hdd right now and it will go as low as 10s on sdd when going to production I guess but it's still very high).
Table ...
0
votes
1answer
20 views
usage percentage of Mongodb internal cache
I've ran two performance test on a MongoDB server with identical environment/settings AFAIK.
I found that the throughput was 10% apart.
When I inspected the mongostat logs, I found that Mongodb in ...
1
vote
2answers
48 views
Unable to retrieve query plan of running query because of blocking
I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes. Probably some plan caching issue. But the problem is ...
2
votes
0answers
36 views
Performance test against identical tables (including data) with very different results [closed]
I created 2 identical schemas and a jmeter performance test to highlight the differences between an indexed and non-indexed table. All tables have clustered indexes from their primary keys.
Before ...
0
votes
0answers
13 views
Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance
I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different ...
0
votes
0answers
15 views
Is querying by a PRIMARY KEY in MySQL faster than other queries?
I have two queries that return on a dataset of 200,000 records with apparently the same performance. The first looks up a primary key, and the second a varchar(32) with no special constraints.
Am I ...
0
votes
1answer
19 views
Optimize query between two MySql tables
I currently have a query that works on two tables of expense and income. This is an example of what the structure of both tables looks like since they have the same columns:
| id| date | ...
3
votes
0answers
30 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 ...
1
vote
1answer
38 views
MY query getting slow when use sub query and exist [closed]
CREATE TABLE `temp_numbers` (
`win_digit` varchar(10) NOT NULL,
`amount` int(11) NOT NULL,
`session` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `...
0
votes
2answers
57 views
Table design and query Optimization: Query to find the suitable work from a list of work items
I have table with jsonb column as below
CREATE TABLE
work
(
id SERIAL NOT NULL,
work_data JSONB
);
sample data is as follows:
100 {"work_id": [7245, 3991, 3358, 1028]}
...
1
vote
1answer
41 views
Postgres query plan shows sequential scan instead of a index scan with on the fly data
My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values.
...
2
votes
0answers
49 views
Optimizing a paginated query with various filters and dynamic sorting
Introduction
Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and ...
0
votes
1answer
88 views
SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014
I have 2 databases that contain nearly identical data.
The first has compatibility level 100 and is restored on a sql2012 instance.
The second has compatibility level 110 and is restored on a sql2014 ...
1
vote
1answer
103 views
What could lead to excesive PAGEIOLATCH_EX
I'm having some delays when insert into a table. The funny thing is that it doesn't always happen.
Basically it's part of a load test that I created to compare some new features. I run the store ...
0
votes
0answers
13 views
Comparing Across Tables to Find Most Recently Modified Row
all. Ive been working with some Sales Force data to solve some data synchronization problems we have between two instances of SFDC.
Quick Background
We have two instances of SFDC (SalesForce) that ...
0
votes
0answers
25 views
MySQL Explain. How to improve type ref extra NULL
I'm struggling to find the right index to the query shown below (I have improved it from minutes to 20 seconds but it needs to be faster):
explain extended SELECT cta.NRO_CUENTA
, (IF(aut.Reversada=...
1
vote
2answers
45 views
MySQL 5.6 users index instead of range type select on a big table and simple query
We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
1
vote
1answer
60 views
Is there any reasonable way to determine what executed a query?
I have an S3 SQL Server database in Azure, and I'm having an issue with a specific Entity Framework generated query and how often it is being run.
As the numbers below show, I'm getting 160K+ ...
1
vote
1answer
33 views
T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER
I have a table with below structure :
FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int)
based on this table I wrote this query
SELECT
...
0
votes
2answers
28 views
Update with rolling sum grouped by another column
I'm trying to build a query to update a MySQL 8.0 table that looks like this
-----------------------
user_id | total | delta
-----------------------
1 | 0 | 30
1 | 0 | -10
...
0
votes
2answers
38 views
Storing the groupwise most recent related record
I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...
1
vote
1answer
22 views
Sybase query: How can I sort the extraction (by well formed columns)
community
I built a Sybase query statement in a script (using the isql command/line ) below is the code:
My script contains:
isql -U databasename_dba -P password <<EOF!
select ...
4
votes
2answers
100 views
How to optimize IN query on indexed column
I have a table with over 50M records. One of the fields is COLOR_CODE. I've set an index on the column COLOR_CODE like this:
"mytable_colorcode_idx" btree (color_code)
I'm noticing that when I run ...
0
votes
1answer
26 views
How to implement complex search functionality?
Below is my table structure:
CREATE TABLE `post` (
`postId` bigint(20) NOT NULL AUTO_INCREMENT,
`userId` mediumint(8) unsigned NOT NULL,
`areaId` smallint(5) unsigned DEFAULT NULL,
`postTitle`...
1
vote
1answer
30 views
How to query for address “predictions” while applying constraints
Let's say I have this simplified database scheme.
The scheme consists of three tables, street, city and zipcode that all have a many-to-many relationship to each other.
What I want to do is a ...
0
votes
1answer
45 views
slow count queries - what are we doing wrong?
Using Postgresql 9.6.14, running the following query:
SELECT COUNT(*) AS "__count"
FROM "crm_client"
INNER JOIN "crm_role"
ON ("crm_client"."role_ptr_id" = "crm_role"."id")
INNER ...
1
vote
2answers
35 views
How to find resource intensive SQL operations in Postgresql? High CPU usage spike
I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period.
...
0
votes
0answers
26 views
Performance Problems with MongoDB Atlas M0 with Larger Documents
Problem Description
Our team is developing a small web application for an academic research project, which is currently hosted on Heroku and connects to a MongoDB Atlas M0 (Free Tier) database. The ...
0
votes
0answers
59 views
Reproduce Deadlock in SQL Server
I have identified a deadlock by using Solarwinds, I have noticed that it is happening in a specific table, that table only has a primary key, which tells me that the deadlock makes sense since every ...
1
vote
1answer
37 views
selecting data after filtering out
I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:
Table A - primary table - products:
+----+-----------+-------------+------------+--------+
| id | title | description |...
-2
votes
1answer
35 views
Performance using a jsonb column in PostgreSQL? [closed]
The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
2
votes
2answers
148 views
Usage of NON Clustered Index in SQL Server
I have 2 Index:
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC
)
CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA]
(
[Column3] asc
)
Now I have 2 ...
0
votes
1answer
14 views
Slow Query not using index in one of the tables
My Rails table structure is like this: a User has_many Contacts, which have one Reservation each, which have many ReservationNotes each.
I'm trying to get all the ReservationNotes for the contacts ...
1
vote
1answer
17 views
How to skip prefix column check in multi column index in MySQL?
Below is my demo table structure:
CREATE TABLE `table1` (
`postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent',
`postLocation` smallint(6) NOT NULL,
`postArea` smallint(6) ...
0
votes
0answers
24 views
Read performance of SQL Server data files data from Azure BLOB
I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here.
The interesting ...
1
vote
1answer
95 views
Slow join performance SQL Server
I am struggling a bit to improve performance on one of my queries.
What I had is basically all of those 4 tables for reporting without any indexes, PKs, etc.
I was thinking that if I copy them (...
0
votes
0answers
78 views
SQL Server Query Timeout with NOT IN clause but IN works fine
So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server.
Works fine on Test and Dev and if we backup and restore the production DB to either of ...
0
votes
1answer
17 views
three tables relation order is correct but values are false
i have those tables
table1
| id | name |
| 1 | axe |
| 2 | bow |
| 3 | car |
| 4 | dart |
table2 table3
| t1_id | ...
0
votes
1answer
27 views
Proc runinng slow once in a while [duplicate]
I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min.
For past couple of weeks it happens once in ...
1
vote
1answer
91 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
37 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
33 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 ...
6
votes
0answers
91 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
21 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
26 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
57 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
51 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 ...
