Avoid using rand() in where clauses

I was working today, building a procedure to populate some tables and do some data updates and deletes, for speed testing for one of my FDD sessions.

I decided to use the built-in rand() function of Firebird 2.x in the where clause of updates and deletes, but found out that the execution time was tooking too long to complete. Looking at statistics, it showed weird numbers for Indexed Reads, like, the updates of 5 records generating 80.000 indexed reads in the Primary Key index.

After some debugging, I just found out that rand() was the guilt! I moved it outside the update/delete statement, storing the result in a variable, and using the variable in the where clause. It worked million times faster!

So, moral of history: Don’t use rand() in the where clause of updates and deletes statements. And yep, I know this is quite unusual use 😉