Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 29, 2014 3:16 PM
Points: 355, Visits: 1,881
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# - http://www.SQLsharp.com/
Post #948209
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse