New answers tagged

0

Assuming "first level" means the leaf level of a nonclustered (secondary) index, the b_li/2 part accounts for the cost of scanning (using sequential I/O) through half those index blocks. Assuming "file records" means the base table, the r/2 component represents the cost of looking up additional data not present in the secondary index. Since this is a ...


3

If you go over to http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (which is the link you'll see if you open an execution plan as xml), you'll see the three reasons listed, which are: TimeOut MemoryLimitExceeded GoodEnoughPlanFound The articles you mention seem ok for find these events, are you having a specific problem? The only ...


1

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal. Considering the sheer size of your tables, you'll probably benefit from datawarehousing. If your tables are constantly updated, you'll need a trigger to keep ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...


0

My understanding: The table contains 1M rows of which 250k are returned by the query. There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL. The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions. The ...


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


0

Your question is too broad for any answer here to fully address, so I'll just address one point. For the major query type of "Give me the latest data generated by a device", you could create a new column in the table that gets the same data as the "data generation time" column and then have a periodic procedure that nulls older values in that column for ...


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

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

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


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

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

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


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


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


0

You have two options: either you loop results from PHP, or you store all the custom fields and values in one single JSON datatype field (stored as a json object, requires MySQL 5.7) in the users table.


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


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


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

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


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;


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


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


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


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


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


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.


0

Percona has a github page on the following OPTIMIZE TABLE (which references another Percona Blog) ANALYZE TABLE There is lots of info explaining why. For example, there are messages stored in non-leaf nodes of Fractal Trees. All those messages are pushed way down to leaf nodes. This simplifies retrieval of stats needed by the Query Optimizer. Please ...


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



Top 50 recent answers are included