Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,966
questions
0
votes
0
answers
3
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 ...
0
votes
2
answers
449
views
large number of tables and postgres query performace
I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
CommunityBot
- 1
1
vote
1
answer
1k
views
When do you need to run Analyze Table on MariaDB?
We have migrated a few large databases from earlier version of 10.1.22 to 10.6.7 - the latest provided by ubuntu repo for 22.04. All was well for a few weeks and then we started seeing very slow ...
CommunityBot
- 1
1
vote
0
answers
40
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
-1
votes
0
answers
17
views
Indexes to add to my queries
As an assignment, I had to write 3 queries that answer 3 different business questions with an emphasis on optimization (Tables consist billions of rows). I'm not working with any actual DB so ignore ...
- 3,685
3
votes
3
answers
342
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 ...
- 82.1k
-3
votes
1
answer
36
views
Clients which have seen an impression of a product at least 3 times before purchasing it- most optimal answer for Billions-record table
or a project I am working on I was asked to solve this task:
Given the table
**events**
event_id int (autoincrement) --10B distinct values
event_ts datetime -- 10B
event_type int (1 = impression, ...
- 34k
0
votes
1
answer
66
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
-4
votes
1
answer
41
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,161
0
votes
1
answer
699
views
Calculate the duration in an interval and outside of events that can last over several days
I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11
For an event on the same day no problem. But over several days I manage to ...
CommunityBot
- 1
0
votes
1
answer
68
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 ...
- 170k
1
vote
1
answer
2k
views
MySQL does not use index, does full table scan instead
EDIT: See further developments below.
I have an InnoDB table with the following schema:
CREATE TABLE `data` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` CHAR(27) NULL DEFAULT NULL,...
CommunityBot
- 1
0
votes
1
answer
503
views
What is the most efficient way to check whether a BYTEA column contains data, in PostgreSQL?
We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA.
In various ...
CommunityBot
- 1
3
votes
1
answer
2k
views
SELECT Query is slow when run remotely in MySQL
When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then ...
CommunityBot
- 1
-2
votes
0
answers
28
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 ...
- 19.2k
0
votes
1
answer
43
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 ...
- 45.7k
1
vote
0
answers
41
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
1
vote
2
answers
110
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....
- 1,662
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) ...
- 17.1k
0
votes
1
answer
39
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
...
- 76.3k
0
votes
1
answer
587
views
Optimizing big sorted join (nested loop) based on value frequency
I have a query involving two tables, let's call them timeline and events, defined as follows:
create table timeline (
event_id int,
ts timestamptz
);
create table events (
id int primary key,...
CommunityBot
- 1
1
vote
1
answer
580
views
What is the most performant way to group by and join high cardinality columns in column based table?
I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
CommunityBot
- 1
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 ...
- 76.3k
1
vote
1
answer
279
views
Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows
I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them.
Trying to insert this query in a table on destination server takes over 2 ...
CommunityBot
- 1
3
votes
4
answers
282
views
Optimizing slow query
For a few days now, i'm trying to optimize this query.
But still I haven't found the right solution in how to fix this.
The contents table contains about 20 million records. As the connect table ...
CommunityBot
- 1
3
votes
1
answer
1k
views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
- 31
1
vote
1
answer
602
views
How to make delete duplicates faster?
On a mysql table with about 1.7M rows, I tried to delete duplicates posts:
delete a FROM comment a
INNER JOIN comment a2
WHERE a.id < a2.id
AND a.body = a2.body;
The result was:
...
CommunityBot
- 1
0
votes
2
answers
314
views
Performance of implementing product images in an e-commerce store as a separate table vs. as columns of the Product table?
I am creating an e-commerce store in Django and am currently trying to decide between implementing product images as an entity of their own then linking to the product by a foreign key or putting ...
CommunityBot
- 1
0
votes
1
answer
39
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,
...
- 359
2
votes
1
answer
665
views
MariaDB: Querying a timestamp partition
I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
CommunityBot
- 1
1
vote
1
answer
475
views
Why turning off condition_fanout_filter speed up query
bin/mysqld_safe --user=mysql --optimizer-switch=condition_fanout_filter=off --innodb-flush-method=O_DIRECT &
Query (from TPC-H query-19):
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS ...
CommunityBot
- 1
-1
votes
0
answers
43
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,358
2
votes
1
answer
124
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
1
vote
2
answers
211
views
Could hardware RAM disks (with battery) have any benefit in database servers?
For database servers with many writes, I am wondering why can't these ramdisk/ramdrive be a solution to avoid wearing out SSDs and to avoid losing data if too much is kept in normal ram.
Can these ...
- 11
1
vote
0
answers
33
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
14
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 ...
0
votes
3
answers
292
views
Using JOIN to find duplicates in another table taking too long
Have a MySQL 8 database with the following tables.
people
id | mobile | name
registrations
id | people_id | other_stuff
I am trying to find the mobile number and the number of times it's duplicated ...
- 54
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.',
...
- 27.5k
2
votes
2
answers
858
views
Optimising MySQL count() with INNER JOIN (550k rows)
I have the query below, which is currently taking approx 1 - 1.5 seconds to run in a MySQL 5.7 database. It's being called from a web application, and whilst I'll be implementing caching at the ...
- 1,420
0
votes
2
answers
796
views
Limit rows in Correlated Subquery
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
CommunityBot
- 1
0
votes
1
answer
429
views
how to avoid hash right join
i have this Postgres query where i left join a couple of tables. This query runs for hours and causes issues. When I run explain analyse I see that the most time is spent in one of the left joins, for ...
CommunityBot
- 1
1
vote
1
answer
247
views
Query sometime not using index
Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This ...
CommunityBot
- 1
4
votes
3
answers
271
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 ...
- 1,603
1
vote
1
answer
660
views
MongoDB - Slow aggregate query
It takes around 6min to get the result from MongoDB, when I use the following aggregate query.
db.barcodes.aggregate([
{
$lookup: {
from: 'company',
localField: '...
CommunityBot
- 1
1
vote
1
answer
521
views
join_buffer_size causes high cpu load
I am playing and testing with my.cnf and at the moment I have these server specs:
12 CPU vCore
500 GB SSD
24 GB RAM
1 GB Swap
and this is the input of my my.cnf:
# GENERAL #
# user ...
CommunityBot
- 1
0
votes
1
answer
951
views
Disk queue length very high in SQL Server 2016
I have two Servers A and B with the same settings, but on Server A, the query runs in 6 minutes and on Server B the same query runs in 15 minutes.
what I noticed is that on Server B is that disk queue ...
CommunityBot
- 1
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.
...
-4
votes
2
answers
79
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
...
- 38.2k
5
votes
1
answer
7k
views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
CommunityBot
- 1