New answers tagged

0

You can download and install the Microsoft SQL Server 2012 Performance Dashboard Reports from https://www.microsoft.com/en-us/download/details.aspx?id=29063. On that dashboard, you will see a link for Missing Indexes. This report will break down details for a set of Proposed Indexes determined by the SQL Server Engine. You should right-click and Export ...


2

Whatever you do, if you go down the RDBMS route, I recommend that you choose PostgreSQL over MySQL - it is a far more sophisticated product and has lots more functionality. Check constraints Window (analytic) functions CTEs - Common Table Expressions Proper set operators - INTERSECT, EXCEPT as well as UNION (which MySQL does have) Far superior support for ...


0

thanks for your answer. Ok, I did vmstat thing that's suggested in Oracle's site. Got this, I really don't know if there's something abnormal with these statistics. procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 39 280 43 ...


0

How much RAM? key_buffer_size, if using only MyISAM, should be about 20% of available RAM. Switch to InnoDB and change some of the settings. Are you never using AUTO_INCREMENT? You seem to have commented out auto_increment* settings. slave-skip-errors=1062 -- Do you understand how fast your Masters can be corrupted? table_open_cache=10000 ...


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


0

You would have to do some digging but one way I can imagine is the SELECT is pulling data pages into cache from disk and subsequent SP runs are just getting their data straight from the buffer pool without having to hit disk.


0

OK, after experimenting a bit further with queries and a set statistics time on, here's what I found: On a small IN-list (4-12 items) - ALMOST identical The performance is almost identical, the hardcoded variant uses a little bit more CPU, the overall "elapsed time" is either same or a little bit longer. On a bigger IN-list (150+ items) - subquery wins BY ...


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


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


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


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


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


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


0

Remember that size often have a tipping point. ie What works for < 80M rows doesn't necessarily work for > 80M rows. Were you able to split the update into smaller batches? ie. 50K rows each


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


0

From the EXPLAIN and table structure it can be seen that you are missing some indexes so some queries need to check million rows to return ~20. SELECT * FROM ooo_video_serie WHERE id_episode='96652' AND active=1 ORDER BY langue,lorde ASC; this would benefit from index on (id_episode, active) or even (id_episode, active, langue, lorde) but if expected ...


0

So assuming you want to keep it simple for the users of the select clause, and not knowing how they are executing it... how about using a function? One alternative: Pass the entire select clause as a parameter(s) to a function, parse it for the where clause column and then direct it to the table or materialized view as suggested by @dezso?


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


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


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


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


0

g.dataset_id = 3 is killing the left try this FROM markers JOIN genotypes g ON g.marker_id = markers.id AND g.dataset_id = 3 The where is killing the left joins in the exists so you can drop that This may give you better performance and exists ( SELECT 1 FROM genotypes join accession ON genotypes.accession_id = accession....


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


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

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


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

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


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


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


1

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



Top 50 recent answers are included