October 2, 2013 at 3:34 am
hello all.i have a parent table:
subjectid subjectname parentid organizationid
4058 x -1 576
4059 x1 4058 576
4060 x2 4058 576
4061 x3 4058 576
4062 x11 4059 576
4063 x111 4062 576
now i want to have count of level of my table?i want a query wich teturns 3 in this example
October 2, 2013 at 3:48 am
Something like this?
WITH SampleData (subjectid, subjectname, parentid, organizationid) AS
(
SELECT 4058,'x', -1, 576
UNION ALL SELECT 4059,'x1',4058, 576
UNION ALL SELECT 4060,'x2',4058, 576
UNION ALL SELECT 4061,'x3',4058, 576
UNION ALL SELECT 4062,'x11',4059, 576
UNION ALL SELECT 4063,'x111',4062, 576
),
rCTE AS
(
SELECT n=0, * -- Assign parent to level=0
FROM SampleData
WHERE parentid = -1
UNION ALL
SELECT n+1, b.*
FROM rCTE a
JOIN SampleData b ON a.subjectid = b.parentid
)
SELECT * -- [Level]=MAX(n)
FROM rCTE;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 2, 2013 at 3:58 am
--SAMPLE DATA
--NOTE: IF YOU LAY OUT YOUR SAMPLE DATA LIKE THIS, PEOPLE ARE MORE LIKELY TO HELP YOU!
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT subjectid, subjectname, parentid, organizationid
INTO #testEnvironment
FROM (VALUES(4058, 'x', -1, 576),(4059, 'x1', 4058, 576),(4060, 'x2', 4058, 576),
(4061, 'x3', 4058, 576),(4062, 'x11', 4059, 576),(4063, 'x111', 4062, 576)
)a(subjectid, subjectname, parentid, organizationid);
--OPTION 1
WITH CTE AS
(
SELECT subjectid, subjectname, parentid, organizationid, 0 AS level
FROM #testEnvironment
WHERE parentid = -1
UNION ALL
SELECT b.subjectid, b.subjectname, b.parentid, b.organizationid, a.level+1
FROM CTE a
INNER JOIN #testEnvironment b ON a.subjectid = b.parentid
)
SELECT subjectid, subjectname, parentid, organizationid, level, MAX(level) OVER() AS MaxLevel
FROM CTE;
--OPTION 2
SELECT subjectid, subjectname, parentid, organizationid, level, MAX(level) OVER() AS MaxLevel
FROM (SELECT subjectid, subjectname, parentid, organizationid,
DENSE_RANK() OVER(ORDER BY N)-1 AS level
FROM #testEnvironment a
OUTER APPLY (SELECT subjectid
FROM #testEnvironment
WHERE subjectid = a.parentid
)oa(N)
)innerQuery;
Returns: -
subjectid subjectname parentid organizationid level MaxLevel
----------- ----------- ----------- -------------- ----------- -----------
4058 x -1 576 0 3
4059 x1 4058 576 1 3
4060 x2 4058 576 1 3
4061 x3 4058 576 1 3
4062 x11 4059 576 2 3
4063 x111 4062 576 3 3
subjectid subjectname parentid organizationid level MaxLevel
----------- ----------- ----------- -------------- -------------------- --------------------
4058 x -1 576 0 3
4059 x1 4058 576 1 3
4060 x2 4058 576 1 3
4061 x3 4058 576 1 3
4062 x11 4059 576 2 3
4063 x111 4062 576 3 3
October 2, 2013 at 4:21 am
Cadavre (10/2/2013)
--SAMPLE DATA--NOTE: IF YOU LAY OUT YOUR SAMPLE DATA LIKE THIS, PEOPLE ARE MORE LIKELY TO HELP YOU!
+1
Cadavre (10/2/2013)
--OPTION 1
...
--OPTION 2
I choose option 1!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 2, 2013 at 4:56 am
dwain.c (10/2/2013)
I choose option 1!
Got to agree, the logic isn't quite right for the outer apply method. The below uses a sproc written by Jeff Moden for generating a hierarchy.
--===== Do this in a nice, safe place that everyone has
USE tempdb;
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.BuildLargeEmployeeTable','P') IS NOT NULL
DROP PROCEDURE dbo.BuildLargeEmployeeTable;
GO
CREATE PROCEDURE dbo.BuildLargeEmployeeTable
/****************************************************************************
Purpose:
Create a randomized "well formed" Adjacency List hierarchy with indexes.
Progammer's Notes:
1. Each EmployeeID (except for the Root Node, of course) is assigned a
random ManagerID number which is initially always less than the current
EmployeeID to ensure that no cycles occur in the hierarcy.
2. The second parameter used to call this stored procedure will optionally
randomize the EmployeeIDss to make the hierarchy truly random as it would
likely be in real life. This, of course, takes a small amounnt of extra
time.
3. This code runs nasty fast and is great for testing hierarchical
processing code. Including the index builds, this code will build a
million node Adjacency List on a 4 processor (i5) laptop with 6GB of RAM
in just several seconds. The optional randomization adds just several
more seconds.
Usage:
--===== Create the hierarchy where all the ManagerIDs are less than the
-- EmployeeIDs. This is the fastest option and will build a million node
-- hierarchy in just about 7 seconds on a modern machine.
EXEC dbo.BuildLargeEmployeeTable 1000000;
--===== Making the second parameter a non-zero value will further randomize
-- the IDs in the hierarchy. This, of course, takes extra time and will
-- build a million row hierarchy in about 17 seconds on a modern
-- machine.
EXEC dbo.BuildLargeEmployeeTable 1000000,1;
Revision History:
Initial concept and creation - Circa 2009 - Jeff Moden
Rev 01 - 15 May 2010 - Jeff Moden
- Abort if current DB isn't "tempdb" to protect users.
Rev 02 - 13 Oct 2012 - Jeff Moden
- Add a randomization stop to make the hierarchy more like real life.
****************************************************************************/
--===== Declare the I/O parameters
@pRowsToBuild INT,
@pRandomize TINYINT = 0
AS
--===========================================================================
-- Presets
--===========================================================================
--===== Supresss the autodisplay of rowcounts to cleanup the display and to
-- prevent false error returns if called from a GUI.
SET NOCOUNT ON;
--===== Make sure that we're in a safe place to run this...
IF DB_NAME() <> N'tempdb'
BEGIN
RAISERROR('Current DB is NOT tempdb. Run aborted.',11,1);
RETURN;
END;
--===== Conditionaly drop the test table so we can do reruns more easily
IF OBJECT_ID('tempdb.dbo.Employee','U') IS NOT NULL
DROP TABLE tempdb.dbo.Employee;
--===========================================================================
RAISERROR('Building the hierarchy...',0,1) WITH NOWAIT;
--===========================================================================
--===== Build the test table and populate it on the fly.
-- Everything except ManagerID is populated here. The code uses a
-- technique called a "Psuedo-Cursor" (kudos to R. Barry Young for the
-- term) to very quickly and easily build large numbers of rows.
SELECT TOP (@pRowsToBuild)
EmployeeID = ISNULL(CAST(
ROW_NUMBER() OVER (ORDER BY (SELECT 1))
AS INT),0),
ManagerID = CAST(NULL AS INT),
EmployeeName = CAST(NEWID() AS VARCHAR(36))
INTO tempdb.dbo.Employee
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
CROSS JOIN master.sys.all_columns ac3
;
RAISERROR('There are %u rows in the hierarchy.',0,1,@@ROWCOUNT) WITH NOWAIT;
--===== Update the test table with ManagerID's. The ManagerID is some random
-- value which is always less than the current EmployeeID to keep the
-- hierarchy "clean" and free from "loop backs".
UPDATE tempdb.dbo.Employee
SET ManagerID = CASE
WHEN EmployeeID > 1
THEN ABS(CHECKSUM(NEWID())) % (EmployeeID-1) +1
ELSE NULL
END
;
--===========================================================================
-- Conditionally randomize the hierarchy to be more like real life
--===========================================================================
IF @pRandomize <> 0
BEGIN
--===== Alert the operator
RAISERROR('Randomizing the hierarchy...',0,1) WITH NOWAIT;
--===== Create a randomized cross reference list to randomize the
-- EmployeeIDs with.
SELECT RandomEmployeeID = IDENTITY(INT,1,1),
EmployeeID
INTO #RandomXRef
FROM dbo.Employee
ORDER BY NEWID()
;
--===== Update the ManagerIDs in the Employee table with the new
-- randomized IDs
UPDATE emp
SET emp.ManagerID = RandomEmployeeID
FROM dbo.Employee emp
JOIN #RandomXRef xref ON emp.ManagerID = xref.EmployeeID
;
--===== Update the EmployeeIDs in the Employee table with the new
--randomized IDs
UPDATE emp
SET emp.EmployeeID = RandomEmployeeID
FROM dbo.Employee emp
JOIN #RandomXRef xref ON emp.EmployeeID = xref.EmployeeID
;
END
ELSE
BEGIN
--===== Alert the operator
RAISERROR('The hierarchy is not randomized',0,1) WITH NOWAIT;
END
;
--===========================================================================
-- Build the indexes necessary for performance.
--===========================================================================
--===== Alert the operator
RAISERROR('Building the keys and indexes...',0,1) WITH NOWAIT;
--===== Add some indexes that most folks would likely have on such a table
ALTER TABLE tempdb.dbo.Employee
ADD CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID)
;
CREATE UNIQUE INDEX By_ManagerID_EmployeeID
ON tempdb.dbo.Employee (ManagerID,EmployeeID)
;
ALTER TABLE dbo.Employee
ADD CONSTRAINT FK_Employee_Employee FOREIGN KEY
(ManagerID) REFERENCES dbo.Employee (EmployeeID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
;
--===========================================================================
-- Exit
--===========================================================================
RAISERROR('===============================================',0,1) WITH NOWAIT;
RAISERROR('RUN COMPLETE',0,1) WITH NOWAIT;
RAISERROR('===============================================',0,1) WITH NOWAIT;
GO
EXEC dbo.BuildLargeEmployeeTable 1000000,0;
GO
SELECT COUNT(*) AS TotalSize
FROM tempdb.dbo.Employee;
GO
PRINT REPLICATE('=',80);
PRINT 'CHECK THAT EACH QUERY GETS THE SAME RESULT';
PRINT REPLICATE('=',80);
DECLARE @HOLDER1 INT, @HOLDER2 INT;
WITH CTE AS
(
SELECT EmployeeID, ManagerID, 0 AS level
FROM tempdb.dbo.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT b.EmployeeID, b.ManagerID, a.level+1
FROM CTE a
INNER JOIN tempdb.dbo.Employee b ON a.EmployeeID = b.ManagerID
)
SELECT @HOLDER1 = MAX(level)
FROM CTE;
SELECT @HOLDER2 = MAX(level)
FROM (SELECT EmployeeID, ManagerID,
DENSE_RANK() OVER(ORDER BY N)-1 AS level
FROM tempdb.dbo.Employee a
OUTER APPLY (SELECT EmployeeID
FROM tempdb.dbo.Employee
WHERE EmployeeID = a.ManagerID
)oa(N)
)innerQuery;
SELECT @HOLDER1 AS CTEResult, @HOLDER2 AS OutApplyResult;
GO
PRINT REPLICATE('=',80);
PRINT 'PERFORMANCE COMPARISON';
PRINT REPLICATE('=',80);
DECLARE @Loop CHAR(1) = '0', @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = COUNT(*)
FROM tempdb.dbo.Employee;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('BaseLine Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
WHILE @Loop <= 5
BEGIN;
RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
WITH CTE AS
(
SELECT EmployeeID, ManagerID, 0 AS level
FROM tempdb.dbo.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT b.EmployeeID, b.ManagerID, a.level+1
FROM CTE a
INNER JOIN tempdb.dbo.Employee b ON a.EmployeeID = b.ManagerID
)
SELECT @HOLDER = MAX(level)
FROM CTE;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Recursive CTE: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = MAX(level)
FROM (SELECT EmployeeID, ManagerID,
DENSE_RANK() OVER(ORDER BY N)-1 AS level
FROM tempdb.dbo.Employee a
OUTER APPLY (SELECT EmployeeID
FROM tempdb.dbo.Employee
WHERE EmployeeID = a.ManagerID
)oa(N)
)innerQuery;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Outer Apply Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
SET @Loop = @Loop + 1;
END;
GO
IF OBJECT_ID('tempdb.dbo.Employee','U') IS NOT NULL
BEGIN;
DROP TABLE tempdb.dbo.Employee;
END;
IF OBJECT_ID('dbo.BuildLargeEmployeeTable','P') IS NOT NULL
DROP PROCEDURE dbo.BuildLargeEmployeeTable;
GO
Building the hierarchy...
There are 1000000 rows in the hierarchy.
The hierarchy is not randomized
Building the keys and indexes...
===============================================
RUN COMPLETE
===============================================
TotalSize
-----------
1000000
================================================================================
CHECK THAT EACH QUERY GETS THE SAME RESULT
================================================================================
CTEResult OutApplyResult
----------- --------------
33 499417
================================================================================
PERFORMANCE COMPARISON
================================================================================
BaseLine Duration: 00:00:00:083
============
============
Loop: 0
============
============
Recursive CTE: 00:00:18:707
============
Outer Apply Duration: 00:00:01:503
============
Loop: 1
============
============
Recursive CTE: 00:00:19:017
============
Outer Apply Duration: 00:00:01:487
============
Loop: 2
============
============
Recursive CTE: 00:00:18:267
============
Outer Apply Duration: 00:00:01:477
============
Loop: 3
============
============
Recursive CTE: 00:00:19:043
============
Outer Apply Duration: 00:00:01:497
============
Loop: 4
============
============
Recursive CTE: 00:00:17:413
============
Outer Apply Duration: 00:00:01:440
============
Loop: 5
============
============
Recursive CTE: 00:00:18:273
============
Outer Apply Duration: 00:00:01:463
============
October 2, 2013 at 5:04 am
thank you for your reply
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply