New answers tagged

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 (...
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 ...
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