Click here to Skip to main content
15,893,487 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
SQL
SELECT [D].[CRMSMBOpportunityKey]
		, [A].[CreatedOn]
		, [B].[Value]
		, [B].[AttributeName]
	INTO [Tmp].[LeadAuditHistory_Days]
	FROM [SMBBI_DataSources].[GMODW_DS].[AuditBase] [A] WITH (NOLOCK)
	INNER JOIN [SMBBI_DataSources].[GMODW_DS].[FilteredStringMap] [B] WITH (NOLOCK)
		ON CAST([A].[ChangeData] as NVARCHAR(255)) LIKE '%' + CAST([B].[AttributeValue] AS NVARCHAR(255)) + '%'
			AND [B].[AttributeName] = 'new_salesstage'
	INNER JOIN [SMBBI_DataSources].[GMODW].[DimOpportunityCRMSMB] [D] WITH (NOLOCK)
		ON [A].[ObjectID] = [D].[OpportunityID]
			AND [D].[IsCurrent] = 1
Posted
Updated 9-Jun-14 0:09am
v2
Comments
Magic Wonder 9-Jun-14 5:58am    
What is the query execution time? How Many Nos of records are there in your tables? On first sight, try to avoid Like in joining condition.
Member 10872775 9-Jun-14 6:37am    
takeing more than 15 min , no. of records 5 lacks, there is no other column to join

Firstly do proper indexing in your table, kindly take a note that sometimes more indexing / wrong indexing may slow your output query when your data in lacs.

Also, fetch limited data in #temp tables from your main tables as per requirements. Do indexing in #temp tables also. Write query on your #temp tables.

Avoid like if you can in your joining condition.

Then check the query execution time.

Hope this will help you.
 
Share this answer
 
v2
The optimizer can't use a normal index on [B].[AttributeValue] because of the LIKE clause, so either standardize the AttributeValues so that you can drop the LIKE clause.
Or add a Full Text Index on [B].[AttributeValue]. Note that the Full Text Index isn't useful if the values in [A].[ChangeData] isn't in the dictionary. In that case you might need to create a custom dictionary on the values in [A].[ChangeData]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900