SQL Server Performance

Partitioned View Issue

Discussion in 'T-SQL Performance Tuning for Developers' started by asims, Jan 7, 2003.

  1. asims New Member

    I have a partitioned view that when run using static values searches only the appropriate member table it should. However, when the static values are changed to variables and those variables assigned values, it (the query optimizer) searches all the member tables in the view, even though the sql itself is exactly the same. This is very easy to reproduce. The following script will create 2 small tables with the correct partitioning criteria, a view over the tables and then insert a few rows into each member table. Lastly, run the sql at the bottom and then check the plan produced by the query. You can see that all member tables are checked when variables are used instead of static values. This is not a big deal with a small # of rows in a table...however, the member tables we are dealing with have 2-6 million rows each so I don't want the optimizer checking each one when I have already told it where to find the data.

    What's the deal?

    Many thanks for any help with this.

    /***** Script start ******/
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tst1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tst1]
    GO

    CREATE TABLE [dbo].[tst1] (
    [checkDate] [int] NOT NULL ,
    [state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [city] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tst1] WITH CHECK ADD
    CONSTRAINT [PK_tst1] PRIMARY KEY CLUSTERED
    (
    [checkDate],
    [state],
    [city]
    ) ON [PRIMARY],
    CONSTRAINT [CK_tst1] CHECK ([CheckDate] = 20021)
    GO
    /*************************/
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tst2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tst2]
    GO

    CREATE TABLE [dbo].[tst2] (
    [checkDate] [int] NOT NULL ,
    [state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [city] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[tst2] WITH CHECK ADD
    CONSTRAINT [PK_tst2] PRIMARY KEY CLUSTERED
    (
    [checkDate],
    [state],
    [city]
    ) ON [PRIMARY],
    CONSTRAINT [CK_tst2] CHECK ([CheckDate] = 20022)
    GO
    /**************/
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vuTst]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [dbo].[vuTst]
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE
    View vuTst
    AS
    SELECT * FROM tst1
    UNION ALL
    SELECT * FROM tst2

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    /***************/
    insert into tst2 values(20022,'colorado','denver','88277')
    insert into tst2 values(20022,'colorado','durango','88277')
    insert into tst2 values(20022,'colorado','leadville','88277')
    insert into tst2 values(20022,'colorado','vail','88277')
    insert into tst2 values(20022,'colorado','aspen','88277')
    insert into tst2 values(20022,'colorado','vista','88277')
    insert into tst2 values(20022,'colorado','col sprngs','88277')
    insert into tst2 values(20022,'texas','dallas','75207')
    insert into tst2 values(20022,'texas','ft. worth','76204')
    insert into tst2 values(20022,'texas','abilene','78277')
    insert into tst2 values(20022,'texas','amarillo','74907')
    insert into tst2 values(20022,'texas','houston','88307')
    /****************************/
    insert into tst1 values(20021,'colorado','denver','88277')
    insert into tst1 values(20021,'colorado','durango','88277')
    insert into tst1 values(20021,'colorado','leadville','88277')
    insert into tst1 values(20021,'colorado','vail','88277')
    insert into tst1 values(20021,'colorado','aspen','88277')
    insert into tst1 values(20021,'colorado','vista','88277')
    insert into tst1 values(20021,'colorado','col sprngs','88277')
    insert into tst1 values(20021,'texas','dallas','75207')
    insert into tst1 values(20021,'texas','ft. worth','76204')
    insert into tst1 values(20021,'texas','abilene','78277')
    insert into tst1 values(20021,'texas','amarillo','74907')
    insert into tst1 values(20021,'texas','houston','88307')
    insert into tst1 values(20021,'texas','austin','88307')
    insert into tst1 values(20021,'texas','el paso','88307')
    insert into tst1 values(20021,'texas','san anton','88307')
    /***** Script end ******/

    declare @state varchar(10)
    declare @checkdate int
    declare @city varchar(10)
    set @city = 'dallas'
    set @checkdate = 20021
    set @state = 'texas'
    select * from vuTst
    where checkDate = 20021 --@checkdate
    and state = 'texas' --@state
    and city = 'austin' --@city

    /* this sql checks for untrusted partitioning columns */
    SELECT name, status FROM sysobjects WHERE xtype = 'C' AND status &0x800 = 0x800



  2. bradmcgehee New Member

    What is the 'lazy schema validation' set to?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. asims New Member

    Not sure...but I will check. Why would this have any effect?

    Isn't that option primarily for remote servers? This view is entirely local.

    Thanks.
  4. bradmcgehee New Member

    Yes, this is for remote servers, as I understand it. I assumed that this was your case, as the benefits of using partioned views on a local SQL Server aren't a whole lot? What benefits do expect to get from using local partioned views?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. asims New Member

    What I hope for is this:

    imagine 10 tables each with 5 million rows in them.
    tbl200201, tbl200202, etc. Each table has exactly the same definition and each has a column defined as the "partitioning column".

    I build a view of the syntax:
    select * from tbl200201
    UNION ALL
    select * from tbl200202
    UNION ALL
    etc.

    I then select from the view instead of the underlying member table and give the optimizer enough information (by use of the partitioning column) to know which specific member table contains the data I'm looking for. The optimizer identifies the correct member table and issues the SQL against it rather than against EACH member table.

    This is how Microsoft describes the use of partitioned views. I think the reasoning is that a select against 5 million rows will always be faster than a select against 50 million.


    What I'm finding is that when using static values to issue the select, it works as advertised. The plan shows that the optimizer correctly identified the proper member table and issued the select against it *ONLY*. When using variables however, the plan generated shows that the optimizer checked all member tables in search of the data. It doesn't make sense.

    PS. The performance difference in this small test is inconsequential. When you're looking through 50 million rows it is definitely noticeable.

    Thanks again for your help.
  6. bradmcgehee New Member

    What you describe sounds reasonable, but I don't have any personal experience with it, so I can't offer any solutions now. Has anyone else done this before? If I have time, I will try to research this topic and see what I can find.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  7. asims New Member

    http://www.sqlmag.com/forums/messageview.cfm?catid=12&threadid=7904

    The above link contained an article dealing with this issue. It looks like the optimizer is choosing the correct table after all, though only at runtime. At SQL-parse time, a literal constant can be evaluated immediately and the correct table chosen with no further effort. However when a variable is used it is evaluated only at runtime...thus the 2 plans.

    I am going to verify that this is the case.
  8. bradmcgehee New Member

    Please let us know, this is very interesting.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page