Solved

SP Optimisation

Posted on 2014-09-05
8
72 Views
Last Modified: 2014-09-25
Hi,

I have a program that uses this stored procedure.  I have now identified this SP as the bottle neck and was wondering if anybody could offer any suggestions on optimisation?

Here is the code:

GO

CREATE PROCEDURE [dbo].[Attributes_Values]
(
@serial_id INT,
@char_name VARCHAR(MAX)
)
AS
DECLARE
   @multivalue CHAR,
   @datatype varchar(5)
   
BEGIN
-- DETERMINE IF ATTRIBUTE IS MULTI-VALUE
SET @multivalue = (SELECT TOP 1 characteristics.multi_valued FROM characteristics
INNER JOIN char_data_mv AS C1 ON characteristics.char_id = c1.char_id
WHERE char_name = @char_name)

-- USE A SPECIFIC FUNCTION TO RETRIEVE THE MULTI VALUES FIELDS
IF (@multivalue = 'Y')
BEGIN
SELECT DISTINCT dbo.return_CHAR_MV_New_Line(@serial_id, c1.char_id) AS char_value FROM
characteristics
INNER JOIN char_data_mv AS C1 ON characteristics.char_id = c1.char_id
WHERE
char_name = @char_name
END
ELSE
BEGIN
SET @datatype = (SELECT TOP 1 characteristics.char_data_type FROM characteristics
WHERE
char_name = @char_name)
-- RETURN CH DATA TYPES
IF (@datatype = 'CH')
BEGIN
SELECT DISTINCT C1.char_value FROM
characteristics
INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
WHERE
char_name = @char_name
END
-- RETURN NU DATA TYPES
ELSE IF (@datatype = 'NU')
BEGIN
SELECT DISTINCT C1.number_value FROM
characteristics
INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
WHERE
char_name = @char_name
END
-- RETURN PD DATA TYPES
ELSE IF (@datatype = 'PD')
BEGIN
SELECT DISTINCT C1.char_value FROM
characteristics
INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
WHERE
char_name = @char_name
END
-- RETURN DA DATA TYPES
ELSE IF (@datatype = 'DA')
BEGIN
SELECT DISTINCT C1.date_value FROM
characteristics
INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
WHERE
char_name = @char_name
END
-- RETURN ME DATA TYPES
ELSE IF (@datatype = 'ME')
BEGIN
SELECT DISTINCT C1.blob_value FROM
characteristics
INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
WHERE
char_name = @char_name
END
END
END

Open in new window

0
Comment
Question by:andyw27
8 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40305503
How big is the 'characteristics' table?
And I hope that you have an index on char_name column since it appears on all WHERE clauses.
0
 

Author Comment

by:andyw27
ID: 40305508
Not too big, 90 rows at present
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40305517
Have you run SQL Query Analyzer against the query? This will give ideas of WHERE in that query indexes are used, what tables are use, when temps are used, how long different parts take....
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40305518
If so, please post a result set here.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40305524
Worked a little in your code and could reduce something but dunno if will be a big boost in the performance.

GO

CREATE PROCEDURE [dbo].[Attributes_Values]
(
@serial_id INT,
@char_name VARCHAR(MAX)
)
AS
DECLARE
   @multivalue CHAR,
   
BEGIN
	-- DETERMINE IF ATTRIBUTE IS MULTI-VALUE
	SET @multivalue = (SELECT TOP 1 characteristics.multi_valued 
					FROM characteristics
					INNER JOIN char_data_mv AS C1 ON characteristics.char_id = c1.char_id
					WHERE char_name = @char_name)

	-- USE A SPECIFIC FUNCTION TO RETRIEVE THE MULTI VALUES FIELDS
	IF (@multivalue = 'Y')
	BEGIN
		SELECT DISTINCT dbo.return_CHAR_MV_New_Line(@serial_id, c1.char_id) AS char_value 
		FROM characteristics
		INNER JOIN char_data_mv AS C1 ON characteristics.char_id = c1.char_id
		WHERE char_name = @char_name
	END
	ELSE
	BEGIN
		SELECT DISTINCT CASE char_data_type
							WHEN 'CH' THEN C1.char_value 	-- RETURN CH DATA TYPES
							WHEN 'PD' THEN C1.char_value 	-- RETURN PD DATA TYPES
							WHEN 'NU' THEN C1.number_value 	-- RETURN NU DATA TYPES
							WHEN 'DA' THEN C1.date_value 	-- RETURN DA DATA TYPES
							WHEN 'ME' THEN C1.blob_value 	-- RETURN ME DATA TYPES
		FROM characteristics
		INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
		WHERE char_name = @char_name
	END
END

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40305674
> FROM characteristics
> INNER JOIN char_data AS C1 ON characteristics.char_id = c1.char_id AND C1.object_id = @serial_id
Potentially stupid questions...
(1)  Are there indexes on these columns?
(2)  What are the data types for these columns?  If char/varchar, consider using something with a smaller data type.

>SELECT DISTINCT dbo.return_CHAR_MV_New_Line(@serial_id, c1.char_id) AS char_value
Any execution plan on a SP that calls a udf is not going to return the udf's execution plan, so the obvious questions are is the udf optimized, and is it absolutely necessary to have the udf here, as it will execute once for every row in the return set.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40306023
What specific indexes are on the two tables?

Just based on the above, my best guess of what's needed is:

characteristics
    clustered on: char_id
    nonclustered on: char_name

char_data
    clustered on: ( char_id, object_id )
    nonclustered on: char_name

Also, if the first query is taking a long time, we need to see the function code.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40307081
It should also be noted that doing a TOP without an ORDER BY clause will retrieve a random set of row(s).  In other words, you are saying that you don't care which row(s) are returned.  That may or may not be your intention.

You should also reconsider defining char_name as varchar(MAX)  (I am assuming it has this data type from the parameter you are using to compare).  The problem with this is that you cannot create an index on it.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

12 Experts available now in Live!

Get 1:1 Help Now