New answers tagged query-performance
0
Did you try indexed view?
CREATE VIEW vw WITH SCHEMABINDING
AS
SELECT d.dim_date_id
,f.dim_provider_id
,f.dim_event_id
,d.date
,f.input_form_id
,f.known_from
from dbo.tbl_fact_outcome f
join dbo.tbl_dim_date d on DATEADD(DAY,1,d.date) between f.known_from and f.known_to
where
(d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y' or ...
1
It depends!
on:
what is your bottleneck
database speed / computation power
transfer speed / bandwitdth
application computation power
application main memory
which indices are present
are the joins supported by them
are the selects supported by them
how is the data structured and what data does the application need of that data
do you need all the data ...
0
In most cases, one select will be faster. 5 selects mean 5 times the overhead, as @RickJames said.
If your database is not on the same server your query is running on, this also means 5 times the network latency, which can add a lot to your overall time. (I've seen this a few times recently, when employees had an application that talks to the database ...
6
Look at it this way... There is a lot of overhead in each SELECT:
Round trip between client and server
Allocate a thread to work on the query
Parse the SQL
Optimize it
Perform it (The meat!)
Send the results back
In some experiments, I have seen the "overhead" be 90% of the work. 5 queries takes nearly 5 times the overhead.
4
This is a common issue with functional transformations of any type not only dates. To avoid this issue just move the transformation to the constant side of comparison. Instead of
WHERE CONVERT_TZ(TBL.completed_date, timezone1, timezone2)
BETWEEN date1
AND date2
use the next syntax: (notice the reverse transformation of the TZs)
WHERE TBL....
0
Not enough rep to comment, so posting here. Execution plans would likely help understanding.
I wonder if the DATEADD function in the WHERE clause is messing with SARGability? Also, if most of your queries which search via [date] also search via [Errordescription] or vice versa, I would test creating a covering index on those. Not having more knowledge of ...
1
Adding OR clauses makes it more difficult to estimate how well the index will filter. One solution is to add a generated always column that calculates whether the predicates for magic and itemId are satisfied, and index that:
CREATE TABLE tblExample (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
fTS timestamp NULL DEFAULT CURRENT_TIMESTAMP,
status ...
4
You will always have a full table/index scan (no seek) anyway.
There is no way for SQL to "guess" on "CreationDate<= LastAccessDate" so it will have to read every single row to compare both date.
If your table is big, it will take time.
You could try with a Computed column on "CreationDate<= LastAccessDate", this could ...
6
You can improve things greatly without rewriting the query by just adding indexes, from what I can tell.
Here's the execution plan I get on SQL Server 2019 (note that I changed the date to '20090814' just so it would return some results):
Table 'Users'. Scan count 18, logical reads 15430
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 94 ...
2
You will get a table scan because you have a CAST() function on your date column.
I would make sure that the date column is indexed and then change the where clause to:
WHERE LastAccessDate >= '20160813' AND LastAccessDate < '20160815'
...that way you should get an index seek.
Probably even better if you turn the hard coded dates into datetime ...
-3
SELECT distinct Actor.Name
FROM (SELECT A.PID, count(M.MID) AS YCMOV
FROM Movie M
INNER JOIN M_Cast A
ON TRIM(A.MID) = TRIM(M.MID)
INNER JOIN M_Director D
ON TRIM(D.MID) = TRIM(M.MID)
WHERE TRIM(D.PID) = 'nm0007181'
GROUP BY TRIM(A.PID)
)YC
INNER JOIN
(SELECT A.PID, D....
3
Am I missing something obvious here? :-)
In one case you return all rows. In the other case, you have a WHERE clause to limit the rows to be returned. Even with the same plan, there would be a difference in the number of rows to be returned to the client app. That difference can certainly explain the difference you see.
In the actual execution plan, you can ...
2
Logical reads is only an indicator of how much data SQL Server reads from disk (or the buffer pool). There are a bunch of other factors that can contribute to a long-running query.
The most obvious suspect is how much time it takes to move the data from your server to Management Studio and/or the time it takes to render the data in a table. To eliminate ...
2
That's a system session.
DBCC INPUTBUFFER(37); might give a clue what it's doing, or CROSS APPLY with sys.dm_exec_input_buffer(), but you might not be able to understand any more about what it's doing. Since it's a background task, it is unlikely to be a cause for any other perceived slowness but, being internal, I'm not sure what you could possibly do to ...
1
Which index to use?
While your WHERE conditions are not very selective, the current query plan makes a lot of sense with ORDER BY activity_count DESC LIMIT 101. See:
How to search a table with 80 million records faster?
However, your predicates strike me as pretty selective:
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND ...
1
For your query with ...
a very small LIMIT 40
and not very selective WHERE conditions â‘
... this partial, multicolumn expression index might work wonders:
CREATE INDEX foo ON activities (COALESCE(origin_created_at, created_at) DESC, id DESC)
WHERE ("isBulk" = false OR type = 0) AND deprecated_at IS NULL;
â‘ Currently, after doing a lot more work ...
2
That query will get faster if you increase work_mem (because then there will be no more "lossy" blocks).
The idea to first select all ids from one table and then select rows from another table based on these ids is fundamentally wrong. You should instead join the two tables and do the same work with a single query.
0
Although Erwin's masterful-as-always answer has incredible performance, it seems to be dependent on the number of prefix characters that need to be matched, which could be tricky to maintain; in telco, e164 prefixes are effectively arbitrary, and can be remarkably long, particularly if you are going to local geographic prefixes in a country with an already ...
0
I can reproduce this if I do your steps exactly, creating the index before populating the table. But if I create the index after the table is populated, I can't reproduce it. That is because the index present during population (when it is not populated in order, the way the primary key is) becomes somewhat bloated. This bloat isn't a lot, but it is enough ...
1
I guess it depends on the size of offer: if the table is large, the nested loop join might still be the most efficient option.
You can try
SET enable_nestloop = off;
and see if that improves the execution time or not.
The mis-estimate may be caused by a correlation between offer_id and user_id across the two tables, but unfortunately there is no way that I ...
3
why an ORDER BY can influence the estimates of an index seek.
Can somebody explain this to me?
This is a top values query. The ORDER BY specifies which 1000 rows the query will return. Without the ORDER BY SQL Server is free to return any 1000. So once SQL Server has 1000 rows from Schema1.Object2 that qualify in the WHERE clause, it can stop. The LEFT ...
answered Jun 16 at 18:05
David Browne - Microsoft
24.8k2 gold badges25 silver badges59 bronze badges
0
Ok, so i've got a working query that is happening in an acceptable timeframe. It feels ugly, so if there are obvious ways I can improve it please do let me know.
with base_data as (
/*This is where the query for incidents/static assets goes*/
select affected_sectors, involved_parties, reported_at, tags, casualties
from incidents
...
0
try:
explain analyze
with testdata as (
select table0."individual_entity_proxy_id" as "INDIVIDUAL_ENTITY_PROXY_ID"
from test1 table0
where ((table0."shared_paddr_with_customer_ind" = 'N')
and (table0."profane_wrd_ind" = 'N')
and (table0."tmo_ofnsv_name_ind" = 'N')
and ((...
2
The USING clause of the CREATE POLICY command expects an expression inside the parentheses. An SQL query, by itself, is not an expression, but you can make it into one by writing it as a scalar subquery. So you just need another pair of parentheses:
CREATE POLICY ..
USING (
(SELECT p.owner_id = ANY (app_pub.get_current_role_ids ())
FROM app_pub....
2
Your query seems to perform ok already. Some ideas to squeeze out faster times:
Index-only scans for aggregated.offers
seller_nickname seems to be functionally dependent on seller_id. It is more expensive to read and group by a varchar(30) additionally, than to base that on just an integer. Cutting seller_nickname out of the base query should make it ...
3
Explanation of behavior
Some of the causes of the IO_COMPLETION wait type are:
Writing intermediate sort buffers to disk (these are called ‘Bobs’)
Reading and writing sort results from/to disk during a sort spill
There are two sorts that could be spilling, which uses tempdb.
A source of the slowness could also be one the unfortunate "many to ...
1
The reason why index is not used is that the selectivity is too small - at the estimated 4M rows out of 13M rows in the table it means that 30% of all records are estimated to be read. Instead of looking up 30% of all the data in random access (through key lookup), it's faster to read everything and filter it in DB engine.
There are basically three options ...
3
You could use HammerDB. With HammerDB you perform a TPC benchmark test. TPC benchmark testing can be used by vendors for official certification, but you can also use it as 'non-offical' for your personal benchmarking. The TPC-E (OLTP) and TPC-H (OLAP) are the more modern version of the TPC benchmark tests. With HammerDB you can emulate an OLAP or OLTP ...
0
PostgreSQL optimizes the query so that the left join is completely ignored, so that doesn't factor in the question at all.
You didn't provide EXPLAIN (ANALYZE, BUFFERS) results, but either you don't have an index on test1.rank, or PostgreSQL considers the condition not selective enough.
One thing you can try is to create an index on test1.rank and VACUUM ...
1
Hitting an index tens of thousands of times is not free. At some point it is faster to read the entire table, and clearly that planner thinks it has reached that point.
Note that the time taken to seq scan document_service_contacts is less than 10% of the total execution time (thought much of this low time is probably that it is already in the cache). ...
0
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
Your problem involves what STRAIGHT JOIN does.
Doing a STRAIGHT JOIN may take the Query Optimizer out of the way at some steps.
For example, note what the MySQL Internals Documentation says:
The straightforward use of find_best() and greedy_search() will not apply for LEFT JOIN or RIGHT JOIN. For example, starting with MySQL 4.0.14, the optimizer may ...
1
You should construct an UPDATE statement that updates many rows at once, along the lines of:
UPDATE table1
SET population_count_hour_1 = sub.pop_count_1,
population_count_hour_2 = sub.pop_count_2,
...
FROM (SELECT state,
count(*) FILTER (WHERE start_time < 3600 AND end_time < 0) AS pop_count_1,
count(*) FILTER (...
1
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
According to POINT - 3 :
In case when I query by news source ID (the commented out WHERE s.Id = 52), the result comes immediately, regardless of whether there are lots of items for that source or 0 items for that source.
This is possible because on using WHERE s.Id = 52 it using index from NewSources & NewITems table do check explain plan might be ...
0
Slow performance when joining a small table and filtering out on a non-key column in MariaDB (MySQL)
A classic performance problem. But it takes some out-of-the-box thinking to make it perform.
The trick is to build _and maintains an extra table for ordering news articles by category (or byline or topic or ...). Here is a writeup on the details: http://mysql.rjweb.org/doc.php/lists
-1
I try my best to explain what can do in this situation base on your question information, and I hope help you.
When moving to the new CE, you can expect that some query execution plans will remain the same and some will change. Neither condition inherently suggests an issue.
Base on the Microsoft Link and white paper wrote by Joseph Sack (SQLskills.com)
I ...
Top 50 recent answers are included
