Faster Filter Queries

We should all now be indoctrinated enough to know the holy trinity of fast queries in .Net:

• Stored procedures – NOT inline SQL
• Table indexes – see your DBA if you’re unsure
• Data Readers (if your data is read only)

I am also a strong proponent of the Microsoft Application Blocks, especially the Data Application Block. You will save yourself a lot of time, your code will be cleaner and easier to read, and your data activity will be optimized. (download here).

So that’s all good, but what I really want to talk about is an optimization that Nuri showed me for the query itself.

I was writing a standard read-only report on movie data in a GridView, and I had several filters for the users, such as date range, metro area, film, etc. My query passed in 0 (zero) for all filters that were unused. My initial WHERE clause looked like this:

WHERE (o.PostedDate BETWEEN @begindate AND DATEADD(d,1,@enddate) )
AND (o.FilmID = CASE @filmID WHEN 0 THEN o.filmID ELSE @filmID END)
AND (o.ContactID= CASE @contactID WHEN 0 THEN o.ContactID ELSE contactID END)
AND (o.MarketID = CASE @marketID WHEN 0 THEN o.MarketID ELSE @marketID END)
AND (o.NewspaperID = CASE @newspaperID WHEN 0 THEN o.NewspaperID ELSE @newspaperID END)

Well, that does the trick, but it’s not efficient. In the case of 0, I am still putting a requirement on the parser to go through each row and verify that the value equals itself, and the optimal solution is that the parser simply ignores the filter if a 0 is passed.

The optimized WHERE clause looks like this:

WHERE (o.PostedDate BETWEEN @begindate AND DATEADD(d,1,@enddate) )
AND ( @filmID = 0 OR o.FilmID = @filmID )
AND ( @contactID = 0 OR o.ContactID = @contactID )
AND ( @marketID = 0 OR o.MarketID = @marketID )
AND ( @newspaperID = 0 OR o.NewspaperID = @newspaperID )

Much better, right? If you have any query optimization tricks to share with me, I’d love to hear them.