SQL C# CLR TVF issues
-
This problem is really starting to annoy me now!
I created a SQL C# CLR TVF which accepts around 30 parameters. The function was running like a charm, processing around 250000 records in 9 seconds which is amazing considering the amount of string manipulation performed by the CLR function.
But for some weird reason and without changing the code of the function or the data set, it started taking 80-90 seconds to run the same code on the sama data set. I even restarted the SQL instance to see if it would go back to the initial speed but to no avail.
The query looks like this:
SELECT top 10000 g.ID, g.GUID, g.mkTitle, g.mkNameKey, g.mkAddressKey, g.mkName1, g.mkName2, g.mkName3, g.mkNormalisedName, g.mkOrganisationKey,g.mkNormalizedOrganization, g.mkOrgName1, g.mkOrgName2, g.mkorgName3, g.mkPostIn, g.mkPostOut, g.mkPhoneticStreet, g.mkPremise,
g.mkPhoneticTown, g.mkEmailAddress, g.mkTelephoneNumber, g.mkMobileNumber, 1
FROM dbo.Load1XX
CROSS APPLY[dbo].[GenerateKeys](ID, GUID, ISNULL('COLNOTPROV', ''),
ISNULL(FullContactName, ''), ISNULL('COLNOTPROV', ''), ISNULL('COLNOTPROV', ''), ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''), ISNULL('COLNOTPROV', ''),
ISNULL(Address1, ''),
ISNULL(Address2, ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL(Town, ''),
ISNULL(County, ''),
ISNULL(Postcode, ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL(Country, ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL(Email, ''),
ISNULL(HomeTelephone, ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL('COLNOTPROV', ''),
ISNULL(CustomerURN, ''),
ISNULL('COLNOTPROV', '')) as g
The actual execution plan looks like this, so it appears the nested loops is taking the longest time. Is there any way round this?!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Well I believe I have solved the problem!
Originally I was creating my staging table with a clustsred index before I was populating it with the data coming from the CLR TVF. What happens here is the data set is generated in memory then bulk inserted into the staging table. (I'm pretty sure that's what happens)
It looks like I had commented out the line of code which creates the clustered index on my staging table which makes the CLR TFV inserts a few thousand rows into the table every few seconds which is a lot slower than the bulk insert!
LOL..... not a day goes by without learning something new! :w00t:
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply