aeternam (5/5/2010)
Using a while loop in combination with a table variable shouldn't, in general, be the best performing option.How would this query perform:
SELECT <something>
FROM <the partitioned table>
WHERE etp.PartitionID <= 10
..Håvard..
Hello Håvard. Yes, the WHILE loop shouldn't be the best performing option, but sadly it is (again outside of the non-partitioned index; but which solution is best for you depends on your situation). I tried your idea of using PartitionID <= 10 and it had the same effect as using the IN list as well as not using any WHERE condition at all: they all take 10 - 11 seconds for each run. Again, :(.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR