SP Optimisation

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

andyw27Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
andyw27Author Commented:
Not too big, 90 rows at present
0
Neil RussellTechnical Development LeadCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Neil RussellTechnical Development LeadCommented:
If so, please post a result set here.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> 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
Scott PletcherSenior DBACommented:
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
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.