?
Solved

SP Optimisation

Posted on 2014-09-05
8
Medium Priority
?
87 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 51

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

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

Accepted Solution

by:
Vitor Montalvão earned 2000 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 66

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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