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
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 ...
girish parganiha's user avatar
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 ...
Gabriel Michelassi's user avatar
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 ...
Milan Ilic's user avatar
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.&...
Doe Jowns's user avatar
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) ...
SQLPRODDBA's user avatar
  • 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, ...
Hasan Can Saral's user avatar
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 ...
Geezer's user avatar
  • 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 ...
funkyman50's user avatar
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'::...
Solmorth's user avatar
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. ...
LateAtNight's user avatar
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 ...
Deepak Kumar's user avatar
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 ...
Sicilian-Najdorf's user avatar
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&#46', ...
octavian darie's user avatar
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....
hacker315's user avatar
  • 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 ...
Steven M's user avatar
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. ...
Medisch Labo Bruyland's user avatar
-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 = ...
Saeed Neamati's user avatar
-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 ...
Francesco Mantovani's user avatar
-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 ...
eslukas's user avatar
  • 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 ...
peppy's user avatar
  • 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 ...
Hhry's user avatar
  • 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, ...
Check12's user avatar
  • 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 ...
Cyril N.'s user avatar
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 ...
user570286's user avatar
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), ...
Farad's user avatar
  • 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....
Alex Tbk's user avatar
  • 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) ...
Bogdan Maceasa's user avatar
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 ...
Alejandro's user avatar
  • 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. ...
mwigdahl's user avatar
  • 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 ...
Texan DBA's user avatar
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 ...
Rahul K's user avatar
  • 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'...
Python coder's user avatar
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 ...
user1555190's user avatar
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 ...
mark's user avatar
  • 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 ...
Viktor Vsk's user avatar
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 ...
herhor67's user avatar
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 ...
SE1986's user avatar
  • 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 ...
colophonemes's user avatar
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,...
drew458's user avatar
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 ...
Rahul K's user avatar
  • 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 ...
ranit.b's user avatar
  • 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 ...
Lucas Bustamante's user avatar
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 ...
Erik Darling's user avatar
  • 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, ...
viblo's user avatar
  • 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 ...
user avatar
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 ...
Tyler's user avatar
  • 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 ...
Jens's user avatar
  • 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 ...
More Than Five's user avatar
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 (...
Luatic's user avatar
  • 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 ...
Esat Erkec's user avatar

1
2 3 4 5
67