Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
3,245
questions
0
votes
1
answer
47
views
Why query takes ages when I filter by an indexed boolean column?
I have a query that filters on a boolean column that has index. But, the query takes ages to finish. When I don't use this filter, the query returns very quickly.
Here are the explain plans. The first ...
- 107
1
vote
1
answer
23
views
MySQL: Materializing table function take so long
Thanks for checking my question
I encountered a strange issue. the same query in same db return different actual time
here is explain analyze statement
explain analyze
SELECT COUNT(1) AS `cnt`
FROM `...
- 11
0
votes
0
answers
12
views
Which is faster between regex and string length function, in searching string of different length?
I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
- 21
0
votes
0
answers
9
views
Find the I/O cost of the following question?
Estimate the I/O cost of retrieving records from Parts table that contains price less than or equal 1000 rupees. Assume that 15% of tuples satisfy the selection criteria. There are 3300 pages in the ...
- 1
0
votes
1
answer
48
views
Different performance of SQL Server instances on same server
I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). ...
- 11
3
votes
0
answers
80
views
Why does my query take a long time to run, and why does changing the order of the joins make it quicker?
I have a query in sql server that takes close to 3 minutes to run. Interestingly enough, when I change the order of one of the joins the performance improves dramatically and it takes only about 20 ...
- 31
0
votes
1
answer
32
views
making query more efficient when there is a millions of row
My table consists of the following columns:
job_id,
event_type,
timestamp,
client_type,
role_type,
Every job_id has record of which event_type (example login, edit, delete) , what timestamp, which ...
- 1
2
votes
3
answers
291
views
How to query most recent pair of each unique data-pair?
There's a table wherein there currency exchange rates are.
fx_rates
---
id
buy_curr_code
sell_curr_code
rate
inserted_at
A table gets updated with unknown frequency: sometimes daily, sometimes ...
- 33
0
votes
0
answers
22
views
Optimising a recursive SQL query that processes several million records in BQ
I need help optimizing a recursive SQL query in BQ.
I have hierarchical data stored in a table as parent-child relationships, i.e
will be stored as
parent_item_id
child_item_id
1
1
1
2
1
3
3
4
...
- 101
0
votes
1
answer
15
views
Is it slower to pass a composite type representing a table to a function then the individual arguments?
Let's say you want to create a function
CREATE FUNCTION f(a,b,c,x,y,z) FROM t;
Is it any slower to restructure the function such that this works,
CREATE FUNCTION f(t) FROM t;
- 58.9k
0
votes
1
answer
33
views
Postgres return multiple columns from subquery
I have found older posts but I'm curious if there is any better solution for my case.
I have working code but it is really slow. I tried to return two columns in subquery but I have an error (the ...
-1
votes
1
answer
50
views
Most efficient way of search for only spaces strings
I'm consuming data from a view. The view is large (millions of rows). I need to know if a specific varchar column rows are composed completely of spaces (" ") or if it has words on it.
Right ...
- 13
2
votes
0
answers
16
views
set options for replicating slow queries in SSMS
I am investigating a slow ad-hoc query(about 45 seconds) from our application. I am not able to replicate the slowness in ssms however to investigate indexing, reads, etc. I've attempted to use the ...
- 253
1
vote
0
answers
16
views
Is it possible to replace data in table with query case outcome
I hope there is someone who can point me in the right direction. I am building a Formula 1 poule. Now I have a table "race" where the predictions come in and a table "uitslag" ...
0
votes
0
answers
38
views
Optimize SQL Query
Im having trouble with the performance of this query, especificallly with the case statements. How would yo optimize it?
SELECT a.t,
a.c,
a.id,
a.id_r,
a.s,
a.n,
a.n_s,
a.c,
a.co,
a....
- 1
0
votes
3
answers
96
views
Poorly Performing Query with Massive Misestimates
I have been looking at this statement for the last day but I've been unable to work out the best way to improve it. So, I thought I would reach out to the wider community for some advise.
SELECT [FLD6]...
- 171
0
votes
0
answers
21
views
Postgres/Aurora query response time unexplained
I have AWS Postgres Aurora RDS version 11 running with db.r6g.12xlarge instances in a 3-node cluster with 1 writer, 2 readers. It should be noted though that this also occurs on our on-premises Ubuntu/...
- 121
0
votes
0
answers
38
views
Why is the query time of my PostgreSQL DB so much slower than in my MySQL DB?
I've been testing the performance of my PGSQL DB and MySQL DB with around a million entries and I've come to the conclusion that MySQL seems to execute my queries much faster, but I feel it should be ...
- 9
-1
votes
1
answer
124
views
conversion failed when converting date and/or time from character string only on one of the server
I have a sql query as follows:
select * from Txn_Master
where Txn_Type = 'TPD' and
Txn_Date = Getdate()
order by Txn_ID DESC
But in this sql-server query when i enter date manually like '01/06/2023'...
1
vote
0
answers
35
views
Best performance for cleaning table
I have two tables with millions of rows in which an average of 300 or more rows are added every day between the two tables.
Many of these rows are rows that do not provide any information and I need ...
- 111
1
vote
1
answer
43
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 15
1
vote
0
answers
47
views
Postgres sorted join with millions of rows, low cardinality, slow query - time to partition tables?
I have an API that is used to query for data about programs and other forms of content.
We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding ...
- 11
0
votes
0
answers
31
views
Optimization of google query with multiple LEFT JOIN and OR operators!
I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
- 1
0
votes
0
answers
23
views
PostgreSQL request taking a lot of time to sum 30 columns
I've an interesting case, I've been able to solve it but in a strange way.
Here's my request :
WITH geom_cte as (SELECT geom as geom)
SELECT SUM(number_a * percentage) AS number_a,
...
...
- 1
0
votes
0
answers
23
views
Improve performance of postgis query with distinct
Hej,
in my hobby project "luft.jetzt" I want to show the user the latest data from air monitoring stations around him. Due to my poor database skills, I am unable to write a performant ...
- 1
0
votes
0
answers
47
views
tuning big partitioned table to make it faster
I have a partitioned table, call it Tabel1, that is based on partition by LIST on ID_X column. This ID_X column is also indexed and it is part of a composite primary key with ID_Y column.
I do this
...
- 137
1
vote
0
answers
29
views
Multipile queries vs a long query with joins in my situation
I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
- 11
0
votes
1
answer
47
views
Will update statistics fix wait type IO_COMPLETION issue in Microsoft SQL Server?
I am using Microsoft SQL Server 2017 and recently we have come up with a situation where we see there is Wait Type IO_COMPLETION is contributing 60%. After performing update statistics this issue got ...
- 251
2
votes
1
answer
110
views
LEFT JOIN not using index when `column1` < `value` AND `column2` > value
I'm trying to LEFT JOIN a table to another table where a column value is between two column values in the second table, and it is not using an index when doing this seemingly simple query. It has ...
- 131
0
votes
0
answers
18
views
Best approach to search for a word in a corpus of pdf and docx files
I am new to the community, so feel free to correct any clumsy mistakes.
I have a large set of pdf and docx files ~10TB. I want to perform searches for specific words that will yield the document, the ...
- 1
0
votes
1
answer
73
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
- 155
-1
votes
3
answers
97
views
How to increment value in Master table column with number of records in Details table in performant and accurate way?
I am using SQL Server 2008 and above.
I have following two tables:
MasterTable
MId
DetailsCount DEFAULT 0
DetailsTable
DId
MId
In MasterTable.DetailsCount, I store number of records in DetailsTable ...
- 121
0
votes
1
answer
35
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 ...
- 1
0
votes
0
answers
204
views
How to avoid cluster index scan?
I've a table with a few millions of records. Here's the table structure:
Column_name
Type
Computed
Length
Prec
Scale
Nullable
TrimTrailingBlanks
FixedLenNullInSource
Collation
Id
int
no
4
10
0
no
(n/...
- 61
0
votes
0
answers
24
views
Would INNER JOIN on equality of two keys delete rows effectively?
Ī̲ repair a MediaWiki site after a DoS attack which injected terabytes of garbage into the text table. Ī̲ found that DELETE with clauses works painfully slow and, even worse, locks the table; that is, ...
- 101
0
votes
1
answer
23
views
How to efficiently query with a join and a where/order across two tables?
I have two PostgreSQL tables:
contents
id (primary key)
captured_at (date)
status (enum)
plenty of other columns we don't care about
library_contents
library_id
content_id
So each content can be ...
2
votes
1
answer
74
views
Efficient downsampling of a selected timeseries to equidistant samples
We have timeseries data from several sensors in a table and I am interested in getting N equally-spaced samples from the full range, as the whole data would be too much to display in a web-app.
I am ...
- 133
0
votes
0
answers
18
views
MySQL InnoDB Full-text search: Override default stopword list
I'm using MySQL8.0 InnoDB and its full-text search features. The default stopwords list of InnoDB only contains 36 words. Extending the stopword list to 500+ words helps to lift our full-text queries ...
- 21
0
votes
1
answer
33
views
Do we have any way to optimize this similarity query?
Do we have any way to optimize the similarity query even more?
The query took 40ms, the table just has 100 000 rows:
Explain Analyze SELECT md_comic_id, similarity (md_titles.title, 'Omega ni dake wa ...
- 101
4
votes
1
answer
219
views
Parameter Sniffing - Hints and fixes
I recently dealt with a problematic stored proc.
At times, runs beautifully fast, other times, runs very very long.
I determined that bad parameter sniffing was the cause.
Just for info - The ...
- 131
5
votes
1
answer
286
views
How to optimize PostgreSQL OR query on two indexed columns
I have a large partitioned table that stores monetary transactions between accounts.
CREATE TABLE "transactions" (
"from" BYTEA NOT NULL -- sender account
,"to&...
- 117
2
votes
1
answer
52
views
PostgreSQL imbalanced index query optimization
I am having performance issues with my query because the index is heavily imbalanced. I have a column family_id on a table. The table has about 150 million records. This family_id column identifies ...
- 121
0
votes
0
answers
47
views
Update stats for ETL tables
There is a huge table with more than 2 tb size . Etl algorithm is as below,
if new scenario it will insert millions of data in partition table
2)if old scenario it will reimport & delete older ...
- 1
1
vote
1
answer
48
views
Occasionally Need to OPTIMIZE TABLE after table creation -- MariaDB 10.6.11
I transfer tables from one server to another by running the CREATE TABLE statement without any indexes. Then I batch insert all of the data into the table. Then I run an ALTER TABLE to add the indexes....
- 131
2
votes
0
answers
31
views
Slower delete performance in MySQL8 compared to MySQL5.7
I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7
Apologies in advance if this is a little fluffy at this stage and I've ...
0
votes
1
answer
66
views
Which database scheme will be better for dating app performance?
I'm creating dating app like Tinder with Spring Boot and want to make sure that even with huge amount of data it will be working with best possible performance.
My tables looks like above. All tables ...
2
votes
0
answers
83
views
deadlock on too many requests and DoS occurred, Why?
I ran into a strange monitoring DBA error "Deadlock" when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.)
Why did this occur?
I add the Deadlock XML Too:
...
- 121
0
votes
1
answer
89
views
Infinite running loop while executing procedure
I have this table with 3 records. 3 records are create scripts. I need to execute the create scripts and if there is a 208 error occurred then I need to keep the record and set status as 1. Then ...
- 89
0
votes
1
answer
54
views
Improve performance for query with nested LIMIT
I have two large tables in a Postgres 11.17 database: procedure_result_sets and procedure_result, where each set has many results.
I'm a stuck on about 0.3s when running this query.
Is there anything ...
- 101
0
votes
1
answer
31
views
Enforce query plan for queries with expensive predicates/UDFs
I have a question on how to enforce the query optimizer to use a certain execution plan. Lets assume we have two tables, t1 (25k rows) and t2 (100k rows). Also note that all the rows in t1 will find ...
- 3