May 1, 2009 at 11:56 am
I have a view which is based on two tables. table_live and table_archive. When I run the query using view is very slow but when I run the query using table_live is very fast.
I already checked the fragmentation on both the tables indexes and those were ok, means no fragmentation in the indexe.
I also checked the query plan and it's all seek on the tables.
I also recreated views.
I am just woundering if there is something related with the partitioned function in the tables. I recently added partitioned range in the table_live. Does that makes a difference in the performance ?
any help would be appreciated.
May 1, 2009 at 12:05 pm
Partitioning is not always a quick performance gain unfortunately. You need to make sure that the partition function you are using is really representative of how you are retrieving your data in order for it to be effective.
Were you using the view prior to partitioning the table and if so, was it performing well? If the answer is yes then most likely partitioning is not configured in a way which benefits the queries you use, or the view.
You could post the table design, partition scheme / function that you are using, the view and some of the queries. I'm sure you will get some feedback that will most likely help.
Aside from that remove the partitioning and reconsider what would be the proper configuration if at all.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 1, 2009 at 12:36 pm
Thanks for your reply, I have attached the query plan. May be it will help.
May 1, 2009 at 12:45 pm
I'm a pretty visual person so, could you attach the estimated (or actual) execution plan. Under Query > Display Estimated Execution Plan (or Cntrl L). Right click in the plan and select Save As. It should save it as a sqlplan file. Attach that and I can read it a whole lot better.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 1, 2009 at 1:16 pm
Sorry about that. Here is the attached sqlplan file.
I rename to zip file because this website does not upload sqlplan file. please rename back to sqlplan extention so that you can read.
May 1, 2009 at 1:27 pm
balbirsinghsodhi (5/1/2009)
Sorry about that. Here is the attached sqlplan file.I rename to zip file because this website does not upload sqlplan file. please rename back to sqlplan extention so that you can read.
I don't see the attachment.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 1, 2009 at 1:31 pm
I sent it again.
May 1, 2009 at 1:50 pm
I can't view that zip file. Not sure why. What are you zipping it with? If I just click on it I get an xml view of the plan in a web page but that again is not a visual plan and the plan is a bit verbose to read through. Can you save it as a sqlplan file?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 1, 2009 at 2:08 pm
are you renaming the zip file into sqlplan extention. ?
May 1, 2009 at 2:15 pm
Actually, It's not a zip file. I just rename the extension to zip so that I can upload into this website.
You just need to change from .zip file to .sqlplan file.
May 1, 2009 at 2:19 pm
David Benoit (5/1/2009)
I can't view that zip file. Not sure why. What are you zipping it with? If I just click on it I get an xml view of the plan in a web page but that again is not a visual plan and the plan is a bit verbose to read through. Can you save it as a sqlplan file?
Sorry about that. I did now and I am able to look at it. I'll get back to you. I am assuming that the transac "table" is actually the view over the transac_live and _archive. Is that correct?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 1, 2009 at 2:34 pm
Can you post information on the partition scheme and function as well? Did you partition both the _archive and _live tables?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 5, 2009 at 3:12 pm
Yes, Transac is a view based on two partitioned tables archive and live. It's really weird that sometime it is working slow and sometime works just fine. Long time back, I figured out that it is best practise to drop the partitioned view and recreate and I am doing that ever day.
Now, It's working fine but some time it gives us performance problem.
May 5, 2009 at 3:20 pm
balbirsinghsodhi (5/5/2009)
Yes, Transac is a view based on two partitioned tables archive and live. It's really weird that sometime it is working slow and sometime works just fine. Long time back, I figured out that it is best practise to drop the partitioned view and recreate and I am doing that ever day.Now, It's working fine but some time it gives us performance problem.
Is this a partitioned view or a partitioned table? Curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 5, 2009 at 3:46 pm
Transac is a view which is based on two partitioned tables. We access transac view for insert,delete and update.
Create view Transac
as
select * from transac_archive
union all
select * from transac_live
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply