Tag Info

Hot answers tagged

11

There are at least three parts of your query that will or may benefit from rewriting: These five correlated subqueries in the SELECT clause are retrieving data from the same table based on the same condition: (select websitelink from company_profile where z_companyid_fk=a.z_companyid_pk) "Website", (select company_email from company_profile where ...


10

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML). When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you. The provided execution plan shows the optimizer choosing a static-like plan. Because the ...


7

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the ...


7

It means that the parameter should be defined to match the data type of the underlying column - currently the parameter is nvarchar(4000) but it should be varchar (looks like 255 but you should check the table). Whether this actually makes your query slow or leads to a bad plan is tougher to determine. But making those two definitions match certainly isn't ...


6

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable. SQL Server does however do it's best to ...


6

Assuming you checked off usual suspects in the wiki page as commented by @a_horse. Also see the spin-off addressing the discussion of bitmap index scans and the size of work_mem. "Recheck Cond:" line in query plans with a bitmap index scan Query This rewritten query should be substantially faster: SELECT id || ':' || group_number AS uniq_id ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


4

Have you tried rewriting your NOT IN predicates as LEFT JOINs? SELECT t1.id || ':' || t1.group_number AS uniq_id FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id AND t1.group_number = t2.group_number LEFT JOIN table_three t3 ON t1.id = t3.id AND t3.timestamp > NOW() - INTERVAL '30 days' AND t3.client_id > 0 WHERE t2.id IS ...


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


3

Basic answers Since you select a couple of big columns (info in comment) an index-only scan are probably not a viable option. This code works (if no NULL values in data!) Add NULLS LAST to make it work in any case, even with NULL values. The added clause won't hurt either way. Ideally, use the clause in the accompanying index as well: SELECT <some ...


3

Is it possible? Sure. Is there likely to be an improvement in performance? No. If there is a change in performance (barring cases where you discover that a join is missing or otherwise fix a query), it'm more likely that the old implicit join syntax will be more efficient. But that's pretty unlikely. Behind the scenes, when you have a query using the ...


3

First, a query or (update) statement with a condition like WHERE posts < comments that compares 2 columns cannot effectively use your indexes so it will probably have to do a full table scan. It might be better if you had a composite index, (posts, comments) or the other way around, but it would still need to do a full index scan. If the rows to be ...


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


2

How about Dynamic SQL ? SET @query = 'SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...'; CASE pSearchType WHEN 1 THEN SET @where = 'Col1 = pInput1'; WHEN 2 THEN SET @where = 'Col1 = pInput1 AND Col2 = pInput2'; WHEN 3 THEN SET @where = 'Col1 = pInput1 ORDER BY Col4'; END CASE; SET @sql = CONCAT(@query,' WHERE ',@where); PREPARE s FROM ...


2

The EXPLAIN plan for SELECT * and SELECT id, which you said were identical, would be separated by the number of rows accessed. How are the rows being accessed ? Through the index_things_on_active_and_date index. The SIMPLE in the EXPLAIN means it is a scan. In both cases, it is an index scan based on active=1 and date < '2015-07-11 00:00:00' How does ...


2

I immediately see a few things that confuse me here, which you should look into. Unnecessary Join In your query you have a portion ... LEFT OUTER JOIN houses ON houses.id = listings.house_id ... yet no values from the houses table are in your SELECT. The good news is that this is not affecting your performance, because the query optimizer has wisely ...


1

Don't use the construct IN ( SELECT ... ) if you can avoid it; it optimizes very poorly. Usually a JOIN can be done instead. Avoid having more than one subquery in FROM ( SELECT ... ) JOIN ( SELECT ... ) JOIN .... Again very poor optimization (because of an index on the tmp table). Again try to replace with a JOIN.


1

I used JOIN and LEFT JOIN to modified your query: SELECT u.*, p.*, f.approved FROM test.default_user AS u JOIN test.default_profile AS p ON (u.id>1 AND p.user_id = u.id) LEFT JOIN test.default_friend AS f ON ((f.user_id = u.id AND f.friend_id = 1) OR (f.friend_id = u.id AND f.user_id = 1)) WHERE u.email LIKE '%some_string%' OR u.username ...


1

Learn about composite indexes; use them where appropriate. For example: ON cpr.product_id = cp.id AND cpr.branch_id = 3 begs for either of these: INDEX(product_id, branch_id) INDEX(branch_id, product_id) Indexing flags (such as active) rarely useful. Don't use LEFT unless the "right" table has optional data. What do you expect the ORDER BY to do if ...


1

You can use a TRIGGER to update db1.reps.lastSyncAt when db2.user.lastmodifieddate is updated. Trigger: DELIMITER $$ DROP TRIGGER IF EXISTS db2.user_BEFORE_UPDATE$$ USE `db2`$$ CREATE DEFINER=`root`@`%` TRIGGER `db2`.`user_BEFORE_UPDATE` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN SET @UserVerification=(select reps.veeva_rep_id from db1.reps where ...


1

Here is your answer: MSDB on C: 86% of 32GB io_stall_write_ms : 192217 If MSDB is on c: master is on c: as well and I'm almost willing to bet that tempdb is still in the default location as the average I/O stall on c is 19 seconds! First, check if tempdb is on c:\ or has any file on the c:\ partition and if so move it away Check if the master database ...


1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...



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