This week's optimization query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

This week's optimization query

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 03:00:45

Recently I posted a solution to this problem.
I do not know if there is a better solution, but I would like to know.

-- Prepare sample data
CREATE TABLE	tSample
		(
			ActHistID INT PRIMARY KEY,
			ActivityID INT,
			StartTime DATETIME,
			EndTime DATETIME,
			ActivityCategoryID INT,
			ActivityTypeID INT
		)

INSERT	tSample
	(
		ActHistID,
		ActivityID
		StartTime,
		EndTime,
		ActivityCategoryID,
		ActivityTypeID
	)
SELECT	1, 163, '2007-09-24 23:00', '2007-09-24 00:00', 10006, 10022 UNION ALL
SELECT	3, 163, '2007-09-24 01:00', '2007-09-24 02:00', 10007, 10023 UNION ALL
SELECT	4, 163, '2007-09-24 02:00', '2007-09-24 03:00', 10008, 10024 UNION ALL
SELECT	5, 163, '2007-09-24 03:00', '2007-09-24 04:00', 10009, 10025

Expected output for this sample data is

Field			OldValue	NewValue
------------------	--------	--------
StartTime		23:00 PM	01:00 AM
EndTime			00:00 AM	02:00 AM
ActivityCategoryID	10006		10007
ActivityTypeID		10022		10023
StartTime		01:00 AM	02:00 AM
EndTime			02:00 AM	03:00 AM
ActivityCategoryID	10007		10008
ActivityTypeID		10023		10024
StartTime		02:00 AM	03:00 AM
EndTime			03:00 AM	04:00 AM
ActivityCategoryID	10008		10009
ActivityTypeID		10024		10025

For original poster, the order of the output was vital!
The OP used SQL Server 2005 as platform.

I used SQL Profiler to measure {ROWCOUNT, CPU, DURATION, READS, WRITES} for different solutions.
This was the last suggestion I posted with profiler data {12, 0, 0, 2, 0}

-- Show the expected output
SELECT          p.theColumn AS Field,
                MAX(CASE p.thePosition WHEN 0 THEN p.theValue END) AS OldValue,
                MAX(CASE p.thePosition WHEN 1 THEN p.theValue END) AS NewValue
FROM            (
                        SELECT          ROW_NUMBER() OVER (ORDER BY s.ActHistID) / 2 AS theSection,
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.StartTime, 100), 2)) AS StartTime,
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.EndTime, 100), 2)) AS EndTime, 
                                        CONVERT(VARCHAR(20), s.ActivityCategoryID) AS ActivityCategoryID,
                                        CONVERT(VARCHAR(20), s.ActivityTypeID) AS ActivityTypeID,
                                        d.Delta AS thePosition
                        FROM            tSample AS s
                        CROSS JOIN      (
                                                SELECT    1 AS Delta UNION ALL
                                                SELECT    0
                                        ) AS d
                ) AS y
UNPIVOT         ( 
                                theValue
                        FOR     theColumn IN (y.StartTime, y.EndTime, y.ActivityCategoryID, y.ActivityTypeID)
                ) AS p
GROUP BY        p.theSection,
                p.theColumn
HAVING          COUNT(*) > 1
ORDER BY        p.theSection,
                CASE theColumn
                    WHEN 'StartTime' THEN 0
                    WHEN 'EndTime' THEN 1
                    WHEN 'ActivityCategoryID' THEN 2
                    ELSE 3
                END


  |--Sort(ORDER BY:([Expr1009] ASC, [Expr1020] ASC))
       |--Compute Scalar(DEFINE:([Expr1020]=CASE WHEN [Expr1016]=N'StartTime' THEN (0) ELSE CASE WHEN [Expr1016]=N'EndTime' THEN (1) ELSE CASE WHEN [Expr1016]=N'ActivityCategoryID' THEN (2) ELSE (3) END END END))
            |--Filter(WHERE:([Expr1017]>(1)))
                 |--Compute Scalar(DEFINE:([Expr1017]=CONVERT_IMPLICIT(int,[Expr1025],0)))
                      |--Stream Aggregate(GROUP BY:([Expr1016], [Expr1009]) DEFINE:([Expr1025]=Count(*), [Expr1018]=MAX(CASE WHEN [Union1004]=(0) THEN [Expr1015] ELSE NULL END), [Expr1019]=MAX(CASE WHEN [Union1004]=(1) THEN [Expr1015] ELSE NULL END)))
                           |--Sort(ORDER BY:([Expr1016] ASC, [Expr1009] ASC))
                                |--Compute Scalar(DEFINE:([Expr1009]=[Expr1005]/(2)))
                                     |--Filter(WHERE:([Expr1015] IS NOT NULL))
                                          |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[ActivityCategoryID], [s].[ActivityTypeID], [Expr1007], [Expr1008]))
                                               |--Compute Scalar(DEFINE:([Expr1007]=CONVERT(varchar(20),(CONVERT(char(5),[Test].[dbo].[tSample].[StartTime] as [s].[StartTime],108)+' ')+right(CONVERT(char(19),[Test].[dbo].[tSample].[StartTime] as [s].[StartTime],100),(2)),0), [Expr1008]=CONVERT(varchar(20),(CONVERT(char(5),[Test].[dbo].[tSample].[EndTime] as [s].[EndTime],108)+' ')+right(CONVERT(char(19),[Test].[dbo].[tSample].[EndTime] as [s].[EndTime],100),(2)),0)))
                                               |    |--Sequence Project(DEFINE:([Expr1005]=row_number))
                                               |         |--Compute Scalar(DEFINE:([Expr1024]=(1)))
                                               |              |--Segment
                                               |                   |--Nested Loops(Inner Join)
                                               |                        |--Clustered Index Scan(OBJECT:([Test].[dbo].[tSample].[PK_tSample] AS [s]), ORDERED FORWARD)
                                               |                        |--Constant Scan(VALUES:(((1)),((0))))
                                               |--Constant Scan(VALUES:((N'StartTime',[Expr1007]),(N'EndTime',[Expr1008]),(N'ActivityCategoryID',[Test].[dbo].[tSample].[ActivityCategoryID] as [s].[ActivityCategoryID]),(N'ActivityTypeID',[Test].[dbo].[tSample].[ActivityTypeID] as [s].[ActivityTypeID])))

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 03:26:37

For what it's worth, here is a SQL Server 2000 approach
with SQL Profiler data {12, 0, 0, 34, 0}

-- Show the expected output
SELECT		d.theColumn AS Field,
		MAX(CASE WHEN c.FromID = d.ActHistID THEN theValue END) AS OldValue,
		MAX(CASE WHEN c.ToID = d.ActHistID THEN theValue END) AS NewValue
FROM		(
			SELECT		s1.ActHistID AS FromID,
					MIN(s2.ActHistID) AS ToID
			FROM		tSample AS s1
			INNER JOIN	tSample AS s2 ON s2.ActHistID > s1.ActHistID
			GROUP BY	s1.ActHistID
		) AS c
INNER JOIN	(
			SELECT		ActHistID,
					'StartTime' AS theColumn,
					CONVERT(CHAR(5), StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), StartTime, 100), 2) AS theValue,
					1 AS thePosition
			FROM		tSample

			UNION ALL

			SELECT		ActHistID,
					'EndTime' AS theColumn,
					CONVERT(CHAR(5), EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), EndTime, 100), 2) AS theValue,
					2 AS thePosition
			FROM		tSample

			UNION ALL

			SELECT		ActHistID,
					'ActivityCategoryID' AS theColumn,
					CONVERT(CHAR(20), ActivityCategoryID) AS theValue,
					3 AS thePosition
			FROM		tSample

			UNION ALL

			SELECT		ActHistID,
					'ActivityTypeID' AS theColumn,
					CONVERT(CHAR(20), ActivityTypeID) AS theValue,
					4 AS thePosition
			FROM		tSample
		) AS d ON d.ActHistID IN (c.FromID, c.ToID)
GROUP BY	c.FromID,
		d.theColumn,
		d.thePosition
ORDER BY	c.FromID,
		d.thePosition

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 04:08:09

Better hack for SQL Server 2005

-- Show the expected output
SELECT          SUBSTRING(p.theColumn, 2, 20) AS Field,
                MAX(CASE p.thePosition WHEN 0 THEN p.theValue END) AS OldValue,
                MAX(CASE p.thePosition WHEN 1 THEN p.theValue END) AS NewValue
FROM            (
                        SELECT          ROW_NUMBER() OVER (ORDER BY s.ActHistID) / 2 AS theSection,
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.StartTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.StartTime, 100), 2)) AS [1StartTime],
                                        CONVERT(VARCHAR(20), CONVERT(CHAR(5), s.EndTime, 108) + ' ' + RIGHT(CONVERT(CHAR(19), s.EndTime, 100), 2)) AS [2EndTime], 
                                        CONVERT(VARCHAR(20), s.ActivityCategoryID) AS [3ActivityCategoryID],
                                        CONVERT(VARCHAR(20), s.ActivityTypeID) AS [4ActivityTypeID],
                                        d.Delta AS thePosition
                        FROM            tSample AS s
                        CROSS JOIN      (
                                                SELECT    1 AS Delta UNION ALL
                                                SELECT    0
                                        ) AS d
                ) AS y
UNPIVOT         ( 
                                theValue
                        FOR     theColumn IN (y.[1StartTime], y.[2EndTime], y.[3ActivityCategoryID], y.[4ActivityTypeID])
                ) AS p
GROUP BY        p.theSection,
                p.theColumn
HAVING          COUNT(*) > 1
ORDER BY        p.theSection,
		p.theColumn


  |--Compute Scalar(DEFINE:([Expr1020]=substring([Expr1016],(2),(20))))
       |--Filter(WHERE:([Expr1017]>(1)))
            |--Compute Scalar(DEFINE:([Expr1017]=CONVERT_IMPLICIT(int,[Expr1023],0)))
                 |--Stream Aggregate(GROUP BY:([Expr1009], [Expr1016]) DEFINE:([Expr1023]=Count(*), [Expr1018]=MAX(CASE WHEN [Union1004]=(0) THEN [Expr1015] ELSE NULL END), [Expr1019]=MAX(CASE WHEN [Union1004]=(1) THEN [Expr1015] ELSE NULL END)))
                      |--Sort(ORDER BY:([Expr1009] ASC, [Expr1016] ASC))
                           |--Compute Scalar(DEFINE:([Expr1009]=[Expr1005]/(2)))
                                |--Filter(WHERE:([Expr1015] IS NOT NULL))
                                     |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[ActivityCategoryID], [s].[ActivityTypeID], [Expr1007], [Expr1008]))
                                          |--Compute Scalar(DEFINE:([Expr1007]=CONVERT(varchar(20),(CONVERT(char(5),[Test].[dbo].[tSample].[StartTime] as [s].[StartTime],108)+' ')+right(CONVERT(char(19),[Test].[dbo].[tSample].[StartTime] as [s].[StartTime],100),(2)),0), [Expr1008]=CONVERT(varchar(20),(CONVERT(char(5),[Test].[dbo].[tSample].[EndTime] as [s].[EndTime],108)+' ')+right(CONVERT(char(19),[Test].[dbo].[tSample].[EndTime] as [s].[EndTime],100),(2)),0)))
                                          |    |--Sequence Project(DEFINE:([Expr1005]=row_number))
                                          |         |--Compute Scalar(DEFINE:([Expr1022]=(1)))
                                          |              |--Segment
                                          |                   |--Nested Loops(Inner Join)
                                          |                        |--Clustered Index Scan(OBJECT:([Test].[dbo].[tSample].[PK_tSample] AS [s]), ORDERED FORWARD)
                                          |                        |--Constant Scan(VALUES:(((1)),((0))))
                                          |--Constant Scan(VALUES:((N'1StartTime',[Expr1007]),(N'2EndTime',[Expr1008]),(N'3ActivityCategoryID',[Test].[dbo].[tSample].[ActivityCategoryID] as [s].[ActivityCategoryID]),(N'4ActivityTypeID',[Test].[dbo].[tSample].[ActivityTypeID] as [s].[ActivityTypeID])))

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 04:40:54

I wonder if

SELECT CONVERT(CHAR(5), GetDate(), 108), RIGHT(CONVERT(CHAR(19), GetDate(), 100), 2)

could be replaced by

SELECT STUFF(RIGHT(CONVERT(varchar(50), GetDate(), 100), 7), 6, 0, ' ')

but I don't know if that is faster. (Won't give leading zero though

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources