Hot answers tagged

12

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here. In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID ...


8

You actually have 595,947 matching rows, which is about 3% of your data. So the cost of the lookup adds up quickly. Suppose you have 100 rows per page in your table, that's 200,000 pages to read in a table scan. That's a lot cheaper than doing 595,947 lookups. With the GROUP BY clause in the question, I think you'll be better off with a composite key on ...


5

The field in your WHERE condition is not the leading field of the index. You have measure defined as NVARCHAR so prefix the literal with an N: where Measure = N'FinanceFICOScore'. Consider creating a Clustered Index on SnapshotKey. If it is unique then it can be a PK (and Clustered). If not unique then it cannot be a PK, but can still be a non-unique ...


5

In some scenarios SQL Server can have really wild estimates for DATEADD/DATEDIFF, depending on what the arguments are and what your actual data looks like. I wrote about this for DATEDIFF when dealing with beginning of the month, and some workarounds, here: Performance Surprises and Assumptions : DATEDIFF But, my typical advice is to just stop using ...


5

If it's a case of different options causing different plans to be used, just check the options using sys.dm_exec_plan_attributes. If the options are not the same for a good and a bad plan, that can then be the reason. A list of plan cache keys can be found in the following answer by Martin Smith: What would cause parameter sniffing one one computer and not ...


5

Let us consider a dataset with N items, of which P items match the predicate and will be returned by the query. 1a.A linear search, unsorted data. There is no way for the algorithm to know whether a value will match the predicate until that value is read. Therefore the only solution is to read all values and ignore those which do not match the predicate. ...


4

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type: Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression One workaround is to use GETUTCDATE (which returns datetime): WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE())) ...


4

From the details you've provided it seems reasonable that the IX_VeryLarge non-clustered index would support both queries you've shown in your question. You have the Payload column typed as VARBINARY(MAX) - if you expect large objects to be stored in that column, I'd likely not INCLUDE it in the index since that will cause the index to be much much larger ...


3

Query Your question updates revealed that your query can be improved radically: SELECT m.* , (SELECT keyfield -- correlated subquery FROM extractkeyfield WHERE messageid = m.messageid LIMIT 1) AS keyfield , s.description AS sendername , r.description AS receivername FROM ( SELECT messagetime -- no ...


3

From comments: DISTINCT is very similar to GROUP BY <all selected columns> and getting rid of the temporary table may be impossible when joining many tables as the server needs it to check uniqueness of the returned rows. Covering indexes (Using index) are quite useful when you need to get the top performance. I prefer IN() instead of multiple ORs ...


2

Using an index requires bouncing back and forth between the index and the data. In MyISAM, each index is a BTree sitting in the .MYI file. At the leaf node of the index is a pointer into the .MYD file. (Or, for FIXED, it will be a record number.) Your SELECTs are happy to scan linearly through the index (a BTree is efficient at that), but then for each ...


2

Your query has many issues that difficult optimisation. You use LEFT JOIN in several tables where INNER JOIN should suffice (e.g. all cases of ged_tbl_document_dv and ged_tbl_document_type). You include tables that you later never really require (e.g. all cases of ged_tbl_document_type), so removing all of them wouldn't impact the result (replace all ...


2

1 - Query was introduced to this website 2 - An index has been created to improve the Query: CREATE INDEX CONCURRENTLY ix_feedlog_client_time_notif_id ON public.ja_feedlog USING BTREE ("clientid","gtime" DESC, "log_type", "id"); Total time before the index: 346507.823 ms Total time after the index: 625.375 ms 3 - The query was fast, but not enough. So ...


2

in order to tackle this I would like a T-SQL query that would show me all the values that have been set for the session. To view the SET options for current sessions: SELECT * FROM sys.dm_exec_sessions;


2

Marcello, it seems you have the answer in your question, albeit with one minor modification. The query: SELECT cp.plan_handle, st.[text] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %'; contains the term '%/* GetOnlineSearchResultsMondy %', which is a ...


1

The function pg_total_relation_size() returns Total disk space used by the specified table, including all indexes and TOAST data Given the disproportionate size between the tables and total relation size (100 to 1), it is probably mainly due to TOAST data. TOAST data is already compressed (LZ), so it will be unlikely to be able to compress it any ...


1

The Optimizer takes the parsed representation of SQL statement and Statistics to generate final execution plan with the lowest cost. During this process the Optimizer generates multiple plans and compares them. Execution plans may change as the Optimizer inputs(Parsed SQL Statement and Statistics) get changed. Why Execution Plans Change Execution ...


1

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees WHERE positionID in (1,3) ORDER BY <<something>>) a WHERE rownum <= 60) b WHERE rnum > 50 Note that you need ...


1

Indexes ! Use InnoDB, not MyISAM. Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)... Something like: SELECT a.article_id AS article_id, article_title, article_content, article_status, article_date_time, ( SELECT SUM(article_hits) FROM phpkb_article_visits WHERE article_id = ...


1

It looks like the join between ja_feedlog and ja_jobs is the culprit (it appears to be taking most of the time doing a filtered indexed scan on ja_jobs for each ja_feedlog resulting row). In cases like this where there are two joined heavily filtered large tables, I find it useful identifying which set of filter conditions will render the least rows on one ...


1

Replace dateadd() with datediff() to get an adequate approximate (30%ish). select distinct SessionId from [User].Session -- 1.2M est, 3.0M act. where datediff(day, CreatedUtc, sysutcdatetime()) <= 365 This appears to be a bug similar to MS Connect 630583. Option recompile makes no difference.


1

ObjectId is a misnomer if it is not unique. You are saying that it takes 4 columns to uniquely identify a row? Rethink. This WHERE does not make sense; it seems like you are over-specifying the row by filtering on so many things, including a flags: WHERE ObjectId = @objectId AND ObjectType = @objectType AND IsDeleted = 0 AND ObjectIdName = ...



Only top voted, non community-wiki answers of a minimum length are eligible