fat wallet (6/29/2010)
this author has no idea about execution plan. He just checked statistical IO and did not analyse the execution plan.Select min(field1) from partitionedtable1
The above statement on a partitioned table does not have any conditions and 100% will cause a clustered index scan on all partitions. That is why it is slow. What you need to do is to add condition to the query and also add appropriate index on field1
...
Hello. Thanks for commenting but I did check the execution plan (as is noted in the article by the cost estimates). And the suggestion to add an index was given by Paul in Comment #2. However, in the context of my particular situation (and I assume for others as well), the cost of adding an index (in terms of space used and degradation of DML operations for several 90+ million row tables) is not worth it compared to adding this (or the CROSS APPLY) logic to the three Procs that make use of the MIN() / MAX() functions.
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