Query hangs inside sproc but runs in seconds when run in Query Analyzer!
-
I have a table of just over 200k records which contains several filtered hash indexes.
There are two issues I'm trying to solve at the moment.
The first is the INSERT statement in the stored procedure hangs but when I run the same code in a new Query Analyzer window, it runs in a couple of seconds. I've tried to run my sproc using WITH RECOMPILE but that's made no difference.
The other problem is the table scan in the actual execution plan which I'm trying to eliminiate.
My query looks like this:
but I have the following index on the mkMatchKeyType1 column:
CREATE NONCLUSTERED INDEX [idx_mkMatchKeyType1] ON [dbo].[MergeTest1_keys_](
[mkMatchKeyType1] ASC,
[ID] ASC
)
INCLUDE ( [GUID])
WHERE ([mkMatchKeyType1] IS NOT NULL)
---------------------------------------------------------
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 -
Removing the filter from the index seems to have done the trick but I'm not satisfied completely as I don't understand the reason for this. Oh well... need to do some more reading on filtered indexes.
---------------------------------------------------------
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 -
This?
http://www.sqlservercentral.com/Forums/FindPost1398567.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
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;
---------------------------------------------------------
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 -
Abu Dina (3/22/2013)
Hmmm.. Galimonster's last comment kind of makes sense but I'm still not entirely sure I understand what's going on.Basically what it means is that since the query in your stored procedure is parameterized, i.e. it uses a local variable or input parameter for filtering, the optimizer has to generate a plan that would work for all possible values of that parameter or variable and therefore it cannot incorporate the use of the filtered index in case the parameter-value supplied is excluded by the filter.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Hah!!! Now I understand.... thanks! 😀
---------------------------------------------------------
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply