SQL Server Performance

partitioned view problem

Discussion in 'T-SQL Performance Tuning for Developers' started by hookla, Mar 15, 2006.

  1. hookla New Member

    Hi
    Im having trouble using a variable in a query on a partitioned view.
    the SQL below illustrates the prob. As you will see, selecting from the view using @x = 2 causes the DB to scan each table rather than look at just the one with the data....

    any help would be much appreciated!!

    regards

    Laurence


    create table a (
    x varchar(1) check ( x = '1' ) not null,
    amount money not null
    )
    go
    create table b (
    x varchar(1) check ( x = '2' ) not null,
    amount money not null
    )
    go

    create view vw as
    select * from a union all
    select * from b
    go

    set statistics io on
    go
    create proc p
    as

    select * from vw where x = '2'

    declare @x as varchar(1)
    set @x = '2'
    select * from vw where x = @x

    declare @sql as nvarchar(200)
    set @sql = 'select * from vw where x = ''' + cast(@x as varchar(1)) +''''
    exec sp_executesql @sql
    go


    exec p
  2. Adriaan New Member

    How can you expect a view that includes a UNION not to at least query the PK values for both parts? Especially with UNION ALL, which doesn't care about duplicates. You might try and see what happens if you drop the ALL keyword from the view definition.

    Is this a partitioned view? Sounds more like a "composite" view to me - but then I have an irrational aversion to views.
  3. hookla New Member

    quote:Originally posted by Adriaan

    How can you expect a view that includes a UNION not to at least query the PK values for both parts?

    that just how a partitioned view works... the query optimiser looks at the check constraints on the tables to work out which tables it should look in.. but my prob is that if you use a @variable it doesnt work....
  4. mmarovic Active Member

    Read carefully in BOL what conditions have to be met for horizontaly partitioned views. You didn't define any pk on tables a. If I remember well column x has to be part of tables primary keys.
  5. hookla New Member

    quote:Originally posted by mmarovic

    Read carefully in BOL what conditions have to be met for horizontaly partitioned views. You didn't define any pk on tables a. If I remember well column x has to be part of tables primary keys.

    for an updateable partitioned view the partition column should be in the pk but doesnt matter if you dont want to update or insert into it.
    inthe example code I show that when you say x = 2 it works fine.. its when you set @x = 2 and then say x = @x that it doesnt..
  6. BrentO New Member

    I've experienced that exact same problem, actually. The execution plan is designed without taking into account the variable.

    I found it in my stored procedures where I query the partitioned tables. I noticed that the first time the stored procs ran, they ran at a normal speed. The second and subsequent times, they ran horrendously slow. I finally realized that it was related to the variables I passed in. If I passed in the same variable that was used on the first pass, then it would run fast again.

    The solution for stored procedures with these types of parameters is to use the "WITH RECOMPILE" hint on the stored procedure so that it'll redo the execution plan for the particular variables that get passed in.

    It gets worse if you have to use joins, and the partitioned key field isn't explicitly specified as the standalone variable. For example, if you added a third table, table C, and joined from C to the view, and the fields in the C table determine which partitioned table gets hit, then it's going to scan all of the tables no matter what.
  7. hookla New Member

    but in my example above it scanning each table the first time too.. Im not changing the variable. thanks for the -with recompile- hint though because that would have been my next problem!
  8. mmarovic Active Member

    I did work with partitioned veiws a long time ago, so I don't remember much about that except that we needed a lot of time to make it work properly. Related to joins problem, maybe next link can helphttp://support.microsoft.com/kb/892935
  9. Adriaan New Member

    I smell parameter sniffing coming up ...

    In stored procedures, it often helps to copy parameter values into locally declared variables, and use the local variables for filtering, instead of the parameters.

    Unless there is a particular reason for using the view, why not let the stored procedure run the UNION query for you, including the filtering?
  10. hookla New Member

    quote:Originally posted by Adriaan

    I smell parameter sniffing coming up ...

    In stored procedures, it often helps to copy parameter values into locally declared variables, and use the local variables for filtering, instead of the parameters.

    Unless there is a particular reason for using the view, why not let the stored procedure run the UNION query for you, including the filtering?

    what u mean by "parameter sniffing"??

    it needs to use the partitioned view... the real tables and data are much more complicated butIve reduced it here to the core problem for simplicity...
  11. Adriaan New Member

    Parameter sniffing is a phenomenon associated with stored procedures.

    For stored procedures where performance degrades over time, you may find performance can be improved if you don't use the actual procedure parameters for filtering. You copy the parameter values into locally declared variables, and use those variables for filtering. This will often improve performance.

    Unfortunately for you, there is no such thing as a view parameter, so that is why I suggested using a stored procedure instead.

    There is nothing in a view that you cannot also do in a stored procedure - well, except that you can't have a trigger on a stored procedure.
  12. hookla New Member

    this needs to be a partitioned view though...
  13. Adriaan New Member

    Depends on whether the client app can work with the results of a stored procedure ...

    IOW does it need an updateable recordset, or can it process a disconnected rowset?
  14. hookla New Member

    we have 100 reports all looking at the same table which over time grew to be 100gig. we split this up into smaller tables by source system and month and are using a partitioned view to union them all together. the reports all pass in a date dimention and only report on one month at a time, but the partitioned view scans all tables when you use a variable instead of a literal. the example at the top of this post reduces it down to the core problem...
  15. Adriaan New Member

    As we are talking about reports, they probably will work fine on the resultset of a stored procedure. The problem would be that you might have to change to definition of those 100 reports - I can appreciate you may not have the time or resources to do that.

    But why not give it a try with one report, see if it can improve performance?

    So you create a test procedure, basically the same query as in your partitioned view, with some filter parameters and matching local variables to get the 'sniffing' going. Then you copy one of your reports, change it to use the stored procedure as its source, then run it a few times with some different filter parameters.
  16. mmarovic Active Member

    I think you already have the answer you were looking for. You can't use neither local variables nor sp parameters to achieve reading from just one table. Since you are talking about reporting, I guess using dynamic sql is acceptable solution. From security point of view make sure parameters are not taken from free text fields on the client.

Share This Page