1

I have a database that's used to store fingerprints. Under my conception this should be a small database but the problem is that the original developer was probably tripping on peyote and decided it would be a great idea to store the fingerprints in BLOB fields.

Thanks to this magic trick the database is now 3 GB in size and the frontend its crashing all the time, no wonder since a simple SELECT TOP(1000) takes about a minute from MSSMS.

So I'm left with a doubt about how to proceed. Is there a way I could reduce the size of the table containing the pictures? Or maybe another alternative to this madness.

Regards.

  • What is the version of SQL Server you're running? – Ali Razeghi Feb 23 '15 at 22:40
  • What exactly is the SELECT statement? Is there something stopping you from NOT including the BLOB field in your select list? – Jon of All Trades Feb 24 '15 at 0:00
  • Hello, the query that takes forever to load, on SQL 2005 btw, its the classic SELECT TOP 1000 f1, f2, f3, f4 FROM t1 – Nelz Feb 24 '15 at 11:25
2

There are a few approaches to handling this problem. Here are three options:

  1. Create a FingerprintBLOB table (perhaps in a different database) for the BLOBs of fingerprints then drop that column from theOriginalTable. That way when you SELECT TOP(1000) * FROM OriginalTable you will not need to read the BLOBs from the FingerprintBLOB table. This should greatly reduce the I/O requirements.

    If you want to manage the storage all from SQL Server, this would be the choice to make. It keeps the fingerprints in the database, but separates the I/O issues for general queries.

  2. Store each FingerprintBLOB as a separate file in a directory structure on disk. Keep the BLOB's \\Server\Share\Path\Filename in the OriginalTable.

    If you would like to keep the database space smaller and prefer to store on the file system then choose this approach. You can implement this on your own, which means you will need to manage the two separate backups: Database and File Structure.

  3. Or, a hybrid option is that you can use the SQL Server FILESTREAM which can be used to write files to non-database disk, but still provide a single SQL Server BACKUP and RESTORE process that backs up both kinds of data.

Any approach would be best done by creating new tables or a table and file structures. Then migrate the data to the appropriate locations. Once you have the data migrated you can adjust object names, if desired.

3

3GB is not really a lot of data. My laptop has 16GB memory, our datawarehouse is 3TB and that would be a fairly small warehouse. The maximum database size according to here is 524,272 TB!? So I think performance here is more about the kit you are on, plus index and query design.

You could refactor as Russell has suggested. However you could also maintain the original one-table design with appropriate indexing, either a non-clustered covering index, or even full-text indexing to support the 'search for record' type queries - this should not include the blob. Then only get the fingerprint images on an individual basis, when required by the application. To put this another way, do not include the blob column in SELECTs where it is not explicitly required. This will have the same effect as splitting the data into two tables without the redesign. Don't attempt to get all the fingerprints at once!

Please tell us more about the spec of kit you are on, the version of SQL Server you are running, the table DDL (including indexes) plus what type of queries the application normally runs.

  • Well, I'm working on a 4 GB Windows Server 2003 VM Running MSSQL 2005. As you can see scenario changes dramatically. Nevertheless I got the answer I was looking for, this will help me to sort the frontend/database communication speed. Additionally, I was under the impression that storing files its always better using a file system and storing the path in the database? – Nelz Feb 24 '15 at 11:38
  • 2
    I did see a rule of thumb on Bob Beauchemin's blog I think, if the file is > 1MB, store on filesystem, so I'm fine with that option, I understand where you are coming from. But where I'm coming from is, that's actually quite a large refactor. Let's say it's an afternoon's work for you, what's the regression test? Say a day? 2 days? Is this a business-critical app, or just occasional use? You will have to guarantee the app functions exactly as it did before, make new backup arrangements for the files etc. I think you could fix the performance without changing the whole thing, low risk. – wBob Feb 24 '15 at 11:54
  • Its a bit of both, nature of the information its critical but the usage its occasional. Maybe changing the settings of the image size using the SDK provided by the vendor changing the way the frontend works with the data so It don't take so long for it to show the 14 scans that stores per person. – Nelz Feb 24 '15 at 13:06
  • Sounds like there should be a one-to-many relationship between the 'person' and 'fingerprint' objects anyway. Good luck! – wBob Feb 24 '15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.