January 20, 2009 at 11:31 am
I have created a view on two partitioned table. for example
create view viewname
as
select * from table1
union all
select * from table2
When I join this view with other table, it's slow but when I job table1 and table2 with other table separately,it's very fast.
I checked the execution plan, both are taking index seek.
any help will be appreciated
January 20, 2009 at 12:10 pm
sorry your question is a bit vague, I suggest you carefully read up BOL and the numerous articles written about partitioned views, it's not just a quesion of writing a view as you have posted!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 20, 2009 at 12:14 pm
There's more to execution plans than just whether or not they use index seeks. You might need to look at how the joins are being done, and so on.
Also, as already mentioned, there's more to partioned views than just using Union All. Do you have a column with a partion constraint on it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 2:06 pm
Yes, There is a constraint which partioned the data into two tables.
There are two tables Live and Archive, I keep only five years of data in Live table and all the old data in Archive table. Partitioned is based on period.
I created view on Live and Archive table for the user so that they can access view and join with other table. When they join this view with other table , it's very slow but when they join Live and Archive table with other tables, it's very fast.
I check the indexes. execution plan , missing indexes all are fine.
January 21, 2009 at 9:46 am
Can you post the execution plans of the fast queries and the execution plan of the slow query? The code for both of them would be helpful, too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 11:23 am
Here are both the queries and also IO stats. I have noticed one thing that view is using transac_archive table even the date range does not fall in Transac_Archive table. Both the tables are partitioned with a period.
select t.patient,pc.PROCEDURE_CODe,t.ser_dt,i.inv_num,Dim_Provider.Provider_Name
frominvoice i
inner join Idx..Transac t on
(i.group_zz=t.group_zz and i.Patient=t.patient and i.invoice=t.invoice)
inner join P4P..contenrollment ce on
i.Patient = ce.Patient
inner join idx..DN1 pc on
t.Proc_zz = pc.Record_Number
inner join idx_ops..dim_Provider Dim_Provider on
i.prov=Dim_Provider.Provider_Key
where(t.ser_dt >= '10/1/2007')
and(t.ser_dt <= '9/30/2008')
And pc.PROCEDURE_CODe In (select distinct code from P4P..h_measures_Cpt where id=32 and type='Num'
and des_code in ('MPM-B2','MPM-B3') and Description='CPT')
-------------------------------
select t.patient,pc.PROCEDURE_CODe,t.ser_dt,i.inv_num,Dim_Provider.Provider_Name
fromIdx..invoice i
inner join Idx..Transac_live t on
(i.group_zz=t.group_zz and i.Patient=t.patient and i.invoice=t.invoice)
inner join P4P..contenrollment ce on
i.Patient = ce.Patient
inner join idx..DN1 pc on
t.Proc_zz = pc.Record_Number
inner join idx_ops..dim_Provider Dim_Provider on
i.prov=Dim_Provider.Provider_Key
where(t.ser_dt >= '10/1/2007')
and(t.ser_dt <= '9/30/2008')
And pc.PROCEDURE_CODe In (select distinct code from P4P..h_measures_Cpt where id=32 and type='Num'
and des_code in ('MPM-B2','MPM-B3') and Description='CPT')
------------------
IO Status
----
--without view
Table 'ContEnrollment'. Scan count 9, logical reads 106, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'H_Measures_CPT'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TRANSAC_LIVE'. Scan count 315, logical reads 10692, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DN1'. Scan count 9, logical reads 2647, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_PROVIDER'. Scan count 0, logical reads 110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVOICE'. Scan count 1640,logical reads 6323, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- With view
Table 'DIM_PROVIDER'. Scan count 0, logical reads 145, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVOICE'. Scan count 1640, logical reads 7988, physical reads 81, read-ahead reads 239, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContEnrollment'. Scan count 1, logical reads 37, physical reads 1, read-ahead reads 35, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TRANSAC_LIVE'. Scan count 315, logical reads 9622357, physical reads 34074, read-ahead reads 1231301, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TRANSAC_ARCHIVE'.Scan count 45, logical reads 1601310, physical reads 13644, read-ahead reads 200198, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DN1'. Scan count 1, logical reads 2413, physical reads 1822, read-ahead reads 36, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'H_Measures_CPT'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
January 21, 2009 at 11:42 am
Did I miss the execution plans or are they not posted?
Also, I can't see where you have any difference in the From clause of the two selects. Does one use the partitioned view and the other doesn't?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 12:41 pm
How can I post the execution plan. ?
If you have noticed , first query is using view, the name of the view is Transac and the secound query is using directly table.
If we join Transac view with other table it is taking Transac_archive table no matter which post period it should put.
My understanding is when we join transact view which is partitioned, it should not look for Transac_archive table when the post period range is falling in Transac_live table.
January 21, 2009 at 12:50 pm
You are correct that it should not look at the other table if you criteria is such that it only points to the recent table. Having said that, if the engine does not see that view as partitioned (which my guess is by what you are saying that the engine does not) it will just see this as standard view and will scan all the tables associated with it. So, I would look through the criteria in BOL again to make sure that you are meeting ALL the criteria. I think that will take care of the issue.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 21, 2009 at 4:45 pm
The wired thing is, when I run the query with joining view first time it takes around 6 minutes but when I run same query second time it takes 33 mintues and then 19 minutes.
Why it's like that.
I
s there some thing got stored in the cache , whcih can be removed ?
January 21, 2009 at 5:08 pm
I have noticed one more thing. My partition is based on post_pd and the query I am filtering with SER_DT which not partition field.
so when I run the followng query using view and see the execution plan, I don't see query optimizer is looking for Transac_Archive table.
Select * from Transac where post_pd = '10804'
but when I run following query with filtering ser_dt and check the execution plan, query optimize is looking for Transac_Archive table because this field is not partitioned. both the dates falls in the transac_live table which is current.
Select * from Transac where SER_DT= '2008/04/01'
the wired thing is view runs slow first time and run faster secound and third time.
January 21, 2009 at 5:11 pm
Sorry , I did little mistake in previous reply.
When I run the query with joining view first time it takes around 6 minutes but when I run same query second time it takes 33 secound and then 19 secound
First time 6 mintue
secound time 33 secound
third time 19 secound
January 21, 2009 at 5:22 pm
Ah, that makes a whole lot more sense. Yes, probably many of the data pages are being cached. Based on the time differential between queries 1 and 2 I would say that you have some other indexing that you may want to consider as well. Just a guess. Add the following before the query;
set statistics io on
set statistics time on
Execute the query and look at the messages tab. There is probably a table that has a lot of reads against it. Then check out the execution plan and look for that table to see if it is using the most efficient index possible. Hopefully looking at an index seek. If you feel like it you could post the output of the statistics and an execution plan so that we can take a look further.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 21, 2009 at 5:34 pm
I already did that testing and I did see logical reads are so many but in the execution plan is taking clustered index seek.
I have attached the execution plan. you have to rename it's extention into sqlplan so that you can view it. this web site does not suppor sqlplan format to upload.
January 21, 2009 at 5:50 pm
Doing away with the two key lookups will probably save you quite a bit. I will take a look to see if I can provide some additional information tomorrow on what the index might need to be in order to get rid of those.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply