The first run of following query takes about 45 seconds and all the next ones only 1 second. Can you please explain what is the reason and how is it possible to make query run faster on the first execute too?
OPEN SYMMETRIC KEY TEST_KEY DECRYPTION BY PASSWORD='password'
SELECT this.ID FROM SeparationFiles this
INNER JOIN BarcodeValues b ON this.FIRST_BARCODE_ID = b.ID
WHERE DecryptByKey(b.ENCRYPTED_VALUE, 0) = 'Peter'
Here is execution plan:
How many rows in the SeparationFiles table? You've got 50% of the cost of your query in scanning the SeparationFiles table. Looks to me like you might not have an index on the FIRST_BARCODE_ID column. And if you do have an index on that column, you might need to INCLUDE the ID column in the index.
The first time this query is run, the table, or perhaps some part of it, is cached in memory. The second time it's read from memory. This probably explains why it's faster the second time. But it appears to me the real problem is an index problem, depending on the number of rows in the SeparationFiles table.
As Randy says, the first time you run this the data from the SeparationFiles table is probably read from disk and cached. The next time you execute the query the data is in memory and is thus much quicker.
As Randy points out you should look at changing the query so that it uses an index seek, the problem you have is that your where clause is non-SARGable and so wouldn't use an index anyway. You should probably change the where clause to:
Where b.encrypted_value = EncryptByKey(key_GUID('password'), 'Peter')
Then make sure you have an index on encrypted_value
