Timeline for all appointment based on interval distance
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 24, 2020 at 5:12 | comment | added | Masoud R | thank you. updated question why your well thought answer could still be the same. | |
| Sep 23, 2020 at 21:20 | comment | added | Dan Oberlam | (whoops, I meant to ask if you could edit your answer to include the relevant details & justification - I think it makes this a more complete answer). Yeah, TOP hasn't been an issue for me on its own. I have seen problems when used in a subquery/view in a way that picks very different plan shapes for 100 rows vs 1M, and I wonder if it might prevent minimal logging. For CCIs, see here. Sure - time & IO stats are great too. Regardless of your preference, showing either plans or time/stats (or both!) is helpful when asserting performance improvements | |
| Sep 22, 2020 at 22:50 | comment | added | Jason A. Long | 3) Execution plans are a useful tool but they aren't a substitute for an actual performance test. If I'm really trying to eek out every last millisecond, I'll post execution times and IO stats before execution plans (just my own preference). | |
| Sep 22, 2020 at 22:49 | comment | added | Jason A. Long | 1) This is true but I've never seen it cause a problem, not even when working with millions of rows. 2) I'm not familiar with what you're doing here but the paste a plan doesn't show the creation of the #CciForBatchMode temp table (so I don't know how you have it defined). To test, I simply created it with a single BIT column and 0 rows (just to make the code execute). In my test, #CciForBatchMode was ignored and both plans were identical in shape, subtree cost and performance. That said, I am interested in learning more about the technique. Please post links if you have any available. | |
| Sep 22, 2020 at 22:06 | comment | added | Dan Oberlam | It is - can you add it to the question? Other thoughts: 1. using a variable with TOP leads to an automatic cardinality estimate of 100 rows, which may have negative consequences depending on usage (RECOMPILE hint or dynamic SQL addresses this) 2. Enabling batch mode (fake join or query hint, depending on SQL Server version) is just a hair better, but probably not enough to matter 3. Including query plans to demonstrate the improvement is always nice :) brentozar.com/pastetheplan/?id=BkfrSxdSv | |
| Sep 22, 2020 at 21:37 | comment | added | Jason A. Long | @Dannnno it doesn't involve querying any tables, doesn't create, populate or pull from any #temp tables. Doesn't create any unnecessary rows and therefore does not need to be filtered and there is no sorting required. Is that sufficient justification? | |
| Sep 22, 2020 at 21:32 | comment | added | Dan Oberlam | Mind adding some justification as to why this performs better? | |
| Sep 22, 2020 at 21:05 | history | answered | Jason A. Long | CC BY-SA 4.0 |