Jump to content
chkaufmann

Good Key/Hash for SQL string

Recommended Posts

I use a cache for sql query results. For this I create a key with THashBobJenkins for the sql string and then I have dictionary with the results with these hashes as key.

 

Unfortunately I run into a problem that two different sql strings give the same hashkey. So I probably have to use the whole sql string as key. Or can somebody provide me a good solution to reduce the sql string to a "smaller" key without loosing uniqueness?

 

Christian

Share this post


Link to post

If memory taken by whole strings is not a problem, I would use whole strings. (Or lossless compression of this string.)

Do you include parameters into this hash? Because SELECT a.Value FROM Table a WHERE (a.Id=:Id) can produce different results for different parameters.

  • Like 1

Share this post


Link to post

Almost no hash avoid duplicate values. Only the full data, maybe lossless compressed, use as a key has no duplicate.

The hash used as a key in a dictionary is interesting to speed up search for previously executed request. The dictionary should contain the list of all requests having that hash. Once you found the list, using the hash, you can iterate it to find the request. The smaller hash you use, the more duplicates you'll have.

 

Share this post


Link to post

Why can't you use MD5?
A TObjectDictionary< String, TDataset > where "String" is MD5( _Your_SQL_String_With_All_Parameters_evaluated ) and TDataset the structure for the result.
The Dictionary should be created with OwnValues parameter, this way the TDataset instances will be freed when the dictionary is destroyed.
 

Share this post


Link to post

Whats the problem in simply using a TDictionary<string,queryresult> where the key is the sql statement - even if there is a hash collision the hashtable handles it.

I doubt that the extra comparisons of the colliding SQL strings would affect performance noticably.

Edited by Stefan Glienke

Share this post


Link to post

I use the full sql string as key now. Maybe I'm just too old - sometimes still thinking that having several thousand strings as keys may affect performance...

 

Christian

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×