CODEWITHSUNDEEP
sqloptimizationprofiling
Ben Page
Ben Page

Reputation: 3100

Compare numbers excluding suffix in sql

ID numbers in my database have a code embedded in the last 3 characters. When comparing these ids for equality sometimes it is allowable to ignore the last 3 characters.

I.e. 12345001 == 12345009 should be true.

I usually do this with the following where clause:

where floor(id1/1000) == floor(id2/1000)

but in the case where I know id1 is stored in 'base' form where the last 3 digits are zero the following would also work:

where id1 == floor(id2/1000)*1000

It would also be possible with use of substring.

Q: Can anybody tell me from experience which of these three methods is the most efficient, or recommend the best (most accurate) way to profile these three different queries.

Thanks.

Upvotes: 0

Views: 154

Answers (1)

Alex K.
Alex K.

Reputation: 175816

Running either id value through a function will prevent any index use & the simple mathematic operations on integers would be faster than a cast to a character type followed by a substring comparison.

If you wanted to optimise this fully you could add a computed column to derive floor(id/1000), then index that and use it in your WHERE clause.

(Another one for you to benchmark abs(id1-id2)<1000 but basic series of mathematical transforms like this would only have temporal significance over huge datasets)

Upvotes: 1

Related Questions