Database Challenges

Here is the performance stats of the solution by pp.pragnesh for TSQL Challenge23.

--Pragnesh_Patel_tsqlchallenge_23_v4.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--
--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

;WITH CTE_AutoRow ( RowID, AuditTime, IsOnLine )
	AS (
			SELECT ROW_NUMBER() OVER (ORDER BY AuditTime ASC)
				, AuditTime
				, IsOnLine
			FROM TC23
		)
	, CTE_FILTER
	AS (

			SELECT 
				Null AS PrevRowID,  Null AS PrevAuditTime,  Null AS PrevIsOnLine,
				RowID, AuditTime, IsOnLine
			FROM CTE_AutoRow
			WHERE CTE_AutoRow.RowID = 1

			UNION ALL
			SELECT 
				Prev.RowID		AS PrevRowID, 

Here is the performance stats of the solution by pp.pragnesh for TSQL Challenge22.

Here is the performance stats of the solution by pp.pragnesh for TSQL Challenge22.

Here is the performance stats of the solution by pp.pragnesh for TSQL Challenge22.

Here is the performance stats of the solution by pp.pragnesh for TSQL Challenge22.

--Pragnesh_Patel_tsqlchallenge_22_v4.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--
--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

DECLARE @TC22_Result AS TABLE
(
    BoxId INT,
    BallID INT
)

DECLARE @TC22_PendPreferences AS TABLE
(
    BoxId INT,
	BallId INT
)

DECLARE @PendBoxCntr AS INT



SELECT @PendBoxCntr = Count(BoxID) FROM TC22_Boxes

INSERT INTO @TC22_PendPreferences ( BoxID, BallID )
SELECT BoxID, BallID FROM TC22_Preferences

WHILE @PendBoxCntr <> 0

	BEGIN

		INSERT INTO @TC22_Result ( BoxID, BallID )

--Pragnesh_Patel_tsqlchallenge_22_v3.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON

--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

DECLARE @TC22_Result AS Table
(
    BoxId INT,
    BallID INT
)

DECLARE @PendBoxCntr AS INT

SELECT @PendBoxCntr = Count(BoxID) FROM TC22_Boxes

WHILE @PendBoxCntr <> 0

	BEGIN

		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT TC22_Preferences.BoxID, TC22_Preferences.BallID
		FROM TC22_Preferences
		WHERE TC22_Preferences.BoxID IN (
											SELECT BoxID
											FROM TC22_Preferences

--Pragnesh_Patel_tsqlchallenge_22_v2.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON

--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

DECLARE @TC22_Result AS Table
(
    BoxId INT,
    BallID INT
)

DECLARE @PendBoxCntr AS INT

SELECT @PendBoxCntr = Count(BoxID) FROM TC22_Boxes

WHILE @PendBoxCntr <> 0

	BEGIN

		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT TC22_Preferences.BoxID, TC22_Preferences.BallID
		FROM TC22_Preferences
		WHERE TC22_Preferences.BoxID IN (
											SELECT BoxID
											FROM TC22_Preferences

--Pragnesh_Patel_tsqlchallenge_22.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON

--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

DECLARE @TC22_Result AS Table
(
    BoxId INT,
    BallID INT
)

DECLARE @PendBoxCntr AS INT
DECLARE @PendBallCntr AS INT

SELECT @PendBoxCntr = Count(BoxID) FROM TC22_Boxes
SELECT @PendBallCntr = Count(BallID) FROM TC22_Balls

WHILE @PendBoxCntr <> 0	OR @PendBallCntr <> 0

	BEGIN

		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT TC22_Preferences.BoxID, TC22_Preferences.BallID