Tip #447: To filter or not to filter – functional view

This seems to be the week of unsupported tips. I know, we said it before: Swim between the flags, and Use filtered views even if you use stored procedures.

But what do you do if this particular report’s query is a monstrosity, adding custom indexes don’t help, and any attempt to restructure it just makes it worse? Sounds familiar?

Consider this simple pre-filtered SQL that returns us all invoiced and delivered line items in the last financial year:

declare @fromutc datetime, @toutc datetime
select 	@fromutc = dbo.fn_LocalTimeToUTC('1-jul-2014'),
	    @toutc   = dbo.fn_LocalTimeToUTC('1-jul-2015')

select 
   CRMAF_FilteredContact.contactid, 
   CRMAF_FilteredContact.fullname, 
   i.datedelivered, 
   i.name, 
   id.actualdeliveryon, 
   id.baseamount, 
   id.tax, 
   id.extendedamount
from
   FilteredContact as CRMAF_FilteredContact
   inner join FilteredInvoice i 
      on CRMAF_FilteredContact.contactid = i.contactid
   inner join FilteredInvoiceDetail id 
      on id.invoiceid = i.invoiceid
where
	id.actualdeliveryonutc 
	between @fromutc
	    and @toutc

If everything else fails (including scheduling the report overnight), my last resort is to isolate some of the views into a separate function and, gulp, replace the filtered views with the base views. In the case above, that could be something like that:

create function [dbo].[OutsideTheFlags]
   (@fromutc datetime, @toutc datetime)
returns table
as return (
select 
   i.ContactId,
   i.datedelivered, 
   i.name, 
   id.actualdeliveryon, 
   id.baseamount, 
   id.tax, 
   id.extendedamount
from
   Invoice i 
   inner join InvoiceDetail id 
      on id.invoiceid = i.invoiceid
where
	id.actualdeliveryon 
	between @fromutc
	    and @toutc
)
GO	
-- This is important. Otherwise won't run
GRANT SELECT 
ON [dbo].[OutsideTheFlags]
TO [DOMAIN\ReportingGroup {GUID}]
GO

and then the reporting query becomes:

declare @fromutc datetime, @toutc datetime
select 	@fromutc = dbo.fn_LocalTimeToUTC('1-jul-2014'),
	    @toutc   = dbo.fn_LocalTimeToUTC('1-jul-2015')

select 
   CRMAF_FilteredContact.contactid, 
   CRMAF_FilteredContact.fullname, 
   i.datedelivered, 
   i.name, 
   i.actualdeliveryon, 
   i.baseamount, 
   i.tax, 
   i.extendedamount
from
   FilteredContact as CRMAF_FilteredContact
   inner join dbo.OutsideTheFlags(@fromutc, @toutc) i  
      on CRMAF_FilteredContact.contactid = i.contactid

Original query: 1.9 sec, new one: 0.2 sec, 900%+ improvement. YMMV, of course. (And, yes, I cheated, times still need to be converted to local)

When performing this unsupported gymnastics, be very, very careful: you’ll lose lookups and optionsets expansion, the dates will be UTC-only, etc. Not to mention that the above gives blanket access to all invoices for all users. Test, test, and test.

The usual disclaimer: use at your own risk, contains small parts, choking hazard, swim between the flags. Not tested on animals, or children. Good luck making it work for CRM Online.

Tweet about this on TwitterShare on Facebook1Share on Google+0

2 thoughts on “Tip #447: To filter or not to filter – functional view

  1. Daniel Kasimirowicz says:

    I like these kind of tips. And I know, you guys like the German word Spie├črutenlaufen. We would use the following disclaimer here:

    Zu Risiken und Nebenwirkungen fragen Sie Ihren Arzt oder Apotheker.

Leave a Reply

Your email address will not be published. Required fields are marked *