Skip to main content
P
pixelwiz19801
Known Participant
February 8, 2011
Question

Data Compression in ColdFusion 9 for SQL Server 2008

  • February 8, 2011
  • 2 replies
  • 2102 views

Hi,

I need to store fairly large blocks of HTML text in SQL Server (I won't go into why, unless necessary).

Unfortunately even after stripping out the white space, the text string gets so long that the INSERT/UPDATE query times out.

I'm looking for a way to compress that text on the fly and send over just the compressed string.  Then uncompress it when it's retrieved from DB.

The only way I can think of doing it is using CFZIP to create a temporary zip file, then read it in and send that binary string to SQL Server.  But I don't know if that'll speed things up any, cause of all the file operations.  Ideally I would just do it in memory.

I also looked into GZIP for ColdFusion, but I don't think there is much difference.

Any suggestions?

    This topic has been closed for replies.

    2 replies

    R
    recursivecodes
    Participating Frequently
    February 8, 2011

    I'd start with some basic troubleshooting.

    Have you tried manually constructing the INSERT/UPDATE query and running it directly in your SQL Mgmt Studio?  Does the insert/update work?

    Does the form post timeout or the query?  Does a similar but slightly smaller request complete?  How long does it take?

    I can't really see an insert taking _that_ long to run.  I'd bet there is something else going on.

    But to answer the question - you can try something like http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=812 to flatten the whitespace from the HTML.

    PS... you are doing some kind of sanitizing to make sure there isn't any malicious scripts, etc in the HTML, right?

    P
    pixelwiz19801Author
    Known Participant
    February 8, 2011

    I am generating the HTML myself, in a way I'm using SQL to cache some frequently requests parts of pages, and the process that runs to generates these HTML snippets uses EventGateways that tell it when to run.  At peak times, there might be several requests coming in back to back.  So I can't have this insert query running for 15 seconds.

    It does run with smaller data chunks.

    I don't want to expand the timeout, because I need it to be fast, a lot faster than it is now, and the biggest thing that's slowing it down is the size of the string that's getting sent to the SQL Server from ColdFusion server, they are not on the same local network.

    I am already stripping out the white space, but that's not helping enough, I really need to have the string compressed and store the compressed string, but I can't figure out how to do it on the fly.

    Ben Nadel did some stuff in 2007 before CFZIP was an official tag where he was using java libraries to compress a data stream I think, but that's a little over my head.

    R
    recursivecodes
    Participating Frequently
    February 8, 2011

    Have you looked into the new caching features in cf 9?  Sounds like they would be pretty beneficial to you.

    WolfShade
    WolfShade
    Legend
    February 8, 2011

    Is the insert timing out?  Or is it truncating the data?  If it's timing out, I think you can extend the amount of time the CF server will wait by using a URL parameter; if it's truncating the data, do you have the datatype set to varchar(max)?

    ^_^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices