January 31, 2002 at 11:49 pm
I am interested in finding out what others have experienced with partitioned views.
Has anyone got a comparison between a table select using an index hint compared with the same table split into member tables with a partitioned view (note you cannot have an index on partitioned view but you can on the member tables).
February 1, 2002 at 8:45 am
I've not dealt with them personally, as my environments aren't that large. However, to post the TPC benchmarks that SQL Server 2000 has garnered, Microsoft has used partitioned views. Here's an editorial from SQL Server magazine that describes a bit about it:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8541
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 1, 2002 at 10:24 am
I don't have benchmarks, but I remember going to some sessions when ss2k was being released. They showed how the server makes decisions and doesn't even send the query to the other servers if they don't have data (based on the key). Otherwise it does.
Keep in mind that you are adding overhead for the network transfer of some data twice (from server-server and server-you). But this is easily outweighed by the disk load times when the data is spread across more physical disks (on different servers). For large sets of data it is worth it. If you aren't in the high 10s or 100s of GB, I'm not sure it helps.
Steve Jones
February 1, 2002 at 9:40 pm
I asked the question about partitioned views (not the distributed type on federated servers).
Thanks for the response. I am familiar with the benchmarking and the way Federated SQL servers work.
What I am looking for is an indication from someone who has tried the 2 methods and knows from experience what the relative performance is.
The table I want to partition has 975 million rows and takes up about 120GB including indexes.
February 2, 2002 at 8:15 am
Here is SQL Server MVP Narayana Vyas Kondreddi's test on a smaller scale (5 million rows) and some of the performance improvements he saw:
http://vyaskn.tripod.com/federated.htm
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 2, 2002 at 6:51 pm
I read an article (cant remember where though!) about partitioned views. Apparently queries are still executed serially from 1 server to the next and not in parallel (which you would think would happen) - even in sk2 sp2. This seems to contradict Narayana Vyas Kondreddi's test. I havent had the time to check this and would be very interested in hearing the results.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
February 2, 2002 at 8:39 pm
Thanks again. The link http://vyaskn.tripod.com/federated.htm was interesting.
I will post my findings shortly on the performance of partitioned view compared to original table.
As yet I haven't had a chance to do comaprison of performance when table query used an index hint.
What might suprise everyone is that a delete from a partitioned view is slow compared to the same delete on original table. More later.
February 4, 2002 at 11:15 am
When you post, pls also let us know the physical as well as logical setup of the sever. Better yet, write it up as an article and send it to one of use (sjones, awarren, bknight @sqlservercentral-com).
Steve Jones
February 5, 2002 at 5:16 am
Personally I have seen improvements doing this (especially on larger tables). Also have seen bennifits by putting large tables into seperate filegroups even when the filegroup files are on the same drive.
February 5, 2002 at 4:52 pm
Thanks for the reply.
I am going to take Steve's advice and write an article. There doesn't appear to be much independent performance information on partitioned views and since I have to test anyway before I implement, an article may be helpful to others.
February 6, 2002 at 7:41 am
Please do write that article. I know quite a few of us are interested in your findings.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 3:44 pm
I am writing the article. Takes time, dependent on resources e.g. server availability. The test db I am working with is 120 GB.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply