Hot answers tagged

27

Since you get the correct plan with the ORDER BY, maybe you could just roll your own TOP operator? SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER FROM ( SELECT dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER, ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum FROM ...


26

Try forcing a hash join* SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc INNER HASH JOIN CORRESPONDENCE_JOURNAL cj ON dc.DOCUMENT_ID = cj.DOCUMENT_ID AND dc.QUEUE_DATE <= GETDATE() AND dc.PRINT_LOCATION = 2 ORDER BY cj.FILE_NUMBER The ...


25

Edit: +1 works in this situation because it turns out that FILE_NUMBER is a zero-padded string version of an integer. A better solution here for strings is to append '' (the empty string), as appending a value can affect order, or for numbers to add something which is a constant but contains a non-deterministic function, such as sign(rand()+1). The idea of ...


8

While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in ...


8

The warning is there because of the XML function value(). The second parameter to value() is what you want the value stored in the XML to be converted to. You could argue that this is not in fact an implicit conversion but a very explicit conversion since you are asking for it to happen. Perhaps something for a connect item to suggest to Microsoft. Simplest ...


6

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag): ;WITH cte AS ( SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo, rn = ROW_NUMBER() ...


6

The greatest-n-per-group tag has a number of questions and answers relevant to this type of problem, with the canonical example for SQL Server being: Retrieving n rows per group With the two main options being: ROW_NUMBER (as in Aaron's answer); and APPLY So while the question is most likely a duplicate of that (from the point of view that the answer ...


6

The biggest difference in time in your execution plans is on the top node, the UPDATE itself. This suggests that most of your time is going to IO during the update. You could verify this by turning on track_io_timing and running the queries with EXPLAIN (ANALYZE, BUFFERS) The different plans are presenting rows to be updated in different orders. One is ...


6

Instead, try WHERE InsertedOn>=CAST(GETDATE() AS date) AND InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date)) This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the ...


5

I've read the results from this post and understand the concept of a Row Goal etc. What I'm curious about is how I can go about changing the query so that it uses the better plan Adding OPTION (QUERYTRACEON 4138) turns off the effect of row goals for that query only, without being overly prescriptive about the final plan, and will probably be the ...


5

Your CTE-based approach with window functions is a very good start. There's another, even more suitable window function that you could use: LAG(). Here's how: SELECT iVehicleMonitoringId AS CurrentID, LAG(iVehicleMonitoringId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousID, iAssetId AS CurrentAsset, LAG(iAssetId, 1) OVER (ORDER BY ...


5

I am not exactly sure why the selectivity of an equality predicate is so radically over-estimated by the GiST index on the tstzrange column. While that remains interesting per se, it seems irrelevant to your particular case. Since your UPDATE modifies one third (!) of all existing 3M rows, an index is not going to help at all. On the contrary, incrementally ...


3

This is the part of the execution plan where you expect an index being used: -> Seq Scan on paid gap (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1) Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= ...


3

You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL. To demonstrate, here is a simplified version based on the AdventureWorks sample database: The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of ...


3

The warning in the query plan means that because you have an implicit datatype conversion, SQL Server won't be able to accurately guess the correct number of rows returned, which in turn might lead to a less-than-optimal plan. This is important in queries that have to perform well, normally because they work with a lot of data, but in your situation, this ...


3

SQL Server 2012 introduced some missing Windowed Aggregate Functions including LEAD & LAG, e.g. Window Functions in SQL Server: Part 2-The Frame or How to Use Microsoft SQL Server 2012's Window Functions LAG allows to access data from previous rows without using a cursor: SELECT ... FROM ( SELECT ... DATEDIFF(second, dtUTCDateTime, ...


2

Please provide SHOW CREATE TABLE. Indexes needed: users_interest: (user_to, new) users_view: (user_to, new) mail_msg: (user_to, folder, new) users_block: (user_from) and (user_to, user_from) user: (activated, online) and (activated, last_visit) The construct NOT IN ( SELECT ... ) performs poorly; change it to a LEFT JOIN ... WHERE ... IS NULL. For ...


2

Mostly, this seems to be a misunderstanding. According to your query plan, you are retrieving rows=1410288 and the query itself is not that slow. It does not "take 224.9 seconds": Execution time: 697.753 ms You could probably improve the performance of your query some more by increasing the locality of clustered data, i.e., CLUSTER (or pg_repack) your ...


2

Since you're doing a TOP(1), I recommend making the ORDER BY deterministic for a start. At the very least this will ensure results are functionally predictable (always useful for regression testing). It looks like you need to add DC.D_ID and CJ.CORRESPONDENCE_ID for that. When looking at query plans, I sometimes find it instructive to simplify the query: ...


2

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL. A few points to get you started on the performance of your query: Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain. tblMetaData ...


2

Are you looking to use the existing methods already implemented in PostgreSQL, or to implement your own new indexing methods? PostgreSQL's implementation of b-tree indexes cannot index values longer than about 2712 (although it will compress the value if it is highly compressible, before failing due to size) unless you recompile PostgreSQL with a ...


2

As dnoeth mentions, you should be able to use lag to look at the previous value: SELECT ... , DATEDIFF(second, dtUTCDateTime, prev_dtUTCDateTime) AS DateDiffSeconds FROM ( SELECT ... , LAG(dtUTCDateTime) OVER (ORDER BY dtUTCDateTime) AS prev_dtUTCDateTime FROM VehicleMonitoringLog Where dtUTCDateTime > GetDate() - ...


2

type int(11) NOT NULL COMMENT '1. chatter, 2. marketing', -- Change to TINYINT UNSIGNED. This will save 3 bytes per row. Ditto for several other fields. And for those fields that are also in indexes, especially the PK, that "3" gets multiplied. bigint(20) unsigned -- If you are not expecting more than 4 billion, save 4 bytes each by switching to INT ...


2

You should consider using LEAD and LAG Analytic Functions and appropriate indices for sorting. I've changed one self-join query with them. Elapsed time was reduced tenfold.


2

According to the statistics provided user I/O wait time is 6172 out of db time 8383. When you see "user I/O" as a major wait event, SQL tuning is the best answer, specially adding missing indexes. Since the plan shows it is using full table scan you can add indexes in the column used in where clause.


2

Try creating the following index: CREATE INDEX s_contact_idx_001 ON s_contact_lupd_lastupdby_name ( last_upd, last_upd_by, last_name ); You should change the name to correspond to your naming convention and make sure that it is unique.


2

the query does not return 43 rows but 1 row that contains the number 43 the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects. from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table ...


1

One more thing. If these tables are very larges even if you have good indexes, you should updates these tables rows in cycle, because the transaction log is growing every time and big transactions takes more times. Example: CREATE TABLE UpdateTable(id int IDENTITY(1,1), name varchar(200), namenew varchar(200)) INSERT INTO UpdateTable VALUES ...


1

Query #1: You are missing indexes on sessions.ip and auth.session. The latter is really important because now during the join for each row in sessions (~2M) MySQL has to check all ~4M rows in the auth table instead of picking the one or few directly using the index. That means the query is maybe 1,000,000 times slower than it needs to be. You can make the ...


1

I don't really follow the examples but based just on: Each time an event of type=0 happens, a counter increases for the item involved, and each time an event of type=1 happens, it decreases again. The counter must always lie between 0 and 3 (inclusive). Easier if you just change event_type to -1 and 1 select item, min(seq) from ( select ...



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