Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

Improving Performance of Cross-Partition Queries Expand / Collapse
Posted Tuesday, July 6, 2010 3:11 PM

Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 11:37 AM
Points: 526, Visits: 2,894
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# -
Post #948209
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse