Query Optimise

How do i optimise this query ? The INNER LOOPS seem to take a hit on execution .
GO
 
DECLARE @Name1 nvarchar(100)
DECLARE @Name2 nvarchar(100)
DECLARE @Name3 nvarchar(100)
DECLARE @Name4 nvarchar(100)
 
DECLARE	@return_value int
declare @counter int
	set @counter = 0
	while @counter < 4650
BEGIN	
 
		 SET @Name1 = N'Name1'
		SET @Name2 = N'Name2'
		SET @Name3 = N'Name3'
		SET @Name4 = N'Name4'
		SET @Type = 1
		
		
 IF OBJECT_ID('tempdb..#SearchNames') IS NOT NULL DROP TABLE #SearchNames
 
CREATE TABLE #SearchNames
(
Names varchar(100)  NOT NULL
)
 
 
 
BEGIN   
SELECT * FROM oc_SDN WHERE Ent_Num IN 
			 (
		SELECT Ent_Num FROM oc_SDN A INNER JOIN #SearchNames B ON A.SDN_Name LIKE '%'  + B.Names  + '%'
			  )
OR Ent_Num IN ( 
		SELECT Ent_Num FROM oc_Aliases A INNER JOIN #SearchNames B ON A.Alt_Name LIKE '%'  + B.Names  + '%'
			  )
END
 
SET @counter = @counter +1
SELECT	'Return Value' = @return_value
END
 
GO

Open in new window

byte1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Think something is missing from your code as you never put any data into the temp table and have two sets of BEGIN/END tags that seem out of place.
0
Aneesh RetnakaranDatabase AdministratorCommented:
How will you populate the #SearchNames ?
0
byte1Author Commented:
Sorry about that, I left some code.

if LEN(@Name1) > 0 BEGIN
SET @Name1 = replace(replace(replace(@Name1,' ','<>'),'><',''),'<>','%') 
INSERT INTO #SearchNames (Names) VALUES (@Name1)
END  
if LEN(@Name2) > 0 BEGIN	
SET @Name2 = replace(replace(replace(@Name2,' ','<>'),'><',''),'<>','%')  
INSERT INTO #SearchNames (Names) VALUES (@Name2)
END  
if LEN(@Name3) > 0 BEGIN
SET @Name3 =   replace(replace(replace(@Name3,' ','<>'),'><',''),'<>','%') 
INSERT INTO #SearchNames (Names) VALUES (@Name3)
 END  
if LEN(@Name4) > 0 BEGIN
SET @Name4 =  replace(replace(replace(@Name4,' ','<>'),'><',''),'<>','%') 
INSERT INTO #SearchNames (Names) VALUES (@Name4)
END  

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Aneesh RetnakaranDatabase AdministratorCommented:
can you post the complete query
0
byte1Author Commented:

GO
 
DECLARE @Name1 nvarchar(100)
DECLARE @Name2 nvarchar(100)
DECLARE @Name3 nvarchar(100)
DECLARE @Name4 nvarchar(100)
DECLARE @Type int
 
DECLARE	@return_value int
declare @counter int
	set @counter = 0
	while @counter < 100
BEGIN	
 
		 SET @Name1 = N'Name1'
		SET @Name2 = N'Name2'
		SET @Name3 = N'Name3'
		SET @Name4 = N'Name4'
		SET @Type = 1
		
		
 IF OBJECT_ID('tempdb..#SearchNames') IS NOT NULL DROP TABLE #SearchNames
 
CREATE TABLE #SearchNames
(
Names varchar(100)  NOT NULL
)
 
if LEN(@Name1) > 0 BEGIN
SET @Name1 = replace(replace(replace(@Name1,' ','<>'),'><',''),'<>','%') 
INSERT INTO #SearchNames (Names) VALUES (@Name1)
END  
if LEN(@Name2) > 0 BEGIN	
SET @Name2 = replace(replace(replace(@Name2,' ','<>'),'><',''),'<>','%')  
INSERT INTO #SearchNames (Names) VALUES (@Name2)
END  
if LEN(@Name3) > 0 BEGIN
SET @Name3 =   replace(replace(replace(@Name3,' ','<>'),'><',''),'<>','%') 
INSERT INTO #SearchNames (Names) VALUES (@Name3)
 END  
if LEN(@Name4) > 0 BEGIN
SET @Name4 =  replace(replace(replace(@Name4,' ','<>'),'><',''),'<>','%') 
INSERT INTO #SearchNames (Names) VALUES (@Name4)
END  
 
if(@Type = 1)
BEGIN   
SELECT * FROM oc_SDN WHERE Ent_Num IN 
			 (
		SELECT Ent_Num FROM oc_SDN A INNER JOIN #SearchNames B ON A.SDN_Name LIKE '%'  + B.Names  + '%'
			  )
OR Ent_Num IN ( 
		SELECT Ent_Num FROM oc_Aliases A INNER JOIN #SearchNames B ON A.Alt_Name LIKE '%'  + B.Names  + '%'
			  )
END
ELSE
BEGIN
SELECT * FROM oc_SDN 
WHERE Ent_Num IN (
		SELECT Ent_Num FROM oc_SDN A INNER JOIN #SearchNames B ON A.SDN_Name = B.Names WHERE LEN(Names) > 0
			  )
OR Ent_Num IN ( 
		SELECT Ent_Num FROM oc_Aliases A INNER JOIN #SearchNames B ON A.Alt_Name = B.Names WHERE LEN(Names) > 0
			  )
END
 
SET @counter = @counter +1
SELECT	'Return Value' = @return_value
END
 
GO

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
try this
Also make sure that there is an index on all the joining columns, if you are using sql server 2005/2008 , go for include index
For table oc_SDN , the index should be   SDN_Name INCLUDE (Ent_Num )
and for oc_Aliases   Alt_Name INCLUDE (Ent_Num )
GO
 
DECLARE @Name1 nvarchar(100)
DECLARE @Name2 nvarchar(100)
DECLARE @Name3 nvarchar(100)
DECLARE @Name4 nvarchar(100)
DECLARE @Type int
 
DECLARE      @return_value int
declare @counter int
set @counter = 0 
IF OBJECT_ID('tempdb..#SearchNames') IS NOT NULL DROP TABLE #SearchNames 
CREATE TABLE #SearchNames ( Names varchar(100)  NOT NULL ) 
while @counter < 100
BEGIN      
	TRUNCATE TABLE #SearchNames
	SELECT @Name1 = N'Name1'
		, @Name2 = N'Name2'
		, @Name3 = N'Name3'
		, @Name4 = N'Name4'
		, @Type = 1
	            
	if LEN(@Name1) > 0 BEGIN
	SET @Name1 = replace(replace(replace(@Name1,' ','<>'),'><',''),'<>','%') 
	END  
	if LEN(@Name2) > 0 BEGIN      
	SET @Name2 = replace(replace(replace(@Name2,' ','<>'),'><',''),'<>','%')  
	END  
	if LEN(@Name3) > 0 BEGIN
	SET @Name3 =   replace(replace(replace(@Name3,' ','<>'),'><',''),'<>','%') 
	 END  
	if LEN(@Name4) > 0 BEGIN
	SET @Name4 =  replace(replace(replace(@Name4,' ','<>'),'><',''),'<>','%') 
	END   

	if(@Type = 1)
	BEGIN  
	 
		SELECT * FROM oc_SDN WHERE Ent_Num IN (
		SELECT Ent_Num FROM oc_SDN A WHERE CHARINDEX(@Name1,A.SDN_Name) >0 
		UNION 
		SELECT Ent_Num FROM oc_SDN A WHERE CHARINDEX(@Name2,A.SDN_Name) >0 
		UNION 
		SELECT Ent_Num FROM oc_SDN A WHERE CHARINDEX(@Name3,A.SDN_Name) >0 
		UNION 
		SELECT Ent_Num FROM oc_SDN A WHERE CHARINDEX(@Name4,A.SDN_Name) >0 
		)
		OR 
		Ent_Num IN ( 
		SELECT Ent_Num FROM oc_Aliases A WHERE CHARINDEX(@Name1,A.SDN_Name) >0 
		UNION 
		SELECT Ent_Num FROM oc_Aliases A WHERE CHARINDEX(@Name2,A.SDN_Name) >0 
		UNION 
		SELECT Ent_Num FROM oc_Aliases A WHERE CHARINDEX(@Name3,A.SDN_Name) >0 
		UNION 
		SELECT Ent_Num FROM oc_Aliases A WHERE CHARINDEX(@Name4,A.SDN_Name) >0 
		
		)
	END
	ELSE
	BEGIN
	INSERT INTO #SearchNames (Names) SELECT @Name1
	UNION SELECT @Name2
	UNION SELECT @Name3
	UNION SELECT @Name4
	DELETE FROM #SearchNames WHERE LEN(Names) = 0 
	SELECT * FROM oc_SDN 
	WHERE Ent_Num IN (
		SELECT Ent_Num FROM oc_SDN A INNER JOIN #SearchNames B ON A.SDN_Name = B.Names 
	)
	OR Ent_Num IN ( 
		SELECT Ent_Num FROM oc_Aliases A INNER JOIN #SearchNames B ON A.Alt_Name = B.Names 
	)
END
 
SET @counter = @counter +1
SELECT      'Return Value' = @return_value
END
 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byte1Author Commented:
About the indexing , I was not able to create a - SDN_Name INCLUDE (Ent_Num ), but it did work the other way around, where Ent_Num was the index and SDN_Name was in the Included columns.

Ent_num int
SDN_Name nvarchar(max)


Error:
Column 'SDN_Name' in table 'dbo.oc_SDN' is of a type that is invalid for use as a key column in an index. (Microsoft SQL Server, Error: 1919)
0
byte1Author Commented:
Update :
I changed the SDN_Name datatype to nvarchar(150) which allowed me to create an index as specified.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Is it faster ?
0
byte1Author Commented:
It did improve my search time. Thanks. But I wish i could do more improvements.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.