April 6, 2006 at 1:44 pm
I came up with two ways (A and B in query below) to get a solution to a problem, and so ran the execution plan with both versions in the same query to look at relative performance.
one did better than the other, to the tune of 1.76% vs 76% of overall batch.
The odd thing is that, when I reversed the two, instead of 78% and 1.76%, I got 20% and 75%%.
Even weirder (to me), when I ran A, then B, then A again, I got substantially different performance for each of the two section A's (A1 - 0.4%; B - 20%, A2 - 75%)
I'm not really that stessed over it - the whole thing runs in about 1.5 seconds. but, as I try to dive deeper into performance tuning, this one is making my head scratch, and my dba had no light to shed on the subject.
--basic query - tables include 'trips', 'stops on trips', and 'properties of stops'. 'stop properties' are attached to only 1 stop of a trip, and I need the properties to appear for each line of output (which is at the stop level). so, I am associating the properties with the trip id. code not shown joins the result with the stops for final output.
DECLARE @LANE_SET_ID INT
SET @LANE_SET_ID = 1556
DECLARE @DATEFROM DATETIME
DECLARE @DATETO DATETIME
SET @DATEFROM = '3/16/06'
SET @DATETO = '3/16/06'
SET @DATETO = DATEADD(S, -1, DATEADD(D,1,@DATETO))
CREATE TABLE #TEMP
( TRIP_ID INT
, CARRIER CHAR(4)
, TRAILER VARCHAR(20)
, SEAL_NO VARCHAR(30)
, COMMENTS VARCHAR(255)
, SCHED_END DATETIME
, ACTUAL_END DATETIME
)
INSERT INTO #TEMP
SELECT
TI.TRIP_INSTANCE_ID
, ISNULL(TI.ALT_CARRIER_SCAC, TI.CARRIER_SCAC)
, TI.CONVEYANCE_ID_NUMBER
, TI.SEAL_NUMBER
, TI.TRIP_COMMENTS
, SCHED_END_DATETIME
, ACTUAL_END_DATETIME
FROM DBO.TRIP_INSTANCE TI (NOLOCK)
WHERE TI.LANE_SET_ID = @LANE_SET_ID
AND ( TI.ACTUAL_END_DATETIME IS NULL OR TI.ACTUAL_END_DATETIME BETWEEN @DATEFROM AND @DATETO)
AND TI.SCHEDULE_ID IS NOT NULL
AND TI.TRIP_RELEASED ='Y'
ORDER BY TI.TRIP_INSTANCE_ID
-- ------------------------------------------------------------------------------------------------------------------------------
-- Section A
-- ------------------------------------------------------------------------------------------------------------------------------
SELECT T.*
, (SELECT REFERENCE_NUMBER
FROM STOP_REFERENCE_NUMBER SRN (nolock) JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
AND SDI.TRIP_INSTANCE_ID = T.TRIP_ID) AS BOL
, (SELECT PIECES
FROM STOP_REFERENCE_NUMBER SRN (nolock) JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
AND SDI.TRIP_INSTANCE_ID = T.TRIP_ID) AS PIECES
, (SELECT WEIGHT
FROM STOP_REFERENCE_NUMBER SRN (nolock) JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
AND SDI.TRIP_INSTANCE_ID = T.TRIP_ID) AS WEIGHT
FROM #TEMP T
ORDER BY SCHED_END
-- -----------------------------------------------------------------------------------------------------------------------------
-- Section B
-- ------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #STOP_REF
(
TRIP_ID INT
, BOL VARCHAR(30)
, PIECES INT
, WEIGHT FLOAT
)
INSERT INTO #STOP_REF
SELECT T.TRIP_ID, SRN.REFERENCE_NUMBER, SRN.PIECES, SRN.WEIGHT
FROM STOP_REFERENCE_NUMBER SRN (nolock)
JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
JOIN #TEMP T ON T.TRIP_ID = SDI.TRIP_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
SELECT T.*, SR.BOL, SR.PIECES, SR.WEIGHT FROM #TEMP T LEFT JOIN #STOP_REF SR ON SR.TRIP_ID = T.TRIP_ID
-- ------------------------------------------------------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------------------------------------------
-- Section A
-- ------------------------------------------------------------------------------------------------------------------------------
SELECT T.*
, (SELECT REFERENCE_NUMBER
FROM STOP_REFERENCE_NUMBER SRN (nolock) JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
AND SDI.TRIP_INSTANCE_ID = T.TRIP_ID) AS BOL
, (SELECT PIECES
FROM STOP_REFERENCE_NUMBER SRN (nolock) JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
AND SDI.TRIP_INSTANCE_ID = T.TRIP_ID) AS PIECES
, (SELECT WEIGHT
FROM STOP_REFERENCE_NUMBER SRN (nolock) JOIN STOP_DETAIL_INSTANCE SDI (nolock) ON SDI.STOP_DETAIL_INSTANCE_ID = SRN.STOP_DETAIL_INSTANCE_ID
WHERE SRN.REFERENCE_TYPE_ID = 100000
AND SDI.TRIP_INSTANCE_ID = T.TRIP_ID) AS WEIGHT
FROM #TEMP T
ORDER BY SCHED_END
DROP TABLE #TEMP
DROP TABLE #STOP_REF
April 7, 2006 at 10:21 am
John,
Here is what you need to do
enter and run the following Transact-SQL commands:
SET STATISTICS TIME ON
and below that place your query [ the first one]
What is most of interest here is the time information displayed at the bottom of the query’s output. e.g.
SQL Server Execution Times:
CPU time = 30 ms, elapsed time = 387 ms.
What this tells you is how much CPU time was used to execute the query, and how long the query took to run. Of these two pieces of information, only the first is of much use. The CPU time is a relatively consistent measurement of the amount of CPU resources it takes for your query to run, and this is relatively independent of how busy your CPU is. Sure, you probably will see some variation in this number, but it won’t be significant, not as wildly changing as the elapsed time number. The elapsed time number is a measurement of how long the query took to execute (not counting the time for locks or reads). This number will jump around a lot because of the ever-changing load on your server.
Because the CPU time is relatively consistent, you can use it as a way to help you determine whether the changes you make in your queries, during performance tuning, are actually helping or hurting. More on this later.
Same thing you do then after for query B and
based on the CPU time from both cases you can tell which one is better option.
And finally about your so called weird behaviour of query when you alter the sequence or repeat the same query few more times is due to the caching of your t-sql. If next time t-sql can use cache version then it wan't need compilation and that alters the timing.
Hope this helps,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
April 7, 2006 at 12:01 pm
You might also be experiencing skewed results due to buffer and procedure caching. I'd suggest running the following 2 DBCC statements just prior to your execution for each query you are testing and then comparing those results:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply