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
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 ...
user3239193's user avatar
-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 ...
bdadam's user avatar
  • 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, ...
Shahid Thaika's user avatar
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 ...
Mohamed Fathi's user avatar
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. ...
utterly confuzzled's user avatar
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 ...
Madhusudan's user avatar
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 ...
Madhusudan's user avatar
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 ...
Madhusudan's user avatar
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 ...
datascinalyst's user avatar
-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 ...
Dr.G.Singh's user avatar
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'...
Hamilton's user avatar
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 ...
Bigbob23's user avatar
  • 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 ...
lifeisajourney's user avatar
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 ...
user1117605's user avatar
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 ...
Marisa's user avatar
  • 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 --> ...
Javi M's user avatar
  • 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 ...
Googlebot's user avatar
  • 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 - ...
Sicilian-Najdorf's user avatar
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 ...
Dirk Boer's user avatar
  • 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 ...
Jefferson's user avatar
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 ...
aitap's user avatar
  • 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), ...
Gabriel Michelassi's user avatar
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 ...
Rendi's user avatar
  • 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 ...
Ravindra's user avatar
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 ...
Linh OR's user avatar
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,...
Sicilian-Najdorf's user avatar
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 ...
KaliTheGreat's user avatar
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 ...
Sicilian-Najdorf's user avatar
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 ...
dezdichado's user avatar
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 ...
kanders84152's user avatar
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' ...
VarunAgw's user avatar
  • 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 ...
Harshita Mishra's user avatar
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 ...
patrick's user avatar
  • 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 ...
Pawel Zieminski's user avatar
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 ...
Steven M's user avatar
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 ...
Eta_Argus's user avatar
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 ...
Newbie-DBA's user avatar
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 ...
Anwesh Budhathoki's user avatar
-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
aee's user avatar
  • 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 ...
basiclawe's user avatar
  • 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 ...
Segfault's user avatar
  • 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 ...
Gabriel Michelassi's user avatar
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 ...
Milan Ilic's user avatar
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.&...
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,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, ...
Hasan Can Saral's user avatar
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 ...
Geezer's user avatar
  • 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'::...
Solmorth's user avatar
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. ...
LateAtNight's user avatar
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 ...
Deepak Kumar's user avatar

1
2 3 4 5
67