Any better Ideas that may speed this procedure up?
-
Below is a procedure that is taking around 8 seconds to complete and the progressively getting longer as the data table size increases. Any one have any better ideas to speed this up.
CREATE PROCEDURE spGetData @ID1 int, @ID2 int
AS
SET NOCOUNT ON
SELECT T1.Param_ID, T1.Value, MAX(T1.Date) as DataDate, T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3, T3.DataType_ID AS Type
FROM tblValues AS T1
INNER JOIN tblParam AS T2
ON T2.Param_ID = T1.Param_ID
INNER JOIN tblSetupKey AS T3
ON T3.Key_ID = T1.Key_ID
WHERE T1.Key_ID IN (SELECT Key_ID FROM tblSetupKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID IN (1,2,5,6))
GROUP BY T3.DataType_ID, T1.Param_ID, T1.Value,T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3
Return
Edit completed on 12/3:
Original post contained:
WHERE T1.Key_ID IN (SELECT ID2 FROM tblKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID IN (1,2,5,6))
Edited by - cfeisel on 12/03/2003 05:58:58 AM
-
/*
Really depends on characteristics of the tblKey table and its indecies.
The "IN" clauses are similar to "OR", so getting rid of them could help alot.
*/
SELECT T1.Param_ID,
T1.Value,
MAX(T1.Date) as DataDate,
T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3,
T3.DataType_ID AS Type
FROM tblValues AS T1
INNER JOIN tblParam AS T2
ON T2.Param_ID = T1.Param_ID
INNER JOIN tblSetupKey AS T3
ON T3.Key_ID = T1.Key_ID
INNER JOIN (
SELECT DISTINCT ID2
FROM tblKey
WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID IN (1,2,5,6)
) T4
ON T1.Key_ID = T4.ID2
GROUP BY T3.DataType_ID, T1.Param_ID, T1.Value,T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3
-- or Use temp table
SELECT DISTINCT ID2
INTO #Temp
FROM tblKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID IN (1,2,5,6)
SELECT T1.Param_ID,
T1.Value,
MAX(T1.Date) as DataDate,
T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3,
T3.DataType_ID AS Type
FROM tblValues AS T1
INNER JOIN tblParam AS T2
ON T2.Param_ID = T1.Param_ID
INNER JOIN tblSetupKey AS T3
ON T3.Key_ID = T1.Key_ID
INNER JOIN #Temp T4
ON T1.Key_ID = T4.ID2
GROUP BY T3.DataType_ID, T1.Param_ID, T1.Value,T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3
-- or even ... if tblKey.DataType_ID & ID1, ID2 are indexed
-- or Use temp table
SELECT DISTINCT ID2
INTO #Temp
FROM (
SELECT ID2 FROM tblKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID = 1
UNION ALL SELECT ID2 FROM tblKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID = 2
UNION ALL SELECT ID2 FROM tblKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID = 5
UNION ALL SELECT ID2 FROM tblKey WHERE ID1 = @ID1 AND ID2 = @ID2 AND DataType_ID = 6
) ID2s
SELECT T1.Param_ID,
T1.Value,
MAX(T1.Date) as DataDate,
T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3,
T3.DataType_ID AS Type
FROM tblValues AS T1
INNER JOIN tblParam AS T2
ON T2.Param_ID = T1.Param_ID
INNER JOIN tblSetupKey AS T3
ON T3.Key_ID = T1.Key_ID
INNER JOIN #Temp T4
ON T1.Key_ID = T4.ID2
GROUP BY T3.DataType_ID, T1.Param_ID, T1.Value,T2.DefaultOrder, T2.Order1, T2.Order2, T2.Order3
Once you understand the BITs, all the pieces come together
-
Try this
SELECT
T1.Param_ID,
T1.Value,
MAX(T1.[Date]) as DataDate,
T2.DefaultOrder,
T2.Order1,
T2.Order2,
T2.Order3,
T3.DataType_ID AS Type
FROM
dbo.tblValues AS T1
INNER JOIN
dbo.tblParam AS T2
ON
T2.Param_ID = T1.Param_ID
INNER JOIN
dbo.tblSetupKey AS T3
ON
T3.Key_ID = T1.Key_ID
INNER JOIN
dbo.tblKey AS T4
ON
T4.ID2 = T1.KEY_ID AND
ID1 = @ID1 AND
ID2 = @ID2 AND
DataType_ID IN (1,2,5,6)
GROUP BY
T3.DataType_ID,
T1.Param_ID,
T1.Value,
T2.DefaultOrder,
T2.Order1,
T2.Order2,
T2.Order3 -
CREATE PROCEDURE dbo.spGetData @ID1 int, @ID2 int
AS
SET NOCOUNT ON
SELECT
T1.Param_ID
, T1.Value
, MAX(T1.Date) as DataDate
, T2.DefaultOrder
, T2.Order1
, T2.Order2
, T2.Order3
, T3.DataType_ID AS Type
FROM dbo.tblValues AS T1
INNER JOIN dbo.tblParam AS T2 ON(T1.Param_ID = T2.Param_ID)
INNER JOIN dbo.tblSetupKey AS T3 ON(T1.Key_ID = T3.Key_ID)
WHERE exists(select * from dbo.tblKey where T1.Key_ID = ID2 and ID1 = @ID1 and ID2 = @ID2 AND DataType_ID IN (1,2,5,6))
and T1.Key_ID = @ID2
GROUP BY
T3.DataType_ID
, T1.Param_ID
, T1.Value
, T2.DefaultOrder
, T2.Order1
, T2.Order2
, T2.Order3
return(0)
-- The following indexes may help... depending on your data:
-- dbo.tblValues(Key_ID, Param_ID, Date)
-- dbo.tblParam(Param_ID)
-- dbo.tblSetupKey(Key_ID)
-- dbo.tblSetupKey(Param_ID)
-- dbo.tblKey(ID1, ID2, DataType_ID) --> order with the most selective key first
Hope that helps...
-
Let me start by saying Thanks for all the suggestions. However, I did make a mistake when I posted my original script. In the Where clause, ‘Select ID2’ should have read ‘Select Key_ID’. Sorry for the confusion. I have corrected this in the original post. The script should look like this:
CREATE PROCEDURE spGetData @ID1 int, @ID2 int
AS
SET NOCOUNT ON
SELECT
T1.Param_ID,
T1.Value,
MAX(T1.Date) as DataDate,
T2.DefaultOrder,
T2.Order1,
T2.Order2,
T2.Order3,
T3.DataType_ID AS Type
FROM
tblValues AS T1
INNER JOIN
tblParam AS T2
ON
T2.Param_ID = T1.Param_ID
INNER JOIN
tblSetupKey AS T3
ON
T3.Key_ID = T1.Key_ID
WHERE
T1.Key_ID IN
(SELECT Key_ID FROM tblSetupKey WHERE ID1 = @ID1 AND
ID2 = @ID2 AND DataType_ID IN (1,2,5,6))
GROUP BY
T3.DataType_ID,
T1.Param_ID,
T1.Value,
T2.DefaultOrder,
T2.Order1,
T2.Order2,
T2.Order3
Return
A little more information on the tables:
TblValues = [Date], [Key_ID], [Param_ID], [Value]
TblParam = [Param_ID], [DefaultOrder], [Order1], [Order2], [Order3]
TblSetupKey = [ID1], [ID2], [DataType_ID], [Key_ID]
Tblvalues holds values for a particular key_id and param_id. The outcome of the query is to retrieve the most recent value for a paticular number of key_id’s, defined by @ID1, @ID2 with all DataType_ID IN (1,2,5,6), and distinct param_id combination. So I should have distinct row of key_id, param_id, datatype_id. The above code works and achieves this but all other variations that I have tried to speed up the search have resulted in nondistinct rows due to the differences in [Value].
I guess when I come down to it, I’m trying to formulate a query that will give me distinct rows while ignoring the [Value] column.
Edited by - cfeisel on 12/03/2003 06:00:44 AM
Edited by - cfeisel on 12/03/2003 06:02:38 AM
-
After you get the optimal syntax run this proccedure through the profiler and then through the index tuning wizard to see if SQL Server has any suggestions for indexing. Its a fast and easy way to get some improvement in performance.
-Isaiah -
A great way to see where the bottleneck(s) in a query are, is to use the "Show Execution Plan" under the "Query" menu item in SQL Analyzer. After the problem query runs, click on the "Execution Plan" tab in the results window. It will show you in terms of "cost" where the query is bogging down. You want to examine the high cost areas. Some red flags to look for are table scans and index scans. These can sometimes be eliminated by adding indexes and/or altering your "where" clauses and joins. Check out some material on performance tuning. Basic performance tips and tricks can go a long way to improving query times.
Enjoy!
-
quote:
Tblvalues holds values for a particular key_id and param_id. The outcome of the query is to retrieve the most recent value for a paticular number of key_id’s, defined by @ID1, @ID2 with all DataType_ID IN (1,2,5,6), and distinct param_id combination. So I should have distinct row of key_id, param_id, datatype_id. The above code works and achieves this but all other variations that I have tried to speed up the search have resulted in nondistinct rows due to the differences in [Value].I guess when I come down to it, I’m trying to formulate a query that will give me distinct rows while ignoring the [Value] column.
As long as Value is part of the output and you have more than one value it will always take distinct to including it. However, what does value represent and based on that how would you determine what value to keep if you are keeping in your output.
Ex. Value is the sales for a day, and datadate is the date for that figure. I want only the one related to the most recent datadate to be included in the row, the rest should not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply