Dormant Full Text Index in SQL Server

I recently created a full text index to improve search performance on some large text fields. The resulting performance was great, so I took it to show my boss (an IT "Bruno").

I had to wait a few minutes before I could see him, and then when I fired up my lovely new search page disaster struck in the form of a timeout - making me look like an imbecile.

I covered by showing him something shiny, and hastily hit refresh. The search came back in less than a second, as did each subsequent search. Happy boss.

When I got back to my desk I did another search and got another timeout. It seems my Full Text Index is going to sleep if not used for 5 minutes, and then takes 30 seconds or more to wake up. Rubbish!

So, I have had to set up a job to hit the FTI with a simple query every 5 minutes, keeping it "awake".

Something like this:

SELECT * FROM Dreams WHERE CONTAINS(SubText, 'fear of trombones')

Doesn't feel great having to do that, but now my Full Text Index is always ready for action.

Optional Search Parameters in SQL Server Stored Procedures

I often create Stored Procedures in SQL Server for searching - but what is the best way to handle optional search parameters?

In the old days I wrote queries like this:

SELECT *
FROM Chimps
WHERE (@Age = 0 OR Age = @Age)
AND (@Weight = 0 OR Weight = @Weight)

... defaulting @Age and @Weight  to zero if not specified.

However, I later found that this performs a lot better:

SELECT *
FROM Chimps
WHERE Age = ISNULL(@Age, Age)
AND Weight = ISNULL(@Weight, Weight)

... with the params defaulting to NULL if not specified. Lovely.

But wait! What if there is a chance that some of our chimps have a NULL value for Age or Weight? Then we would have to go a bit further:

SELECT *
FROM Chimps
WHERE ISNULL(Age, 0) = COALESCE(@Age, Age, 0)
AND ISNULL(Weight, 0) = COALESCE(@Weight, Weight, 0)

This still performs well in most of my scenarios, but let's face it - ideally our chimps will not have a NULL weight. Weigh those chimps!