New answers tagged

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


6

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


-2

There is a performance difference. The newer JOIN syntax *ansi-92 will outperform older ansi-89 syntax, at the very least on SQL Server and MySQL. I can't speak directly to Oracle.


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


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


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


0

INSERTs have lower impact in affected indexes than UPDATEs, however INSERTs affect all indexes (except for conditional indexes that do not meet the condition). Indexes are necessary if you need to retrieve data often and quickly. You must weigh the impact of one (indexes in updates/inserts) or the other (full scan searches): you can't have it both ways! An ...


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


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


0

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


4

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


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


0

SELECT COUNT(UserID) as CountUsers FROM (SELECT A.user_id as UserIDs, A.order_id as XTag, B.order_id as YTag FROM MyCustomTable A INNER JOIN MyCustomTable B ON A.User_ID = B.User_ID AND A.Order_ID <> B.Order_ID WHERE A.type='x' AND b.type = 'y' AND user_id <> '56') A Would this work?


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


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


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


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


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


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


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

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

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


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


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


-1

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data. So it would look like this: SELECT p.id, ...


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


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


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


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

Start by changing IN ( SELECT max... ) to = ( SELECT max... ). Change f.Food_name to ( SELECT Food_name FROM Food WHERE Food_therm_id = t.Therm_id ) AS Food_name and get rid of the LEFT JOIN Food ... (I am unsure whether the LEFT JOIN before JOIN is causing trouble.) If those suggestions do not speed it up enough, then... Please provide EXPLAIN SELECT ...


4

Amazingly broad question but a couple of simple answers. Indexing like you suggested. Make sure the appropriate indexes exist and if the need is great enough make sure they are all covering. Get rid of the *. Specify the columns you need. If you are pulling across 100 columns over a million rows that's going to be a LOT of data. If you only need 3 ...


1

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row. It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which ...


5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


2

You'll speed-up the query significantly if you create an index (ideally a unique index) on property_ad_id and created_at, and another on just created_at: CREATE INDEX ON property_ads_history (property_ad_id, created_at); CREATE INDEX ON property_ads_history (created_at); # if not already there! Note that the condition t1.price > -1 OR t1.price <> ...


3

ORDER BY in the sub query is what's making it slow. ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do. And you don't even need to order by all of it. What you need is only to filter out the minimum, so instead of: ORDER BY ...


2

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the following rearrangement of the query (it is functionally exactly the same but uses joins and removes all the redundant comparisons) to see what the planner comes up ...


1

It is best to store your tags in a separate table because of the many to many relationship. With something like tagging images you can end up with a very large number of comma separated values that would need to be parsed any time you search your database which will lead to massive performance problems as your data grows. Going that route and searching for ...


0

history: INDEX(created_reason, created_at) Are you trying to avoid the "last record" via the price <>? If something else, then consider changing AND price <> ( SELECT ... LIMIT 1 ) to AND NOT EXISTS ( SELECT * ... ) Please explain, in words, what the query is trying to do. Perhaps some drastic reformulation is called for. Anyway, it is a ...


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

SELECT tbl_A.id FROM tbl_A LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id WHERE tbl_B.id IS NULL This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations. As for varchar and char: char is more efficient when using data that is all the same length. ...



Top 50 recent answers are included