New answers tagged query-performance
0
votes
MySQL: Materializing table function take so long
Remove word like '%%' from the query; it probably slows things down unnecesarily.
Use RDMS relationships; don't hide information in JSON that you need to search on.
Avoid IN ( SELECT ... ) it is often ...
- 72.8k
3
votes
Accepted
Why query takes ages when I filter by an indexed boolean column?
You don't show EXPLAIN (ANALYZE, BUFFERS) output, so I am reduced to guessing. Anyway there are two main differences:
Since there is no single index for the query, PostgreSQL combines two indexes. ...
- 38.6k
0
votes
Accepted
Different performance of SQL Server instances on same server
From the execution plans, we can see that the important difference is the time spent doing ASYNC_NETWORK_IO waits. For your slow scenario, you do over 14,000 of them but only 159 in your fast case.
...
- 2,631
0
votes
making query more efficient when there is a millions of row
Your query is probably executing some unnecessary loop on millions of records millions of times.
Indexes could help but only if they are very selective.
A simpler way, that does require just two scans ...
- 433
1
vote
How to query most recent pair of each unique data-pair?
A third alternative that does pretty well performance-wise and doesn't require you to change your table schema, is to use a window function like ROW_NUMBER(). This allows you to enumerate the rows ...
- 29.5k
4
votes
Accepted
How to query most recent pair of each unique data-pair?
Erwin's answer is correct, but the query will become slower and slower as the table grows. So let me suggest a different data model:
Add a column active of type boolean that is TRUE for the most ...
- 38.6k
3
votes
How to query most recent pair of each unique data-pair?
DISTINCT ON is one of the simplest ways:
SELECT DISTINCT ON (buy_curr_code, sell_curr_code) *
FROM fx_rates
ORDER BY buy_curr_code, sell_curr_code, inserted_at DESC;
Have an index on (...
- 161k
1
vote
Is it slower to pass a composite type representing a table to a function then the individual arguments?
No the overhead is not different.
In order to benchmark this I create a table with ten million rows.
CREATE TABLE foo(a,b,c,x,y,z) AS
SELECT x, x%2, x%3, x%4, x%5, x%6
FROM generate_series(1,1E7) AS ...
- 58.9k
0
votes
Accepted
Postgres return multiple columns from subquery
Move the subquery into the FROM clause as a lateral join.
SELECT
random things to return as default from JOINs and table workers
,ss.*
FROM workers p
, LATERAL (SELECT SUM(s.points) as ...
- 3,098
1
vote
Accepted
Most efficient way of search for only spaces strings
SQL Server allows storing empty strings and strings composed entirely of spaces. But SQL Server also ignores trailing spaces in varchar comparisons.
So just compare the column to '', which will match ...
- 43.4k
1
vote
Poorly Performing Query with Massive Misestimates
I think the issue is the general complexity in the way the code is written, especially with the predicates themselves. Predicates that use a lot of OR conditions are known to be problematic at times, ...
- 29.5k
0
votes
Poorly Performing Query with Massive Misestimates
Preface: I'm no SQL Server expert and some syntax can be wrong. I'm speaking from a general SQL perspective.
1. Simplify the query
Try to get rid of the unnecessary, just to be sure it isn't confusing ...
- 433
1
vote
Poorly Performing Query with Massive Misestimates
To me it seems the TOP 25 clause is confusing the SQL Server engine. Note that the estimates in the whole execution plan are relatively close to 25.
One way to make the query quicker is to split the ...
- 468
0
votes
Parallel index-only scan with two sub-queries is slow
As far as I can tell (without looking into the source code) PostgreSQL only knows of one way to execute a query filter like WHERE col1 in (select...) or col2 in (select...), so it doesn't bother ...
- 35.1k
2
votes
Accepted
conversion failed when converting date and/or time from character string only on one of the server
when i enter date manually like '01/06/2023' it shows data in table but whenever i enter getdate() function to get current date automatically it shows no data
GETDATE() returns the current system ...
- 29.5k
Top 50 recent answers are included