Documents In VLDB

Hello All,

I have been experimenting with SQL Server 2005 partitions. I loaded a terabyte of information into 2 tables. The first holds the document information and the second holds the actual binary document (in this case pdf). Most of the documents are about 1 megabyte in size, but the largest is 212 megabytes.

SQL Server has no problem storing the blobs. The problem occurs when I attempt to get the data.

I did some quick tests to test how fast I could pull the documents out. The largest took about 24 seconds. The 1 meg documents are sub-second.

Here is how the 212 meg doc breaks down:

Time to load datatable: 18.79 seconds
Time to load byte array: 3.84 seconds
Time to Write and open document: 0.01 seconds

If I access the file from a file server, the time is 0.04 seconds to begin showing the document.

As you can see, the longest time period is related to retrieving the data from SQL, and it is much slower that launching it from disk across the network. (note: the sql server and file server used to test are next to each other).

My question is, how can I speed up the access from SQL Server? I believe the keys are "partition aligned". Any suggestions would be appreciated.

I will add the table definitions and partition information as a reply since only 5000 chars are allowed in the post.