There's no mystery, you get a good(er) or (really) bad plan at basically random because there is no clear cut choice for the index to use. While compelling for the ORDER BY clause and thus avoid the sort, you non-clustered index on the datetime column is a very poor choice for this query. What would make a much better index for this query would be one on (serial_number, test_date)
. Even better, this would make a very good candidate for a clustered index key.
As a rule of thumb time series should be clustered by the time column, because the overwhelming majority of requests are interested in specific time ranges. If the data is also inherently partitioned on a column with low selectivity, like it seems to be the case with your serial_number, then this column should be added as the leftmost one in the clustered key definition.
It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script.
CLR Assembly:
- Must be installed into a database (both the assembly binary and the exposed wrapper method(s)).
- Requires setting up an external project in Visual Studio, something you may not be very familiar with.
- Very easy to integrate into your existing script.
- Debugging can be a bit involved, because you have to either debug locally (i.e., on the server), set up remote debugging, or set up a comprehensive test suite. All of these can be problematic for different reasons. (Note: setting up a test suite is a good idea, but if time is a factor and you've already received a tested chunk of code, it's much safer to skip it in this scenario than if you converted the code to T-SQL, for example.)
SSIS Script Component:
- Means running a totally separate process to do the calculation.
- Much easier to get started than with a separate assembly, where you have to provide all the boilerplate code. With SSIS, pretty much all you do is fill in the required method bodies.
- BIDS, which is x86, can't debug x64 scripts, so there's some ridiculousness that comes from that. (See here for a quick fix.) This would allow you to debug locally (on your workstation), though, which is an advantage.
While I haven't tested, I'll go out on a limb and say that the performance of both methods should be similar. If performance is a big concern, set up both scenarios and comparison test. In fact, you may want to set up both anyway just for practice, so you can see some of the differences and similarities for yourself, which is way more interesting and fun than reading about it (well, at least I think so).
A third option would be to move the process entirely to SSIS, which is more suited to migration/ETL types of tasks. This may or may not be feasible. You wouldn't necessarily need to retool the whole process, as you can, of course, execute arbitrary scripts.
Finally, another option is to convert the code to T-SQL. Depending on the complexity, though, it may be quite a challenge. Certainly there are manipulation tasks much better suited to using a proper programming language/framework like .NET. Without seeing the code itself, it's impossible to say if it would even be worth an attempt. As I mentioned previously, you would definitely need to develop a comprehensive test suite in this case.
Personally, I would probably create a CLR assembly because I'm strong on the programming side of things and it would be the fastest, least intrusive way to integrate the code. But that's me, and I haven't seen the code, so it may not be the best solution for you and your environment.
Best Answer
The time part is the fractional part and the date part the integer part, so if you date is genuinely just a date (no time part at all) and your time is genuinely just a time (no date part at all) you can simply combine them by adding them together.
For example:
The top SELECT shows what values represent today and 12 noon, and the second line adds them to make a single value representing today at 12 noon.