We’ve been using Verity for years. Needless to say, there is a reason I don’t have hair on my head. 🙂
I can’t wait to move to Solr. But in the mean time, we made several enhancement to our indexing that I wanted to share.
First, we have close to 30,000 records (many with large text fields) that get indexed almost hourly. These records are constantly being updated.
Every several days, we would have corruption or locking issues. We decided that we would do a full purge and re-index nightly to fix this issue.
In doing 30,000 records, Verity would freak out. I mean, before we could simply load all into memory via CFQuery, and pass it to Verity. That soon became unwieldy, not because of memory issues (another topic), but because Verity would throw up.
So the first thing we did was look up an industrial strength SQL script to page through the records. Here is the process we use:
- Purge Index
- Use Special SQL Script to grab 500 records
- Update Index
- Grab next 500
- Update Index
- Repeat steps 4 and 5 till we get to our record count
This works like a charm. Well, until last night when we started getting errors from the database. We do lots of SQL Server replication (another topic!), and it seems the tables we were indexing were throwing dead lock errors. Basically SQL Server 2005 found two processes asking for a lock on similar data and killed our index process. First off, we use the (NOLOCK) flag next to each table in our query. That should have prevented the issue. I found so many search results of people complaining why SQL Server is just plain not smart enough to handle simple locks issues like this. Anyways, I digress.
To resolve the issue, I added two additional lines to the SQL query pulling the data:
SET DEADLOCK_PRIORITY HIGH
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Hope this helps folks having similar issues.