locked
sql 2008 optimization issue RRS feed

  • Question

  • Hi All

    I have a parittioned tabled that is partitioned by a smallint column called "timeseriesid" which is a numerical representation of a date value.

    if I try to retrieve data from my partitioned table using the timeseriesid that it is partitioned by as a filter ( ie slect * from table where timeseriesid = 1 ) it works really fast as it makes use of the partiioning stratergry to find all data for that timeseriesid  only searching the single file containing relevant results.

    If I try to perform and aggerate function on the timeseriesid column ie select min(timeseriesid) or select max(timesereisid) from the partitioned table it does not make use of the partition information and scans every row across all files on which the table is partitioned

    In sql 2005 this was not the case.

    The table in question contains 3 billions of records so as you can imagine the query takes 30 Minutes to run.

    In SQL 2005 the same query completes in 7 seconds.

    Is this a bug that has been introduced in sql 2008 ?  ( this is not caused by index fragmentation, missing statistics or the likes I have already checked this as the query generates a different plan on SQL 2008)

    Thanks in advance for your assistance

    Regards

                   Brian    

     

     

     

    Friday, January 21, 2011 2:57 PM

Answers

  • Brian

    Do you mean running SELECT MAX(timesereisid) FROM tbl WHERE timesereisid=1 SQL Server does not use an index on partition column?

    Based on Dan's DDL I did quckly test and it DOES use an index 

     

    --Create a partition function with boundary points for each month
    CREATE PARTITION FUNCTION partfunc (datetime)
    AS RANGE RIGHT
    FOR VALUES ('1/1/2005','2/1/2005','3/1/2005','4/1/2005','5/1/2005','6/1/2005',
          '7/1/2005','8/1/2005','9/1/2005','10/1/2005','11/1/2005','12/1/2005')
    GO
    ---Execute the following command to view the results of step 4:

    SELECT * FROM sys.partition_range_values;
    ---Create a partition scheme mapped to the partition function, as follows:

    CREATE PARTITION SCHEME partscheme
    AS PARTITION partfunc
    ALL TO ([PRIMARY])
    GO
    --View the partition scheme
    SELECT * FROM sys.partition_schemes;
    ---Create an Orders table on the partition scheme:

    CREATE TABLE dbo.orders (
    OrderID        int      identity(1,1),
        OrderDate      datetime NOT NULL,
        OrderAmount    money    NOT NULL
    CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (OrderDate,OrderID))
    ON partscheme(OrderDate)
    GO
    ---Populate some data into the Orders table by executing the following code:

    SET NOCOUNT ON
    DECLARE @month  int,
            @day    int

    SET @month = 1
    SET @day = 1

    WHILE @month <= 12
    BEGIN
        WHILE @day <= 28
        BEGIN
            INSERT dbo.orders (OrderDate, OrderAmount)
            SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2)) + '/2005',
    @day * 20

            SET @day = @day + 1
        END

        SET @day = 1
        SET @month = @month + 1
    END
    GO
    --View the basic data distribution by executing the following:

    SELECT * FROM sys.partitions
    WHERE object_id = OBJECT_ID('dbo.orders')
    ---Return the data for a specific partition by executing the following:


    SELECT MAX(OrderDate) FROM dbo.orders where OrderDate>='2005-03-01 00:00:00.000'
    AND OrderDate<'2005-03-31 00:00:00.000'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 23, 2011 8:13 AM