Solved

SP Optimisation

Posted on 2014-09-05
8
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 49

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 37

Expert Comment

by:Neil Russell
ID: 40305518
If so, please post a result set here.
0
 
LVL 49

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:Scott Pletcher
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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