Solved

SP Optimisation

Posted on 2014-09-05
8
75 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 46

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

896 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

14 Experts available now in Live!

Get 1:1 Help Now