Testimonial

"Bulk Manager is a truly indispensable product providing comprehensive file management for Umbraco. We have saved literally hundreds of hours of developer time using it."

Richard Barg, University of California

 

Examine DB

June 01, 2016

My packages mostly use SQL Server or one of the other supported databases in Umbraco. It's ideal for storing data and most of the times I don't worry about it.

A few months ago a client with very high traffic had an issue with performance in the redirect manager of SEOChecker, SEO Checker uses caching internally but there was a bot requesting 1000's of non-existing pages and each non existing page caused a few SQL queries to check if a redirect existed, or a redirect could be created based on url history.Because of the size of the database and the amount of queries this caused a performance issue on this site and got me thinking of other ways to query data.

Introducing Examine DB

Examine DB is nothing but a data dump from one or more sql tables to an examine index. You can specify a query and the result of this query will be added to the index. We did a little benchmark with just a few records from the Adventureworks database and already seeing that Examine is twice as fast as a normal SQL server.

Benchmark

Install

Examine DB is shipped as a NuGet packaget. Simply use the following command in your packagemanager console.

PM> Install-Package ExamineDB

Re-index single records

Besides indexing all results Examine DB comes with a small API that allows you to re-index a single record, or remove a single record without having to rebuild the entire index (which is not handy when you have 100.000's of records).

To test the (re-)indexing of records I wrote a small test project that you can configure as a dashboard control to test the (re-)indexing.

Examinedbtesttool

Source and documentation

 This project is on Github, all the source and documentation can be found here.

https://github.com/rsoeteman/ExamineDB