Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,346
questions
0
votes
0
answers
25
views
mysql min with where clause optimization
I want to get min user_id for a tenant_id.
mysql engine - innodb
table - users(id, tenant_id, is_deleted, creation_date)
pk (id)
sk (tenant_id, creation_date)
query1 select min(id) from users where ...
-2
votes
0
answers
39
views
What is the reason this COUNT query is so slow?
I posted this other question a while back. I have to admit I fail to explain why the query must be so slow. Please help me figure out why. What causes this query to be not a simple index lookup? I'm ...
- 107
0
votes
2
answers
30
views
MySQL 8 query doesn't prefer index with higher cardinality
I have a query similar to the following:
FROM example_table
WHERE
`date` BETWEEN '2023-11-26' AND '2023-11-28'
AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, ...
- 211
0
votes
0
answers
4
views
Different Spool usage for running the same query
How can spool usage differ for running the same query once using my user and once using production user? as in using production user the spool usage hits one tera and in using my user the spool usage ...
1
vote
1
answer
51
views
What indexes should I put on this table, given this illustration of its contents and the query I will be running on it?
I am working with a table (which I control) that is being populated by another application (which I don't control), each time a record of a particular type in an external system is created or updated. ...
0
votes
2
answers
76
views
formating date colums causing performance issues
I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance ...
- 25
1
vote
1
answer
65
views
Using table valued function as a table in sql server join
I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots ...
- 25
0
votes
0
answers
22
views
resume search with full text index
I have a database structure where resume stored in document column in WHDocument table in database for each person. During skilled person search in application the persons skill is also searched for ...
- 25
1
vote
3
answers
51
views
MySQL query optimisation
This is the MySQL table DDL:
CREATE TABLE `users` (
`username` varchar(250) NOT NULL,
`jid` varchar(250) NOT NULL,
`nick` text NOT NULL,
`subscription` char(1) NOT NULL,
`ask` char(1) NOT ...
-1
votes
0
answers
71
views
SELECT query with ORDER BY is slow
A simple SELECT with ORDER BY on a table of 25 columns and 20 Lakh rows is slow, taking almost 40 seconds.
When ORDER BY is id (PK) the no issue takes around 0.003 seconds but when ORDER BY is on a ...
1
vote
4
answers
184
views
Query optimization question
I'm attempting to improve this query. When I run this as user it runs in ~1 minute but during the ETL load it can take up to 40 minutes. There shouldn't be any locking issues because these tables aren'...
- 71
0
votes
1
answer
26
views
UPDATE table vs JOIN performance
I currently have a query with roughly 10 JOIN statements that takes about 10 seconds to run. I have this displayed as a report on SSRS.
Would it be better to create a table in SQL server and run ...
- 119
-1
votes
0
answers
63
views
Stored Procedure execution became very slow all of a sudden in SQL Server 2012
I have a stored procedure that gets executed over 100,000 times per hour, with each execution typically taking less than 1 millisecond. However, there was a sudden occurrence where it started taking 1 ...
- 437
0
votes
1
answer
44
views
Performance issues with @> operator in postgres 14
CREATE TABLE public.article (
id uuid NOT NULL,
tags _text NULL,
CONSTRAINT article_pkey PRIMARY KEY (id)
);
I have the requirement to load articles that have a tag which is in a large ...
- 25
1
vote
1
answer
46
views
MySQL - Slow Performing Subquery when DISTINCT Is Used
I have this query that runs very slowly when DISTINCT is used;
SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
- 23
0
votes
0
answers
27
views
How to stablish a partitions & indexes stategy in Postgres for 100s of milliion rows
For the purpose of learning, I am playing with Postgress. In particular partitions and indexing.
The Tables
Let's say I have the following tables on my system:
webs
web_id --> BigInt
web_tld --> ...
- 51
0
votes
2
answers
48
views
INSERTing millions of files into a table
I archive millions of XML files (1-100MB each) in a table with the structure of
CREATE TABLE Data
(
ID int(11) unsigned NOT NULL,
XML longtext COMPRESSED,
PRIMARY KEY(ID)
) ENGINE=Aria ...
- 4,491
0
votes
1
answer
99
views
Is this Parameter Sniffing?
I have a particular stored procedure that takes 1 parameter.
This proc is executed once a day with the same parameter value everyday. The row count is also the same with each run of the proc.
Day 1 - ...
- 319
1
vote
1
answer
69
views
Does SQL implementations (or at least SQL Azure) skip obvious duplicate data with joins when returning data?
Say I have:
very wide table A where I need all columns
that I need to join with a small table B that has a LOT of rows.
Of course when I join these two tables I get returned all the content of A for ...
- 355
0
votes
1
answer
174
views
query bad performance
Is there anything I can do to improve this query performance? I have included the execution plan and the Server Profiler output. Is there a index that can help? I see there is a Index Seek that is ...
- 47
1
vote
2
answers
104
views
How to get local MariaDB to be as fast as local PostgreSQL?
Our scientific application needs to store and query fundamental parameters for a number of molecules. There are 2 to 28 million rows per molecule, but the number of molecules is expected to stay small ...
- 123
2
votes
0
answers
80
views
Query not using foreign key indexes
I have the following schema for my database:
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE employments (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
...
1
vote
1
answer
118
views
Union slow (4 minutes) - separate queries take 2-4 seconds
We have a linked server / db with asset data (not managed/owned by us) that we need to join with a local db (SQL Server 2019).
If we run the queries separately, they all run smoothly - but when doing ...
- 11
0
votes
0
answers
28
views
Slow execution even with proper indexes defined
I am using AWS Aurora Serverless V2 with MySql. I have a table with 10M rows as shown below.
CREATE TABLE 'VCont' (
'id' varchar(64) NOT NULL,
'version' int NOT NULL,
'colId' bigint unsigned ...
- 11
0
votes
1
answer
54
views
DISTINCT with LIMIT performance
It is important to note that combining DISTINCT with LIMIT can have a negative impact on performance. This is because the database engine must first find all of the non-duplicate rows in the result ...
- 1
0
votes
1
answer
47
views
SQL Server - Update Statistics FULL scan vs Auto Update [duplicate]
Something I have been trying to figure out..
I take a table with 2 billion + rows and I run a manual stats update with FULLSCAN
Sometime in the future, an automatic update stats runs against the table,...
- 319
1
vote
2
answers
583
views
How to optimize a query on a large table, besides using index?
I need suggestions/ideas of optimizing the following query:
SELECT COUNT(*)
FROM (SELECT 1
FROM f
WHERE parent_sha256 = $1
AND parent_sha256 <> sha256
LIMIT 1) AS ...
- 123
1
vote
1
answer
87
views
SQL 2019 - BatchModeOnRowStoreUsed - Legacy Cardinality - IQP
Have recently migrated from SQL 2016 where the DB's compatibility level was all set to 2008 to SQL 2019 where the compatibility level for all DB's are now 150.
Upon running workload for a while, I ...
- 319
0
votes
0
answers
36
views
Optimizing SELECT COUNT(DISTINCT) on a table increasing daily
Let's say we have a table Daily_users which has the columns student_id, school_id, grade, timestamp. We collect usage data of students daily and so the table grows daily (note that there could be ...
- 101
2
votes
1
answer
150
views
In Postgres, how can I retrieve the top N rows of each ranking partition without ranking the entire table?
Summary
I have a table with event sources and a table with events. I need a query which gives me the N most recent events from each source (N is anywhere from 1 to 100). Currently, I'm doing this with ...
- 123
0
votes
2
answers
81
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
- 101
0
votes
0
answers
29
views
Performance impact when querying without partition key on a partitioned table
Assuming I partition a table of large size (say 6TB) by month. We are serving very high traffic of around 300 RPS.
Sample schema
Create table mydata(
id int not null,
date_created not null,
uuid_val ...
0
votes
2
answers
59
views
query completely stuck in production environment, but works fine in lower environments
I have recently been finding my production application's database instance hitting around 100% cpu, and when I look at stuck queries, I was seeing a lot of stuck queries that were involving a table ...
- 245
0
votes
1
answer
48
views
Postgresql strange behavior with non-ascii characters with triagram index present
I see some strange behavior when using either gin_trgm_ops or gist_trgm_ops indexes. There seems to be quite a difference in plans when using say ILIKE or ~ and search for ascii phrases vs multi-byte ...
- 115
0
votes
1
answer
80
views
Wait time on server replies is sometimes very long when running the query over and over
As seen in this screen shot, I'm running the same query over and over again, comes back with 0 rows (as expected). For some reason I'm seeing very large wait times on server replies while running this ...
- 53
0
votes
1
answer
72
views
How do I identify what appears to be a CPU bottleneck within SQL Sever?
I have virtual machines on two different types of hosts (vmware).
Host type A has CPUs with a clock speed of 2.09GHz
Host type B has CPUs with a clock speed of 2.69GHz
All other resources are the same ...
0
votes
0
answers
51
views
Performance issue on same identical database with same execution plan
Need some help advise on one of the issues that we have bee working on.
For one of our SQL databases (SQL2014) we use database mirroring ( i know its old but we are working on migration) and there is ...
- 772
1
vote
1
answer
35
views
Finding the latest status
I have 2 tables events and event_status.
The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping.
I need a optimized query ...
-4
votes
1
answer
52
views
Please I received this error on a scrub process “scrub failed, no running scrub jobs”
Please help it is urgent . The database is also in replication enter code here
- 1
1
vote
1
answer
66
views
Optimize MySQL server configuration insertion
We are currently doing many insert/update transactions on our Mysql 8 server.
The duration of these transactions is really long and the problem is that we cannot predicate them (business choice)
We ...
- 111
0
votes
1
answer
86
views
How do I query out the largest value using an index
I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so
create index index_events_on_created_at_and_bucket_id
on ...
- 157
0
votes
1
answer
52
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
1
answer
78
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, event_participant, storing 100 GBs of data.
event_participant has all the necessary indexes, so all rows are fetched using them, i.e., no rows are fetched ...
- 29
1
vote
0
answers
46
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,908
0
votes
1
answer
65
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
355
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
0
votes
1
answer
25
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
37
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
24
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 ...
