SQL Server Performance Forum – Threads Archive
partitioned view problemHi
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
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
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
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.
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….
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.
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..
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.
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!
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
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?
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…
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.
this needs to be a partitioned view though…
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?
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…
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.
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.