New answers tagged query-performance
4
Using bigint compared to int has, at least, these potential performance drawbacks:
the data will use more pages on disk
this can affect how long it takes to read data from disk when it's not in RAM
it will also make any maintenance operations involving those fields take longer (backups, index rebuilds, CHECKDB)
these data pages will take up more space in ...
0
You need an index:
CREATE INDEX ON downloads_activity (date_trunc('day',created_at));
Depending on the data type, both the index and the query might need time zone adjustment.
0
Select A.Daily,
count(distinct da.user) as Users
from
(
-- calculate all constants needed once
select date(d) as Daily,
date(d) - Interval '30' DAY as DailyStart,
date(d) - Interval '1' DAY as DailyEnd
from generate_series(current_date - Interval '10' DAY, current_date - Interval '1' DAY, '1 ...
0
So I issued exec sp_updatestats and no more 25 seconds query executions. Same data, same queries. I believe however it will start degrading again after a while. Which worries me as if db engine comes to a screeching halt on dev database with a hundred records how soon it will become unusable on prod system? Maybe we'll have to update stats every hour or so?
1
I think what you are looking for is Query Store, but this is available only for SQL Server 2016 or higher.
For SQL Server 2014 or lower, you can set up an Extended Events (XEvents) session to capture the queries running longer than X amount of time. XEvents are really easy to use and quite intuitive, the only thing you should pay attention to is how much ...
0
Try SSMS's Activity Monitor:
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/open-activity-monitor-sql-server-management-studio?view=sql-server-ver15
Or try to use perfmon:
https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
1
Turn on the Query Store, then look at the reports. See
Monitoring performance by using the Query Store
1
Compare the execution plans. Could be a parameter sniffing issue.
If the plans are the same, compare the IO for the queries - look for physical reads when it’s slow compared to logical reads when it’s quicker. This would be if there’s memory pressure.
1
Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.
You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.
0
You can try with monthly partition table design using date column. It will keep each month's data in appropriate data files and easy to manage. Index B-Tree structure would faster in this method with big volume of data.
Index searching page count will be less and faster as per B-Tree structure
Easy to make read-only, remove specific files from database
Can ...
2
You can see that your second query has Rows Removed by Filter: 18562, while at an average, the first query has Rows Removed by Filter: 1875532.
If you run 50 individual queries, the optimizer will optimize each of them individually, and in the cases where only few rows satisfy the filter condition, it will probably choose a different and better execution ...
6
Multiple parallel threads for the same query all share the same session_id, or spid.
Kendra Little did a great write-up showing that here.
Brent Ozar Erik Darling also has a great, if only tangentially related, post about it here
If your query is blocking itself, that indicates one thread is taking longer to complete its work; this might mean you have out-...
2
The clue here is that those connections each share a "parent_connection_id," which per the docs:
Identifies the primary connection that the MARS session is using
I noticed the aliases used in those queries heavily implies your app is using Entity Framework. Default connection settings (scaffolded out by the Visual Studio project templates) will enable ...
0
PostgreSQL thinks that it can avoid the expensive sort by using an index that returns the rows in sorted order and filter out the rows that do not match the conditions. This strategy turns out to be bad, either because of mis-estimates or an adversarial distribution.
Sorting by primary key is in no way faster than sorting by other indexed criteria. And in ...
1
Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with ...
-1
Have you already added an Clustered Index and a Non-clustered Index? Make sure also that your non-clustered index is not more than 5 because some of the non-clustered index that your query is not using might slower the query even more. Be specific with your query also, if possible don't use the '*'. imagine, you have 4 tables joined together, some of those ...
0
SELECT SUM(CASE WHEN (approved=1) AND (deleted_at IS NULL) THEN `count` END) approved,
SUM(CASE WHEN (approved=0) AND (deleted_at IS NULL) THEN `count` END) not_approved,
SUM(CASE WHEN deleted_at IS NOT NULL THEN `count` END) deleted
FROM (your subquery) source
Top 50 recent answers are included
