Good Key/Hash for SQL string

[chkaufmann 17]

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

[Vandrovnik 214]

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.

[FPiette 383]

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.

 

[Clément 148]

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.
 

[Stefan Glienke 2002]

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 March 8, 2021 by Stefan Glienke

[chkaufmann 17]

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