Hmmm.. Galimonster's last comment kind of makes sense but I'm still not entirely sure I understand what's going on.

Another thing which is really confusing is that I have a SQL C# CLR TVF which was processing 250k records in 9 seconds (great time for the amount of work it does) but for some reason and without changing any of the code of the TVF, it's now taking just over a minute

I've tried dropping and recreating the sproc but that's made no difference. Any idea why the execution time would change like that all of a sudden?

The sproc which calls the CLR TVF below:

USE [Merge]

GO

/****** Object: StoredProcedure [dbo].[usp_GenerateKeys] Script Date: 03/22/2013 10:28:51 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_GenerateKeys] (

-- Version 1

@SOURCETABLE VARCHAR(50)

,@ID VARCHAR(50) = NULL

,@GUID VARCHAR(50) = NULL

,@TITLE VARCHAR(50) = NULL

,@FULLCONTACTNAME VARCHAR(50) = NULL

,@INITIAL VARCHAR(50) = NULL

,@FORENAME VARCHAR(50) = NULL

,@MIDDLENAME VARCHAR(50) = NULL

,@SURNAME VARCHAR(50) = NULL

,@BUILDINGNAME VARCHAR(50) = NULL

,@ADDRESS1 VARCHAR(50) = NULL

,@ADDRESS2 VARCHAR(50) = NULL

,@ADDRESS3 VARCHAR(50) = NULL

,@ADDRESS4 VARCHAR(50) = NULL

,@ADDRESS5 VARCHAR(50) = NULL

,@TOWN VARCHAR(50) = NULL

,@COUNTY VARCHAR(50) = NULL

,@POSTCODE VARCHAR(50) = NULL

,@POSTCODEPREFIX VARCHAR(50) = NULL

,@POSTCODESUFFIX VARCHAR(50) = NULL

,@COUNTRY VARCHAR(50) = NULL

,@ORGANISATIONNAME VARCHAR(50) = NULL

,@COMPANYNAME2 VARCHAR(50) = NULL

,@COMPANYNAME3 VARCHAR(50) = NULL

,@COMPANYNAME4 VARCHAR(50) = NULL

,@EMAIL VARCHAR(50) = NULL

,@HOMETELEPHONE VARCHAR(50) = NULL

,@MOBILETELEPHONE VARCHAR(50) = NULL

,@FAX VARCHAR(50) = NULL

,@TELEPHONEAREACODE VARCHAR(50) = NULL

,@LANGUAGECODE VARCHAR(50) = NULL

,@WEBSITE VARCHAR(50) = NULL

,@CUSTOMERURN VARCHAR(50) = NULL

,@CUSTOMERURN2 VARCHAR(50) = NULL

,@SUBMISSIONID VARCHAR(50) = NULL

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

IF @TITLE IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TITLE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TITLE = '''COLNOTPROV'''

IF @FULLCONTACTNAME IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @FULLCONTACTNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @FULLCONTACTNAME = '''COLNOTPROV'''

IF @INITIAL IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @INITIAL AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @INITIAL = '''COLNOTPROV'''

IF @FORENAME IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @FORENAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @FORENAME = '''COLNOTPROV'''

IF @MIDDLENAME IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @MIDDLENAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @MIDDLENAME = '''COLNOTPROV'''

IF @SURNAME IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @SURNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @SURNAME = '''COLNOTPROV'''

IF @BUILDINGNAME IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @BUILDINGNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @BUILDINGNAME = '''COLNOTPROV'''

IF @ADDRESS1 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS1 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS1 = '''COLNOTPROV'''

IF @ADDRESS2 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS2 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS2 = '''COLNOTPROV'''

IF @ADDRESS3 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS3 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS3 = '''COLNOTPROV'''

IF @ADDRESS4 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS4 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS4 = '''COLNOTPROV'''

IF @ADDRESS5 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS5 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS5 = '''COLNOTPROV'''

IF @TOWN IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TOWN AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TOWN = '''COLNOTPROV'''

IF @COUNTY IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COUNTY AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COUNTY = '''COLNOTPROV'''

IF @POSTCODE IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @POSTCODE = '''COLNOTPROV'''

IF @POSTCODEPREFIX IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODEPREFIX AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @POSTCODEPREFIX = '''COLNOTPROV'''

IF @POSTCODESUFFIX IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODESUFFIX AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @POSTCODESUFFIX = '''COLNOTPROV'''

IF @COUNTRY IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COUNTRY AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COUNTRY = '''COLNOTPROV'''

IF @ORGANISATIONNAME IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ORGANISATIONNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ORGANISATIONNAME = '''COLNOTPROV'''

IF @COMPANYNAME2 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COMPANYNAME2 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COMPANYNAME2 = '''COLNOTPROV'''

IF @COMPANYNAME3 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COMPANYNAME3 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COMPANYNAME3 = '''COLNOTPROV'''

IF @COMPANYNAME4 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COMPANYNAME4 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COMPANYNAME4 = '''COLNOTPROV'''

IF @EMAIL IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @EMAIL AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @EMAIL = '''COLNOTPROV'''

IF @HOMETELEPHONE IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @HOMETELEPHONE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @HOMETELEPHONE = '''COLNOTPROV'''

IF @MOBILETELEPHONE IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @MOBILETELEPHONE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @MOBILETELEPHONE = '''COLNOTPROV'''

IF @FAX IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @FAX AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @FAX = '''COLNOTPROV'''

IF @TELEPHONEAREACODE IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TELEPHONEAREACODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TELEPHONEAREACODE = '''COLNOTPROV'''

IF @LANGUAGECODE IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @LANGUAGECODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @LANGUAGECODE = '''COLNOTPROV'''

IF @website IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @website AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @website = '''COLNOTPROV'''

IF @CUSTOMERURN IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @CUSTOMERURN AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @CUSTOMERURN = '''COLNOTPROV'''

IF @CUSTOMERURN2 IS NULL OR NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @CUSTOMERURN2 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @CUSTOMERURN2 = '''COLNOTPROV'''

IF @SUBMISSIONID IS NULL SET @SUBMISSIONID = '''AD-HOC'''

-- Main sproc logic:

IF @SOURCETABLE IS NULL

BEGIN

RAISERROR('No source table specified', 16, 1);

RETURN

END

IF @SOURCETABLE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @SOURCETABLE)

BEGIN

RAISERROR('Source table doesn''t exist!', 16, 1);

RETURN

END

-- If no ID column is supplied then we can't really perform any processing!

IF @ID IS NULL

BEGIN

RAISERROR('No ID column specified!', 16, 1);

RETURN

END

SET @SQL = 'INSERT INTO dbo.' + @SOURCETABLE + '_Keys_ (ID, GUID, mkTitle, mkNameKey, mkAddressKey, mkName1, mkName2, mkName3, mkNormalizedName, mkOrganizationKey,

mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkorgName3, mkPostIn, mkPostOut, mkPhoneticStreet, mkPremise, mkPhoneticTown, mkEmailAddress,

mkTelephoneNumber, mkMobileNumber, mkMatchKeyType1, mkMatchKeyType2, mkMatchKeyType3, mkMatchKeyType4, mkMatchKeyType5,

mkMatchKeyType6, mkMatchKeyType7, mkMatchKeyType8, mkMatchKeyFuzzyType1, mkMatchKeyFuzzyType2, mkMatchKeyFuzzyType3, mkMatchKeyFuzzyType4,

mkMatchKeyFuzzyType5, SubmissionID)

SELECT gr.*, ' + @SUBMISSIONID + '

FROM dbo.' + @SOURCETABLE + '

CROSS APPLY[dbo].[GenerateKeys](' + @ID + ', ' + @GUID + ', ISNULL(' + @TITLE + ', ''''), ' + '

ISNULL(' + @FULLCONTACTNAME + ', ''''), ' +

'ISNULL(' + @INITIAL + ', ''''), ' +

'ISNULL(' + @FORENAME + ', ''''), ' +

'ISNULL(' + @MIDDLENAME + ', ''''),

ISNULL(' + @SURNAME + ', ''''), ' +

'ISNULL(' + @BUILDINGNAME + ', ''''),

ISNULL(' + @ADDRESS1 + ', ''''),

ISNULL(' + @ADDRESS2 + ', ''''),

ISNULL(' + @ADDRESS3 + ', ''''),

ISNULL(' + @ADDRESS4 + ', ''''),

ISNULL(' + @ADDRESS5 + ', ''''),

ISNULL(' + @TOWN + ', ''''),

ISNULL(' + @COUNTY + ', ''''),

ISNULL(' + @POSTCODE + ', ''''),

ISNULL(' + @POSTCODEPREFIX + ', ''''),

ISNULL(' + @POSTCODESUFFIX + ', ''''),

ISNULL(' + @COUNTRY + ', ''''),

ISNULL(' + @ORGANISATIONNAME + ', ''''),

ISNULL(' + @COMPANYNAME2 + ', ''''),

ISNULL(' + @COMPANYNAME3 + ', ''''),

ISNULL(' + @COMPANYNAME4 + ', ''''),

ISNULL(' + @EMAIL + ', ''''),

ISNULL(' + @HOMETELEPHONE + ', ''''),

ISNULL(' + @MOBILETELEPHONE + ', ''''),

ISNULL(' + @FAX + ', ''''),

ISNULL(' + @TELEPHONEAREACODE + ', ''''),

ISNULL(' + @LANGUAGECODE + ', ''''),

ISNULL(' + @website + ', ''''),

ISNULL(' + @CUSTOMERURN + ', ''''),

ISNULL(' + @CUSTOMERURN2 + ', '''')) as gr'

EXEC sp_executesql @SQL

-- Now let's create some filtered indexes on the Match Key columns

-- EXACT KEYS

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType1' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType1 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType1, ID) INCLUDE(GUID, mkNormalizedName, mkName1,

mkName2, mkName3, mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkOrgName3 )')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType2' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType2 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType2, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType3' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType3 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType3, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType4' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType4 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType4, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType5' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType5 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType5, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType6' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType6 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType6, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType7' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType7 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType7, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyType8' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyType8 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyType8, ID) INCLUDE(GUID)')

-- FUZZY KEYS

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyFuzzyType1' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyFuzzyType1 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyFuzzyType1, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyFuzzyType2' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyFuzzyType2 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyFuzzyType2, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyFuzzyType3' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyFuzzyType3 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyFuzzyType3, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyFuzzyType4' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyFuzzyType4 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyFuzzyType4, ID) INCLUDE(GUID)')

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_mkMatchKeyFuzzyType5' AND OBJECT_ID = OBJECT_ID(@SourceTable + '_keys_'))

EXEC('CREATE INDEX idx_mkMatchKeyFuzzyType5 ON dbo.' + @SourceTable + '_keys_(mkMatchKeyFuzzyType5, ID) INCLUDE(GUID)')

END;