Printable View
I've got a DB that's about 1.5 GB's right now.
We want to load 10000 images now - with a raw JPG size of about 1.5 GB's on disk.
So that's doubling the size of the DB - that does not scare me or scare the DBA in charge of things.
But it's making us think about using a different FILEGROUP for the StuPhoto_T table.
Opinions?
Experiences??
Thanks!
depends on how you access the DB as to whether that is a good idea. If everyone is accessing it over a LAN this is not a good idea. While it might not be quite as functional, you could simply have a link instead to the file in the db and have them click the link if they want to see it.
Sorry - I should have stated it's MS SQL 2005 - wan - about 1000 users.
Right now the VB client program opens a jpg in a folder on a share at the central office to display the student image. Users have the option to turn off image display.
Why do you think having the images in the DB will cause network issues - any different then getting the .JPG from the network share now??
because i would think that the data would be transmitted over the network whether or not it was displayed if it is actually in the db record (someone please correct me if i am wrong). But i can gaurantee it will slow down db searching.
That was my point about using a different FILEGROUP - MS SQL server allows for certain table data to be stored in different areas - even different drives - on a server. I've never done that before.
As for the network aspect - I cannot imagine how having this extra table with data in it will slow down a select that does not refer to this VARBINARY(MAX) field that we have created and stuck in a separate table.
well if the sql isn't calling it i don't think it will either. You made it sound like the sql would call it but the user could turn the display off.
I am interested in how things work out, keep us posted!!!!
It would place more of a resource drain on the sql server where as before with links you would be using the processing power of the server they were logged in on. This may be a issue or not depending on the amount of actual traffic that would be requesting the "Show picture" feature/option.
Can the SQL server handle more of a load? Maybe using the Profiler to establish a baseline before hte change and then rerun a new profile trace after and compare. but that should be tested before implemented and I dont see how you can accurately test it in a production scenerio without having the actual users using it first.
Well - so far I've written a down-and-dirty little VB app that wildcard searches through a folder with 10000 jpgs and loads them into the VARBINARY(MAX) field of this new table.
I've only loaded two images for testing so far - I want to nail down some of my questions and issues before putting 1.5 GB through the TRANSACTION LOG file! The person in charge of the photo folder on the network share is going to look into bulk dropping the filesize of some of the JPG's - they are very nice images - we don't need such heavy and large images.
The whole reason we are doing this is because we are about to release about 100 POCKET PC's that use MS SQL CE. We discovered that there was no easy native-way to encrypt an image on a PPC (zip namespace left that out on the compact framework!). But MS SQL CE does encrypt - so as long as the images are in the DB then encryption comes along as part of the package.
I guess we will be waiting for the first day of school to discover if this was one huge mistake!Quote:
Originally Posted by RobDog888
It's a brand new server - just installed it - just put SQL 2005 on it - it screams nicely ;)
But I like your idea of doing some base-line analysis before/after we do this.
Having the images on a different box has been a support problem when that server has been down - yadda - yadda. I usually promote the idea of storing the image path in the DB - but as I said in that prior post - it's time to test the waters on the dark side...
Is it that important to protect the pr0n err... I mean "student" images :D
@rd - I don't want a newpaper article about how a PPC that I've supplied was lost and then found on e-bay with little Sarah's kindergarten pictures!
Plus it all gets stored on a removable SD card...
Oh yes thats a very good point. ;)
But now Im curious, why or what is the need for having the pictures in the app in the first place (sorry if Im going off topic).
3 GBs is not that large for a business app database. I have a client with 5+ gigs. SQL can handle it if the server can so I wouldnt worry about the size but more on performance.
Robert - it's just eye candy. Silly.Quote:
Originally Posted by RobDog888
I can spend 60 hour weeks developing new functionality - incredible ease-of-use features - no one says a word. We put student images in the app - and the administrators - the secretaries - everyone is impressed.
I demo'd the PPC app last June - they all thought "interesting...I can see a student schedule while talking to Johnny who's cutting class out back." When I got to the point in the PPC demo where the picture popped up - I get oohs and ahhs!
As for the size issue - I've got customers as well with larger db's. Our concern is going from 1.5 to 3 GB with just one table - all VARBINARY data (a datatype I have no past experience with). That threw up a red flag. As I said earlier we are not afraid of this but wanted to know of others experiences with FILEGROUPS and various techniques for data segregation.
If I had time I would go buy Kalen Delaney's new books on Inside MS SQL Server 2005 - I read the SQL 2000 version - and that book was a wealth of great info...
Yes, its disturbing when users dont realize the real features that improve their use of your program.
Thanks for the book sugestion! I see its only $32.99 at amazon.
4 out of 5 stars.
I think I will order it. :thumb:
I need to brush up on my SQL Sever stuff. :(
We store a lot of our binary data (icons, and crystal reports) as blobs in our SQL database. Granted it's not in the realm of 1000's of files, but any one who has worked with Crystal knows they don't exactly produce small files.
At any rate, BLOBS are not stored inline with the table. It actually gets stored in a separate location within the DB file structure. What is stored in the table itself is a reference pointer to the binary data's location. Because of this, loading BLOBS (if I remember this right) actually bypasses the Translog.
But loading BLOBs isn't a quick operation though. (for obvious reasons). Getting them out of the database and into a usable file on the other hand isn't as slow as one would think. But I've only had to deal with one field, two at the most, and only one row. So my assessment maybe out of line.
-tg
Thanks for that TG - we only will ever serve back one image in a recordset at at a time - and my initial testing was that it was as fast as LOADPICTURE of a jpg file was from a network share (no real timing test done - just the look and feel of the operation).
My guess would be that it's actually much faster to get the image from SQL then open a file and use LOADPICTURE...
Quote:
Originally Posted by RobDog888
I just picked up a copy, it seems like a very good book. Thanks Steve!
If you are using .NET... you don't even need to load it from the driver either... You can open a memory stream and send the byte array to it, then load the image from the memory stream.
-tg
Could you explain that a bit further please - maybe a code snippet to look at? Thanks!Quote:
Originally Posted by techgnome
The SQL 2000 copy of this book was great. I personally am very interested in what goes on under the hood - and that book explained in clear details how the engine stores data and indexes and so much other valuable info that it makes you a better database engineer.Quote:
Originally Posted by Mark Gambo
If I can find the project where I did this, I'll post it.Quote:
Originally Posted by szlamany
-tg
@tg - I already got through the memory stream logic - so don't go nuts trying to lookup code snippets for me!
At any rate - this whole issue got a lot less monumental - as the DBA on-site managed to shrink the images from the 1.5 GB to around 62 MB.
Today we bulk loaded all these images - around 8000 of them. The VB program that loaded them went reasonably fast.
They are stored in a VARBINARY(MAX) field in a table with the following layout:
We store the image size that comes in from:Code:Create Table StuPhoto_T
(StuId int Not Null
,OrigFilename varchar(255) Not Null
,ImageSize int Not Null
,ActualImage varbinary(max) Not Null
,constraint PKStuPhoto_T
Primary Key Clustered (StuId)
)
lngBytSize = FileLen(gstrInput)
so that we can verify that the Len() of the ActualImage field matches the ImageSize value (actually for some reason it's off by 1).
This is the select we use to get images (in a SPROC):
Select ActualImage From StuPhoto_T Where StuId=@StuId and Len(ActualImage)=ImageSize+1
We discovered that if you take an ActualImage field and cut it in half it's got enough "jpg-memory-metadata" to attempt to load into an IMAGE control but will cause ugly memory errors when you exit the VB app. So that was our feable attempt to only load good-to-display images into controls.
We already took this far enough this morning so that these images are being downloaded to a POCKET PC and stored in an MS SQL CE database (encrypted - that was the whole reason for this exercise!). In that database the datatype is still IMAGE - as VARBINARY() has a max size of 500 on the CE platform.
thats a heck of a difference in file sizes. What was the deal? Were you only showing thumbnails of huge images?
Some of these were JPG's from a year-book type company of student photos. Large file size with heavy mega-pixels. Some were scaled down in prior years - others were current photos that were still large filesize.
Current logic had me grabbing them with LOADPICTURE and shrinking them to fit a small image control on the form.
I told the DBA what the "normal" display size was in my app and he managed to pre-shrink them on disk to closer to that size.