[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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

0
byte1
Asked:
byte1
  • 5
  • 4
1 Solution
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now