Hot answers tagged

18

To quote Campbell Fraser on this Connect item: These "cardinality inconsistencies" can arise in a number of situations, including when concat is used. They can arise because the estimation of a particular subtree in the final plan may have been perfomed on a differently structured but logically equivalent subtree. Due to the statistical nature of ...


18

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Consistency There is no general guarantee of consistency. Estimates may be calculated on different (but logically equivalent) subtrees at different times, using different statistical methods. There is ...


7

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design ! Also, AaronBertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100% If you want to understand how plan costing works .. Paul White explains it at his best here. From the query processor team - What’s this cost? General guidelines ...


6

Building an admittedly rather simple test bed on SQL Server 2012 (11.0.6020) allows me to recreate a plan with two hash matched queries being concatenated via a UNION ALL. My test-bed does not display the incorrect estimate you see. Perhaps this is a SQL Server 2014 CE problem. I get an estimate of 133.785 rows for a query that actually returns 280 rows, ...


5

The issue of the amounts that are multiplied is easy to solve. You just have to use a derived table that does the calculation (group by) first and then join that (derived table) to the other one, that stores the hierarchical structure. The derived table to use: ( SELECT userid, SUM(amount) AS sele_descendant_amount FROM webineh_user_buys ...


5

Your execution plan for the "individual queries" shows that pre-calculating the StartID, Category etc allows an index to be used efficiently on A, "seeking" straight to the record(s) you want (you have a Non-Clustered Index Seek in your query plan), having identified the given Category etc to search for. The "single-query" with JOINs, on the other hand, ...


4

In SQL Server 2014 & up, new cardinality estimation logic was introduced. From BOL : The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work ...


3

Consider an Entity-Attribute-Value design. The general concept is that you put all of the data in a very long, narrow table which may take the form of: CREATE TABLE dbo.PropertyAttributes ( PropertyID INT NOT NULL REFERENCES dbo.Properties(PropertyID), AttributeID INT NOT NULL REFERENCES dbo.Attributes(AttributeID), StringValue ...


3

Contradiction Detection could kick in to make sure only one of the statements is run, and in my simple test it did as long as there was a statement-level recompile hint, but why risk it? For example: USE tempdb GO -- CREATE SCHEMA agg --DROP TABLE agg.DataMin --DROP TABLE agg.DataMedian --DROP TABLE agg.DataWeightedAverage --GO CREATE TABLE agg.DataMin ( ...


3

To have the data held in two different physical sequences one most store the data twice. This can be achieved by defining a second, covering index. A covering index contains all of the columns required by a query. This way the optimiser has no need to refer to the base table to read further values and is unlikely to revert to a base table scan for the query ...


3

If MySQL properly started and InnoDB initialized with the configured buffer pool size then there is no reason for any error (barring some bug of course). The buffer pool is just that - a memory area used to buffer your IO. When there is not enough of it to keep all the active data, the server just reads them from disk. If you had for example 100GB table ...


3

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one. LIMIT will always operate after sorting is taken care of, so when sorting without an index is necessary, it will sort the entire recordset prior to processing it through LIMIT. I wonder what is it you want to achieve? Applying LIMIT over a ...


3

Nope. If you order by a not indexed column then the server HAS to load all the results into memory and can only then start sorting them. That is heavy - not sure how mySql does it, but on SqlServer that will also tax the tempdb for the temporary holding of the data. If it is a lot of data that may be super slow due to memory flowing over onto the hard disc. ...


3

I would not worry about the 100% The big number are the big number A lot repeats so start optimizing just one This is just a subset of your query DECLARE @date SMALLDATETIME SELECT Reffd AS NAME , ( SELECT ( ( SELECT count(*) FROM [cal_reg].[dbo].[customer] WHERE upper(Reffd) = upper(main.reffd) ...


2

Estimated sub tree cost is a SUM of cost of all operators preceding the one you are looking at. The easiest example is to look at the left-most icon - it will have an Estimated sub tree cost of whole query plan. There are a lot of sign in a query plan that show it needs optimization, however, I've seen a lot of situations when even perfect plans caused ...


2

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query: WITH rel AS ( SELECT prm.power_relation_id FROM power_relation_members prm JOIN power_lines pl ON prm.member_id = pl.id WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc' GROUP BY prm.power_relation_id ) SELECT pl.id, pl....


2

I looked at the table structures and as Aaron had previously pointed out TimeSheets does not have any indexes and the other tables do not have any nonclustered indexes. I created the two databases and their tables and generated the estimated execution plan and I get the same execution plan that you are currently getting. I created a clustered index on ...


2

SQL is not making up rows. If that join is producing more rows than you expect then figure it out. Are you sure ts.JobSubTypeId is a task code? That name does not sound like a task code. Try this - it will show you were the volume is coming from In your query each count is a row -- this is the raw count from Timesheets SELECT ts.JobSubTypeId, count(*...


1

You can create a materialized view on your table: CREATE MATERIALIZED VIEW thecopy AS SELECT * FROM mytable; Then add a unique index that matches your PK on mytable (you cannot add a 'real' PK there as it is not a 'real' table): CREATE UNIQUE INDEX ON thecopy (node_id, pricedate, hour); So your copy is there. If you want to cluster it, you need an ...


1

Define an unique key in your table such that each day can only be represented by one row (UNIQUE KEY(date) would be enough for the example you show). Then use INSERT INTO $tablename(...) VALUES(...) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2); Alternatively you might use REPLACE INTO, but that does the delete and insert, "burning" ...


1

If you mark the functions as WITH SCHEMABINDING, then this may help - particularly if the function doesn't access any tables.


1

So I happened to have stumbled on a more efficient pattern for this sort of operation (based on how much faster this is in testing). Given the above, here's what it would look like (I've excluded fields beyond what are necessary to point to a compoent_data record): Updated Query: WITH cd_start_end AS ( SELECT id, owner_rel_id, ...



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