July 19, 2011 at 3:36 pm
Hi...
I have some problems with the performance of my query.... I've a one table in this form
create MyTable
(
IdMyTable INT IDENTITY NOT NULL,
SomeFile VARBINARY(MAX) NOT NULL,
SystemDate SMALLDATETIME
)
HAVING A PRIMARY KEY (IdMyTable), a non clustered index (SomeFile), I'm doing an insert of some Files wich size is from 5 KB to 16 MB saving the data en the row SomeFile and everithing is Ok.... the problem is when i type this query...
Select SomeFile, SystemDate From MyTable With(nolock) the performance is not good the time to show me the results is like 3 minutes.... do u believe it!!!, do you have some idea to have a better performance ...
Thanks in advance and sorry for my English!!.....
Ciao
July 19, 2011 at 4:10 pm
I'm not surprised by the performance.. I have seen it several times. I believe it is tied to the fact that the blob data is present on some of the pages and not on others. Unfortunately to fix it requires the table to be completely rebuilt.
I fixed it by creating another file group and added a file to the database that contained only BLOB data. I called it Data_BLOB then I recreated the table with this added to the create statement: TEXTIMAGE_ON Data_BLOB.
Basically what happens is that sicne the table has a varbinary(max) column, its data will ALWAYS be placed in the file in the filegroup Data_BLOB, and the rest of the row in whatever filegroup is default (if you haven't set it to something else). It made a huge difference in my environment.
However, I want to be clear about something, you are effectively doing a SELECT * which means that it is going to try and pull the complete contents of that table over the net to you, this does not give you a real view of performance because in practice you should not be doing this.
CEWII
July 21, 2011 at 9:10 am
Hello
Thanks for sharing your experience with barbinary.
I will be creating the new table with the option of TEXTIMAGE_ON and creating a new group to see the performance.
Another question.
When I upload a 40MB file, making the conversion to binary which is the weight of my file in the database, it is just 40mb?.
thanks in advance!.
July 21, 2011 at 9:24 am
No, it will be more than 40MB when stored, each page is 8K or 8,192 bytes BUT sql will not use more than 8,060 bytes per page for data, the rest of overhead and internals.
So 40MB * 1,048,576 bytes per MB = 41,943,040 bytes / 8,060 bytes per data page = 5,204 data pages * 8,192 bytes per page = 42,631,168 bytes / 1,048,576 bytes per MB = 40.65625MB.
Keep in mind this is a very rough estimate but the storage for 40MB costs about 672KB in overhead.
Is any of that clear?
CEWII
July 21, 2011 at 9:49 am
That's a real answer, and based on what you mention is that when uploading a 40mb file in the database, the cost is approximately 672 kb file? ...
Incredible.
and really thank you very much.
😀
July 21, 2011 at 10:28 am
Those are the rough storage costs yes. If you are going to have a lot of files especially in the 40MB range you will very quickly have a very large database so keep in mind that maintenance of a VLDB is different that smaller databases. EVERYTHING takes longer. Breaking the BLOB data off into a seperate filegroup helps but big databases are a special challenge.
CEWII
July 21, 2011 at 12:49 pm
Hello Again!...
Just in some case I'll have some files with this size, normally my files will be since 1MB to 10 MB and in some case with 20 to 40 MB, and as u mentioned this is a big challange with restore the database, make de differents back up.
And thanks a lot for the advice you gave me another vision.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply