Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
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 ...
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 `...
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; ...
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). ...
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 ...
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 ...
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 ...
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 ...
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 ...
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....
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 ...
-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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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/...
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, ...
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 ...
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 ...
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 ...
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 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....
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: ...
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 ...
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

1
2 3 4 5
65