Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,311
questions
0
votes
0
answers
22
views
How to put indexing in this query,query takes 85 sec to complete
Select
count(case when education_qualification='A' then 1 else null end) as Total_Niraksar,
count(case when education_qualification='B' then 1 else null end) as Total_Saksar,
count(case when ...
0
votes
1
answer
37
views
How to optimize a query in postgres?
I have three tables: company, employments and entries. A company has many employments and a single employment has many entries.
The entries table has the most items in the entire database (currently ...
0
votes
0
answers
24
views
Why did the number of live rows fetched by index scans drop after table ANALYZE?
We use PostgreSQL 12 and have a simple table, table_a, storing 100 GBs of data.
table_a has all the necessary indexes, so all rows are fetched using them, i.e., no rows are fetched using sequential ...
1
vote
0
answers
39
views
Request on a copied table is faster than on original table, why?
Here is a very simple request :
select
ftljh."ID_TRAIN",
ftljh."ID_JOUR",
ftljh."ID_LEG",
2 as "CMPT",
ftljh.&...
- 11
5
votes
11
answers
2k
views
Re-write T-SQL where clause causing performance issue
I have a query with the below where clause taking lot of time to execute since the underlying table has lot of data in it:
Environment: SQL Server 2022
WHERE clause:
DATEFROMPARTS(dyear, dmonth, dday) ...
- 1,898
0
votes
1
answer
36
views
Oracle query is fast from DataGrip, extremely slow within application
I have the following table(s):
CREATE TABLE items (
id NUMBER(20, 0) NOT NULL PRIMARY KEY,
assigned_to NUMBER(20, 0) DEFAULT NULL NULL,
revision NUMBER(10, 0) NOT NULL,
...
- 169
3
votes
3
answers
332
views
Getting a SORT operator when I have an index
On a Azure SQL db (SQL2019 compat), I have an ETL process that populates HISTORY tables in a DeltaTrack pattern.
In the Proc, there's an UPDATE to the HISTORY table which the query engine is using a ...
- 441
-1
votes
0
answers
42
views
SSIS ETL, Source ERP has a 13,000,000+ row history table and can't complete a SELECT * WHERE DATE>GETDATE()-7 query
I have designed two ETL packages, one that does a full Truncate and Load of the entire Inventory Transaction History table and one that attempts to do a partial update.
The full refresh takes 4-6 ...
0
votes
1
answer
23
views
Optimize import data from one table into another
Edited on mustaccio request.
I have simple table:
-- DROP TABLE IF EXISTS public.objects;
CREATE TABLE IF NOT EXISTS public.objects
(
id integer NOT NULL DEFAULT nextval('objects_id_seq'::...
- 1
1
vote
0
answers
29
views
Wrong query optimization plan with InnoDB
After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage.
The problem are some queries that are wrong optimized with the new engine.
...
- 11
0
votes
0
answers
13
views
MongoDB indexing for dynamic query
I'm trying to build a query optimizer for survey response data, all questions are stored in a collection and have some fields are number_of_selection, option_marked etc.
Now I'm trying to build a ...
2
votes
1
answer
107
views
SQL 2019 - Legacy Cardinality Estimation
I am running SQL Server 2019 RTM CU18
I am in the process of testing workloads against this server before migrating to it.
All databases are set to SQL 2019 compatibility level.
I am finding that ...
- 181
0
votes
0
answers
32
views
Faster running for this query sql server
i used database engine tunning advisor for creating some index and statistics but still run slow
SELECT
C.code_commande AS 'Order',
C.code_client AS 'Cust.',
...
1
vote
2
answers
92
views
AWS Aurora performance degradation after upgrade from MySQL v5.6 to 8.0
Kindly suggest which parameters to look for..
Following is the Performance Insights for our MySQL 8.0 AWS Aurora cluster..
We are consuming 3000+ queries per second and most queries return immediately....
- 111
4
votes
3
answers
266
views
Is there any way to improve performance on like searches that have the % to the left of the searched value?
I have a query that does a like statement on a column that stores fullpath locations of files on a computer.
Example
select *
from table
where fullpath like '%hi.exe'
Which never seems to use an ...
- 43
1
vote
0
answers
15
views
Strange performance difference in update query alternatives in mariadb
We have a table with composite primary key with 1.6M rows where 2 different ways of writing the same query yields vastly different response times.
Unfortunately our ORM creates the slow quiry variant.
...
-2
votes
0
answers
46
views
Which one is faster? Query on a string column, or join with a lookup table on string columns then query on primary-key column?
Which of these queries is faster?
select *
from SomeTable
where LookupKey = 'some-value'
or
select
SomeTable.*,
LookupTable.Id
from SomeTable
inner join LookupTable
on SomeTable.LookupKey = ...
- 1,343
-4
votes
2
answers
77
views
Calculate total_worker_time in minutes [closed]
I have an easy query which can show me the top 10 elapsed time by query_plan_hash and query_hash:
PRINT '-- top 10 elapsed time by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash
...
- 1,456
-2
votes
0
answers
14
views
What are best practices when storing and querying relatively big NDJSONs (up to 20GB)?
As part of a side project, I am going through a NDJSON data dump. I am currently doing some data exploration with Python using streams, but the whole process is really slow, even after chunking up the ...
- 107
0
votes
1
answer
38
views
Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?
I have a main table set up like this:
id
table_to_join
table_item_id
1
products
123
2
products
577
3
products
344
4
products
1230
5
images
14
6
images
42
7
video
555
8
products
400
9
video
9
...
- 33
0
votes
0
answers
15
views
MongoDB: Complicate aggregation operation speed optimization
We want to calculate the average weight reduced and total weight loss users according to scales collection. The following steps describe our aggregation:
Firstly, we calculate the average weight for ...
- 101
0
votes
0
answers
56
views
How can I improve the performance of a query that relies on data from lots of tables?
I am working on a Web application that renders a food menu in one view. The menu is modelled as one big tree, which contains nodes like services (breakfast, lunch, dinner), courses (starter, main, ...
- 103
1
vote
2
answers
73
views
Slow query in a created view. How can I fix it?
I made quite a complex View on my MySQL database, and I realized that some queries are slow. My first idea was to add indexes but it's not possible to do on a view so I'm lost on how to improve the ...
- 85
0
votes
2
answers
29
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 101
0
votes
1
answer
71
views
How to optimise a query with inner join and order by
I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.
There are 3 tables: transfers(id, amount, type, timestamp, file_id), ...
- 101
0
votes
0
answers
55
views
Optimizing query performance
I have this query that is not performing well
explain analyse
WITH RECURSIVE
user_orgs AS (
select o.id from organisation_admins oa JOIN usr_organisation o ON o.id = oa.id where oa....
- 127
1
vote
1
answer
54
views
PostgreSQL query optimization issues
I am trying to optimize a query on a PSQL table with ~14M rows, which normally would take ~10seconds to run:
SELECT r.cust_id as cust_id, r.payable as toBePaid, r.return_code as returnCode, COUNT(r) ...
0
votes
1
answer
62
views
Slower than expected query performance on Postgres
I just moved a FrontEnd web from querying a small Redshift Cluster to Postgres, looking for, primarily getting rid of Redshift but mostly to get better performance and user experience from a reporting ...
- 103
4
votes
1
answer
250
views
Selecting from sys.dm_tran_locks takes 100+ ms
Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks.
...
- 263
1
vote
1
answer
48
views
Is it a problem if BIGINT data type parameter gets passed into a table for join with INT data type?
We have an application that has been developed in Hibernate and the SQL’s are all inline from the app. There are no stored procedures. The problem I see is the way parameters are being passed in the ...
- 11
1
vote
1
answer
31
views
Slower MySQL queries involving both wp and custom table
Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
- 141
-1
votes
2
answers
135
views
Data retrieving is taking more than 4hrs to fetch 400K records
I have a table with 15 million rows, it's a parent table with 12 child tables.
Even for a simple count query, it is taking hour's to complete.
select count(1) from table
where col_filter >= 'number'...
- 129
0
votes
2
answers
126
views
How to improve performance on a SQL database table that has 60 million rows
I have a table that has 60 million rows in a Postgresql database.
Table is as follows:
id varchar PK
cutomerId Integer
orderId Integer
modified timestamp.
Now I want to create a summary of customer ...
- 103
0
votes
1
answer
24
views
db lock issue need to find the root cause
I have database locks. when I checked the process list the time taken for insert and update query was too large may I knw why and what is the root caused? what should I do inorder to reduce the ...
- 9
2
votes
1
answer
113
views
Postgres-15 response time jumps up from 0.5 ms to 100-200 ms once in a while (~ 0.5% of requests are affected)
I have a simple database with one table of ~5 millions rows, another table with ~10 millions rows, couple of other tables have 10th of thousands of rows.
There is a constant load without any spikes at ...
- 213
0
votes
1
answer
66
views
How to enhance (the speed of) my database
First of all, I need to say that I have not much formal training for SQL, especially the backend, so anything I tested was a trial and error of advice found on the internet.
I have a DB which stores ...
- 13
5
votes
1
answer
189
views
Single Predicate Check Constraint Gives Constant Scan but Two Predicate Constraint does not
I can create the following constraint on the AdventureWorks table Person.Person:
ALTER TABLE Person.Person ADD CONSTRAINT ConstantScan CHECK (LastName <> N'Doesn''t Exist')
This tells SQL Server ...
- 1,868
1
vote
1
answer
58
views
Postgres: does marking a Foreign Key constraint NOT VALID affect query planning?
One can use ALTER TABLE ADD FOREIGN KEY ... NOT VALID to skip checking the FK constraint on existing rows at creation time (and only have subsequently inserted/updated rows checked).
Aside from the ...
- 43
0
votes
1
answer
64
views
Slow COUNT DISTINCT on a large table in PostgreSQL
In PostgreSQL 14, I have a table with around 10 million of rows with this structure:
CREATE TABLE search_stats (
id bigserial NOT NULL,
search_date timestamp NOT NULL,
user_id varchar NULL,...
- 1
2
votes
3
answers
373
views
How to manage mysql database table index
I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end ...
- 141
0
votes
0
answers
18
views
Redshift: Any difference in performance between # and temporary tables?
Is there any difference between the below two styles of using temporary tables in Redshift -- in terms of cluster performance (or) R/W into and from the table?
I am new to Redshift, hence bit unaware ...
- 131
1
vote
2
answers
44
views
Does the Size of Unselected Columns Impact Performance of SELECT Queries in MySQL?
Suppose I have a table with the following structure, overly simplified for example purposes:
Table "Persons":
CREATE TABLE Persons (
id BIGINT(20) NOT NULL AUTO_INCREMENT,
name ...
- 113
10
votes
7
answers
1k
views
Improve The Performance Of Multiple Date Range Predicates
let's say
You have a stored procedure that accepts arrays of datetimes, which are loaded into a temp table, and used to filter on a datetime column in a table.
There can be any number of values ...
- 38.2k
0
votes
0
answers
158
views
Sometimes simple queries are slow, high buffer IO wait times the cause?
I have a Azure SQL database, Premium P2: 250 DTUs, using 250GB of storage.
Sometimes even simple queries runs slow, for example by getting a single row from a simple 300mb table with with 500k rows, ...
- 479
0
votes
1
answer
57
views
How sql organizes rows in one partition
I have always observed that if I run a query (either select or update) for multiple values in the IN clause, if those rows fall in two different partitions, query takes a lol longer and CPU usage goes ...
user2116990
0
votes
1
answer
50
views
Optimizing a MySQL street address table for search
I'm developing an app that has a MySQL table, addresses, which stores events that happen at a given US address. I receive this data from a third-party in large chunks at set intervals. The table has ...
- 11
3
votes
2
answers
104
views
Performance problems on SQL Server 2016 that goes away after running two updates flipping a null column value
This is an abstract question, at least to begin with. But I am looking for theories on why I am experiencing what I am experiencing.
I have a table with a number of columns, one of which is a Int that ...
- 133
0
votes
2
answers
40
views
Query a table with association to itself
General question here.
If a table e.g. Person has a foreign key to itself to model "parent" association and you had to do a query get anyone who is named "Peter" or their parent is ...
- 211
0
votes
4
answers
190
views
Indexes for speeding up find by rank / counting
Consider a table with a single id which has a PRIMARY KEY index:
CREATE TABLE test (id INT PRIMARY KEY)
Now consider queries like
SELECT COUNT(*) FROM test
SELECT * FROM test ORDER BY id LIMIT ?, 1
(...
- 143
0
votes
1
answer
182
views
Identifying the reason of the CPU pressure
I see a high CPU pressure in the SQL Server and SQL Sentry shows only the following command which consumes the CPU abnormally. I think this is an internal process of the database engine but what stuff ...
- 110