Being on the Standard Tier (S0) is causing your queries to be throttled significantly, which is affecting the total runtime. Here are the times for all 5 statements in the batch, as viewed in Sentry One Plan Explorer:
As you can see, most all of the queries have a duration that's much longer than CPU time. This often means the queries are waiting on some ...
does this behaviour is shared with table value function or not?
the short answer is:
It is not, because if you check what execution plans you have in the cache these are the objects you can find there:
cacheobjtype nvarchar(34) Type of object in the cache.
The value can be one of the following:
Compiled Plan Stub
This is the answer:
There are a lot (thousands) of purchases per customer.
DISTINCT ON is fast for few purchases per customer. See:
Select first row in each GROUP BY group?
This should be much faster:
SELECT c.id AS customer_id, p.id AS purchase_id
FROM customers c
LEFT JOIN LATERAL (
FROM purchases p
WHERE p.customer_id = ...
Here what you need to do:
1) use #Temp table
2) break down query on parts, get data step by step
3) make sure columns which you use in the WHERE or join ON clause, are indexed
Temp Table - column data types below are just an example, use data types that exactly match column data types on your tables
create table #UserInfo (
The biggest problem I see is in your joins. You're joining the "id" of Users, to the "id" of Posts and Comments. This basically says, take every record in Users and join it to every record in Posts, then join it to every record in Comments.
Presuming that the UserID exists as a foreign key in Posts and Comments, the code below should work. If not, you'll ...
I can't get that to cost nearly that much.
On my (very fast) desktop it's under 1ms of CPU time, and on Azure SQL Database it's only 4ms.
Perhaps your SQL Server is on a VM and is not getting full access to the host's CPUs.
drop table if exists tblTask
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[tblTask]...
Since there is a limited number of resources (cpu, memory, disk) and you want to use them as efficiently as possible.
For OLTP you typically have many small transactions and concurrency is important. Your data model is typically normalized and most data access is done via index lookup. A subset of your data is used over and over so you want that to be ...
You never ANALYZEd the foreign tables, so the estimates are way off, and PostgreSQL chooses a bad execution plan.
ANALYZE tds, dq_infos;
should improve the situation.
Different from regular tables, foreign tables are not handled by autoanalyze.
To "dive" is to drill down the BTree from the 'root' down to the leaf node for a particular key.
Let's say you have a non-unique INDEX(x). Further, let's say that there are 100 rows with x=123. WHERE x = 123, the two dives would be to "find the first index entry for x=123" and to find the "last" such row.
His first use of "accurate" (in the article) was ...
Igor, you may find this workaround improves your speed on your MySQL 8.0.20 instance
SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;
Refer to dba.stackexchange.com Question 267143 and look for Shane Bester's mention with the bug report. You will discover this was an error in 8.0.20, that will be corrected when 8.0.21 becomes available. Make this ...
Add both of these; see which one it likes:
INDEX(data_sc, descrizione_sc, importo_sc)
INDEX(descrizione_sc, data_sc, importo_sc)
You provided EXPLAIN for one server; was the other one identical?
(the WHERE for NPV_SC and NCASSA_SC in this case select all rows):
All rows? Useless. Why even discuss NPV_SC, NCASSA_SC if you are not filtering on them?
Sorry, but I am going to be blunt.
Abandon the use of collecting data into ever-growing strings (communists, JSON, TEXT, etc). This goes especially for stock quote historical data. It works much better to use one row per quote.
(No, I can't explain why it slowed down. However, if you can provide a reproducible test case, please file a bug report at ...
I wouldn't consider this a hack. Windowing functions, including ROW_NUMBER are fairly efficient and generally perform equal to or better than alternatives. On new instances of SQL Server, I assume that FIRST_VALUE would be more performant, but have not specifically confirmed this.
Note: I've seen LAST_VALUE produce incorrect results, and would recommend ...