Hot answers tagged

12

I agree that "correct answer" isn't correct. You can't rebuild indexes that have been dropped, you would need to create them again, and it would make no sense to do the insert last after the indexes have been dropped and created anyway. I presume the correct answer is in fact something like Disable the non clustered indexes. Do the Insert Rebuild the non ...


9

If you must use a single query (as required by a single inline function), you can use one of the two options below (illustrated in my recent answer to Relating 2 tables with possible wildcards?): Option 1 Use multiple APPLY clauses with a startup condition for each using an outer reference from a previous apply in the chain. The efficiency of this method ...


9

...why the huge performance hit from joining to sys.databases? And why is it inconsistent? There's nothing special about joining to sys.databases. The optimizer happens to choose a plan that is inefficient for the first query. Specifically, in this area of the plan: ...the optimizer chooses a nested loops join to SYSDMEXECCACHEDPLANS, presumably ...


7

The SOS_SCHEDULER_YIELD accumulation is just like I suggested on #sqlhelp. Each of those equates to 4ms of CPU time for the query, and they always show zero resource wait time, as there is no resource wait involved (thread yields the processor and goes directly to the bottom of the Runnable Queue on the scheduler). So - this query was churning through CPU ...


7

This table should have a clustered index on UserId. If there is no more than one thumbnail per user, then the clustered index can be the primary key. Since you say you have a currently-running system on SQLServer 2012 standard edition, online index builds are not possible. You could wait for a low-activity period to create the index, or create a new table, ...


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


5

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way. Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query ...


5

The proposed solution doesn't make much sense because: Calling ALTER INDEX ALL on a heap has no effect; for heaps, it only affects the NonClustered Indexes, none of which exist any longer in the proposed solution. And even if this Table had a Clustered Index that was dropped in the step to drop all indexes, it would then be a heap. As the MSDN page for ...


5

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1: When GROUP BY is present, or any aggregate functions ...


4

You do not specify which RDBMS you are working with. Most what I write here should be quite independent but I mostly have experence in MySQL so maybe different systems allow some other optimizations. The (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount is a dependent subquery - it will be executed for each row in your results. Even if ...


4

I don't know sql-server well enough to state whether it works this way or not, but in theory there is no way you can say that one part of the union is evaluated before another. I.e. even though you have a perfect match, you may still end up with an approximate one. You can however force this behavior by adding a priority to each part of the union and order ...


3

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows. string ... 20 characters long, which in memory is roughly 5x that of the integer (if an integer is 4 bytes, and the strings are pure ASCII at 1 byte per ...


3

You can really benefit from an index on hosts (hostname, scan_id) for this query, and possibly another one including status (especially for the second query below). Your query may also benefit from transferring some joins to per-row totals: CREATE INDEX idx_hostname_scanid ON hosts (hostname, scan_id); CREATE INDEX idx_hostname_status_scanid ON hosts ...


3

Combine all queries using LEFT OUTER JOINS and use the resulting columns in reverse order as parameters of COALESCE function. This function will evaluate all the parameters from left to right and take the first not null value.


2

I tried the LEFT JOIN .. IS NULL idea I noted in the comment: SELECT RECORD.ID AS RECORD_ID, POINT.ID AS POINT_ID FROM RECORD LEFT OUTER JOIN ( POINT LEFT JOIN SEGMENT s1 ON POINT.ID = s1.POINT_START LEFT JOIN SEGMENT s2 ON POINT.ID = s2.POINT_END ) ON RECORD.ID = POINT.RECORD_ID AND s1.POINT_START IS NULL AND s2.POINT_END IS ...


2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


2

Join to a subquery that computes numero with the window function row_number(): UPDATE movimientos m SET numero = sub.rn FROM (SELECT id, row_number() OVER (ORDER BY orden, id) AS rn FROM movimientos) sub WHERE m.id = sub.id; Details for UPDATE syntax in the manual. If you have concurrent write access you need to lock the table to avoid race ...


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


2

The server has too little memory. 16GB is the amount of memory found in a high end tablet. That said, this may be secondary because varbinary aside, the database is quite small. But even that is overloading the server memory. This is quite important because of... For most database use, disk speed is paramount, CPU does not matter (because memory as cache ...


2

Surely READ UNCOMMITED is a recipe for disaster? It's not called "Dirty Read" for nothing! Everything I've ever read about Oracle says so. In particular, Tom Kyte's "Oracle Database Architecture" which flat out states that Oracle refuses to provide that level and that is a good thing! I saw the MySQL tag on this post, but this is universally applicable to ...


1

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


1

(Assuming InnoDB...) The data and the PRIMARY KEY are in one BTree. Each secondary INDEX (including UNIQUE indexes) is in its separate BTree. An update requires modifying a record in the data BTree. If that also involves updating a column that is in any index it requires, effectively, a DELETE from that BTree plus an INSERT somewhere else in that same ...


1

PostgreSQL's DISTINCT ON is very elegant and performs very well (often better than aggregates): select DISTINCT ON (foo.id, foo.baz) foo.id, foo.baz, bar.boom as min_boom from foo join bar on foo.id = bar.foo_id ORDER BY foo.id, foo.baz, bar.boom; Or select foo.id, foo.baz, x.min_boom from foo join (select DISTINCT ON (foo_id) ...


1

One thing to try with IN is to change it to EXISTS (with the proper modifications to the subquery): SELECT COUNT(order_id), user_id FROM MyCustomTable a WHERE type='x' AND EXISTS (SELECT 1 FROM MyCustomTable b WHERE type ='y' AND user_id='56' AND ...


1

Thank you to Paul White, Lennart and Kunal Chitkara for their answers. Also, thanks to ypercubeᵀᴹ for being the first to point out that ORDER BY is required even when a SELECT TOP 1 * query consists of multiple UNION'ed subqueries. 4 Approaches I compared performance of the query written in 4 different ways: Method 1 Ordered SELECT TOP 1 from UNION'ed ...


1

you can populate the table and check for rows CREATE FUNCTION dbo.F1 (@int int) RETURNS @table table (col1 int, col2 varchar(10)) WITH EXECUTE AS CALLER AS BEGIN --insert into @table values (@int, 'one'); if((select count(*) from @table) > 0) return; insert into @table values (@int, 'two'); return; END; GO SELECT * FROM ...


1

So, to test query performance for SELECT statements you'll want to leverage statistics and look at the query plan. Your statistics (using SET STATISTICS IO ON, and SET STATISTICS TIME ON) will show you the number of logical, physical, lob reads and the CPU and elapsed times of the query. When you compare partitioned versus non-partitioned results look for ...


1

If indexes exist on SEGMENT (POINT_START), on SEGMENT (POINT_END), and on POINT (RECORD_ID), your query would perform well. MySQL 5+ creates indexes automatically for any foreign keys, so they're most likely indexed. Note that IN is used for sets, but your query would return zero or one records at most for in IN clause, so an EXISTS would perform better: ...


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



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