New answers tagged

1

I can only speculate what the people meant. Maybe it's related to how MySQL computes index statistics depending on n. The only case that comes to my mind is whether n is higher than 127 or not. As you may know InnoDB stores a string with its length. The length is stored in so called offset fields. The offset can be either one or two bytes. In REDUNDANT ...


1

Is going to be hard to find something that excels both at data ingress (accepting +50k rows per second) and ad-hoc querying an arbitrary EAV time series (timestmap, signal_id, signal_value). I would give clustered columnstore a try. Clustered columnstore would leverage segment elimination on timestamp and clustered columnstores also have better concurrency ...


1

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you. Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes. You will have the added advantage of taking a bit of ...


0

Querying the table in any way takes eons. While queries are running against the table, the BCP processing basically stops. Its like all processing power goes to the query and I get a files backlog. How do I speed up query performance? I should note that rows in this table will never be updated or deleted. Also, it's possible that data could be inserted ...


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


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


25

+1 No, I don't mean I'm agreeing with anything, I mean that as a solution. If you change your query to ORDER BY cj.FILE_NUMBER + 1 then the TOP 1 will behave differently. You see, with the small row goal in place for an ordered query, the system will try to consume the data in order, to avoid having a Sort operator. It will also avoid building a hash ...


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



Top 50 recent answers are included