Results 1 to 25 of 25

Thread: [RESOLVED] About to put 1.5 GB of images into a DB

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Resolved [RESOLVED] About to put 1.5 GB of images into a DB

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: About to put 1.5 GB of images into a DB

    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.
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  3. #3

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    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??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: About to put 1.5 GB of images into a DB

    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.
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  5. #5

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: About to put 1.5 GB of images into a DB

    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.
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  7. #7
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: About to put 1.5 GB of images into a DB

    I am interested in how things work out, keep us posted!!!!
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  8. #8
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yodaâ„¢
    Posts
    60,710

    Re: About to put 1.5 GB of images into a DB

    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.
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    Quote Originally Posted by RobDog888
    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.
    I guess we will be waiting for the first day of school to discover if this was one huge mistake!

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yodaâ„¢
    Posts
    60,710

    Re: About to put 1.5 GB of images into a DB

    Is it that important to protect the pr0n err... I mean "student" images
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  12. #12

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    @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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yodaâ„¢
    Posts
    60,710

    Re: About to put 1.5 GB of images into a DB

    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.
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  14. #14

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    Quote Originally Posted by RobDog888
    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).
    Robert - it's just eye candy. Silly.

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yodaâ„¢
    Posts
    60,710

    Re: About to put 1.5 GB of images into a DB

    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.

    I need to brush up on my SQL Sever stuff.
    VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    • Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  16. #16
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: About to put 1.5 GB of images into a DB

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Thumbs up Re: About to put 1.5 GB of images into a DB

    Quote Originally Posted by RobDog888
    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.

    I need to brush up on my SQL Sever stuff.

    I just picked up a copy, it seems like a very good book. Thanks Steve!
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  19. #19
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: About to put 1.5 GB of images into a DB

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  20. #20

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    Quote Originally Posted by techgnome
    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #21

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    Quote Originally Posted by Mark Gambo
    I just picked up a copy, it seems like a very good book. Thanks Steve!
    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: About to put 1.5 GB of images into a DB

    Quote Originally Posted by szlamany
    Could you explain that a bit further please - maybe a code snippet to look at? Thanks!
    If I can find the project where I did this, I'll post it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  23. #23

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: About to put 1.5 GB of images into a DB

    @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:

    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)
    )
    We store the image size that comes in from:

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  24. #24
    coder. Lord Orwell's Avatar
    Join Date
    Feb 2001
    Location
    Elberfeld, IN
    Posts
    7,621

    Re: [RESOLVED] About to put 1.5 GB of images into a DB

    thats a heck of a difference in file sizes. What was the deal? Were you only showing thumbnails of huge images?
    My light show youtube page (it's made the news) www.youtube.com/@artnet2twinkly
    Contact me on the socials www.facebook.com/lordorwell

  25. #25

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] About to put 1.5 GB of images into a DB

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width