Solved

Query Optimise

Posted on 2009-06-30
10
169 Views
Last Modified: 2012-05-07
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
Comment
Question by:byte1
  • 5
  • 4
10 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24750937
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24751200
How will you populate the #SearchNames ?
0
 

Author Comment

by:byte1
ID: 24751204
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24751226
can you post the complete query
0
 

Author Comment

by:byte1
ID: 24751340

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24751423
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
 

Author Comment

by:byte1
ID: 24754377
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
 

Author Comment

by:byte1
ID: 24754573
Update :
I changed the SDN_Name datatype to nvarchar(150) which allowed me to create an index as specified.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24755307
Is it faster ?
0
 

Author Comment

by:byte1
ID: 24773207
It did improve my search time. Thanks. But I wish i could do more improvements.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compare data between two databases 16 95
execute a MS SQL script as a schedule SQL job 72 123
Record open by another user 6 50
Pivot Query Problem 9 37
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now