Tag Info

New answers tagged

1

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns. My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.


0

Saying "Sending data" merely points out how useless the Profiler often is. This index on a might change the EXPLAIN (eg, start with a instead of b) and speed up things: INDEX(`type`, `deleted`) -- in either order If you need further discussion, please provide SHOW CREATE TABLE and some clues of about how big each table is. Why is b used? Would you get ...


1

The problem with a FromDate / ToDate format is that the query generates an index scan. For example, a staff member takes vacation and returns two weeks later. ID FromDate ToDate 15 2011-01-03 2011-01-17 Imagine many staff members, some with 20 or 30 years of such work history, maybe taking only a day or two at a time several times each year. ...


0

A couple things I noticed about the query that if you can get them changed may help in performance Replace the datediff method with a variable that is set with the dateadd method to add 15 days to today's date. This could allow the query optimizer to generate a plan that can go parallel. Since you are using the exists method in the where clause specifying ...


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.


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


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


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


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


0

Generate a calendar table (that's googleable) and join your other tables to it. This makes it easy to do overlaps, etc. in an efficient way. In case it is not clear this is the standard way. I haven't gone into example usage because it is too involved for an answer here. Search for "calendar table" will give lots of examples and a full explanation to ...


0

What you must do is increase the join_buffer_size. What is join_buffer_size ? The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full ...


0

You should give CREATE TABLE statements or at least info about what is indexed and how. But what I can gather from what you posted: JOIN on functions is bad, not indexable. If you cannot get rid of those substrings, you might store them precomputed in separate columns and index those (at least the dstchannel ones should be enough, because of current join ...


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


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


0

Rewrite the query as: select * from media me where performed_by='worker_id' and exists ( select 1 from media_resources_used au where au.media_id=me.media_id ); Can you please check if this is running any better?


0

After some time i figured out how to do this. Lets put as sample data 2 recurrent tournaments: One daily at 16:20 and another every sunday at 14:00 insert into challenge_schedule(challenge_type_id, game_type_id, quorum, frequency_minute, frequency_hour, frequency_dom, frequency_month, frequency_dow, frequency_year, description) values (1, 4, 10, 20, 16, ...


-4

If the group by clause is missing (could be a copy & paste error) , this could be the reason


0

Duration represents the total time from start to finish the request by SQL Server, which represents time to send over network, cpu time, waits, and execution. As Aaron mentioned once the client has received the data SQL Server has finished it's timing, and the client is then processing. This should not be part of the duration. One useful article I found


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


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


0

If we can assume a device table holding all devices of interest. Example: CREATE TABLE device (device_id int, device text); INSERT INTO device (device_id, device) VALUES (100, 'a') , (101, 'b') , (102, 'c'); The query can be very simple: SELECT d.device_id, g.point, g.dt_edit FROM device d , LATERAL ( SELECT point, dt_edit FROM gps_data ...


0

I don't think adding a new column will be efficiently and you'll need to do more things like adding TRIGGERs to update the field and you'll get the newColumnvalue in every row for the table UserRatingsForProductsTable. Information: create Table Products( ProductId INT(4), ProductName VARCHAR(75), Cost DECIMAL(10,2), PRIMARY KEY (`ProductId`)); ...


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


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


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


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


0

The EXPLAIN took so long because it evaluated the subquery. Perhaps the subquery took so long because of lack of INDEX(field, id). When deleting a large chunk of a table, it is often faster to copy everything that you want to keep into a new table, then use RENAME to swap tables. Or, you could do the deletes in chunks of 100-1000, walking through the ...


0

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field. I have a much better method. DROP TABLE IF EXISTS keys_to_keep; CREATE TABLE keys_to_keep ( id INT NOT NULL, PRIMARY KEY (id) ); INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field; CREATE TABLE mytable_new LIKE ...


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


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


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


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



Top 50 recent answers are included