Substituting a foreign key for a LongText Field (to save space) causes error: Cannot perform join, group, sort, or indexed restriction

Question

I have a table called JobQueue that contains a field named Command of type LongText.

Since many rows contain the same value for Command (often about 2000 characters long) I would like to replace the Command field in the JobQueue table with a foreign key called idCommand that references the id field in table Text_1G. So instead of storing the same 2000 character string over and over again I can just store an integer in idCommand (over and over again) to point to a single copy of the 2000 (or more) character command.

So I

CREATE TABLE Text_1G (id AUTOINCREMENT PRIMARY KEY, [text] LONGTEXT) 
CREATE UNIQUE INDEX idxText_1G ON Text_1G ([text] ASC)

So I was able to successfully insert a row into to Text_1G but when I tried to (left) JOIN JobQueue and Text_1G via the foreign key idCommand, I got this error:

System.Data.OleDb.OleDbException: Cannot perform join, group, sort, or indexed restriction. A value being searched or sorted on is too long

This left join seems to work for shorter strings, but not long ones...

Shucks... Is there a workaround for this problem?

I'm using Access version 16.0.13801.20240.

Answer 1

Not really, Access can't join on long text (memo) fields.

A common workaround is to truncate the field before joining, e.g. ON LEFT(LongTextField, 255) = LEFT(ForeignLongTextField, 255). However, this may cause unintended matches, especially when using code as the key, since there may be a difference beyond the first 255 characters.

A more difficult, but more proper solution is to use a hash as the join key, and join on the hash, e.g.

ON SHA1(LongTextField) = SHA1(ForeignLongTextField)

However, this requires that you can use a hashing function, and Access has no built-in support for hashing. Since you seem to use .Net, you can hash in the application layer, store the hashes in the database, and then use the hashes to make the join. If you use Access itself, you can perform the hashing in VBA, and use that in the query, however, that comes with a large performance penalty and requires you to implement fast hashing.

Note that since the hash is not used for security but for hashing, I generally prefer using a hash with a small result above one that provides good security, so I tend to use MD5 or SHA1.