1

I have about thirty-thousand Binary records, all compressed using GZIP, and I need to search the contents of each document for a specified keyword. Currently, I am downloading and extracting all documents at startup. This works well enough, but I expect to be adding another ten-thousand each year. Ideally, I would like to perform a SELECT statement on the Binary column itself, but I have no idea how to go about it, or if this is even possible. I would like to perform this transaction with the least possible amount of data leaving the server. Any help would be appreciated.

EDIT: The Sql records are not compressed. What I mean to say is that I'm compressing the data locally and uploading the compressed files to a SQL Server column of Binary data type. I'm looking for a way to query that compressed data without downloading and decompressing each and every document. The data was stored this way to minimize overhead and reduce transfer cost, but the data must also be queried. It looks like I may have to store two versions of the data on the server, one compressed to be downloaded by the user, and one decompressed to allow search operations to be performed. Is there a more efficient approach?

Improve this question
1
  • You should tell us why you chose to store the data in this compressed form. What kind of limitations you have. Without it the answers may be misleading. Commented Jun 19, 2016 at 0:58

2 Answers 2

Reset to default
2

SQL Server has a Full-Text Search feature. It will not work on the data that you compressed in your application, of course. You have to store it in plain-text in the database. But, it is designed specifically for this kind of search, so performance should be good.

SQL Server can also compress the data in rows or in pages, but this feature is not available in every edition of SQL Server. For more information, see Features Supported by the Editions of SQL Server. You have to measure the impact of compression on your queries.

Another possibility is to write your own CLR functions that would work on the server - load the compressed binary column, decompress it and do the search. Most likely performance would be worse than using the built-in features.

Taking your updated question into account.

I think your idea to store two versions of the data is good.

Store compressed binary data for efficient transfer to and from the server.

Store secondary copy of the data in an uncompressed format with proper indexes (consider full-text indexes) for efficient search by keywords.

Consider using CLR function to help during inserts. You can transfer only compressed data to the server, then call CLR function that would decompress it on the server and populate the secondary table with uncompressed data and indexes.

Thus you'll have both efficient storage/retrieval plus efficient searches at the expense of the extra storage on the server. You can think of that extra storage as an extra structure for the index that helps with searches.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The CLR however can be way more beneficial than you are giving it credit for here because you can thread the task and use server resources which are presumably way better than his local machine! I think you and @Roland are on the right track of steering Jace away from bloating his sql databse with binary data that is essentially useless in the form and at the location it is.
Might be worth noting that binary lobs (i.e. varbinary(max)) stored in the database is not applicable for the row/page compression described, as it is stored in separate space from the non-max data.
0

Why compressing 30,000 or 40,000 records? Does not sound like a whole lot of data, of course depending of the average size of a record.

For keyword searching, you should not compress the database records. But to save on disk space, in most operating systems, it is possible to compress data on the file level, without the SQL Server even noticing.

update:

As Vladimir pointed out, SQL Server does not run on a compressed file system. Then you could store that data in TWO columns: once uncompressed, for keyword searching, and once compressed, for improved data transfer.

Storing data in a separate searchable column is not uncommon. For example, if you want to search on a combination of fields, you might as well store that combination in a search column, so that you could index that column to accelerate searching. In your case, you might store the data in the search column all lower cased, and with accented characters converted to ascii, and add an index, to accelerate case-insensitive searching on ascii keywords.

In fact, Vladimir already suggested this.

Improve this answer

12 Comments

Yes, that is what I'm doing. I'm compressing the data locally and uploading the compressed files to a SQL Server column of Binary data type. I'll amend my question.
What I mean is a table with an uncompressed string (varchar) column, so that searching for keywords is trivial. To save disk space, store the database file on a compressed file system. Just do not use the binary column type.
I'm afraid I don't have that privilege. File system is presented as is, and modifications are not permissible.
I suppose it might not be possible, but it's worth putting out there in case anyone has ever come across this situation.
@Roland, SQL Server databases are not supported on compressed volumes
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.