مقاله (اینبار فارسی!): تولید اعداد در Range مورد نظر با حداکثر سرعت!

[AminSobati]

چند روز پیش برای منظور خاصی نیاز داشتم تا سمت سرور تعداد زیادی عدد متوالی در Rangeهای مورد نیاز کاربر تولید کنم و حاصل رو با جدول دیگه ای Join کنم. اولین روشی که به ذهن هر برنامه نویسی میرسه (از جمله خود من!) این هست که با Insert کردن به یک جدول در جریان Loop اعداد رو تولید کنیم. هم از یک متغیر برای شمارنده و هم از خاصیت Identity میتونیم استفاده کنیم:


DECLARE @Counter SMALLINT
SET @Counter=1
DECLARE @Output TABLE (c1 SMALLINT)

WHILE @Counter<=10000
BEGIN
INSERT @Output VALUES(@Counter)
SET @Counter=@Counter+1
END

SELECT * FROM @Output


البته در صورت استفاده از Identity، باید مقدار آخرین Identity تولید شده رو کنترل کنیم تا حلقه به تعداد دلخواه اجرا بشه.
به ذهنم رسید اگر یک جدول تک فیلدی با مقدار 1 داشته باشیم و بتونیم Queryهای تو در تو یا Recursive روی اون اجرا کنیم، منطقا میشه مقدار اولیه 1 رو در خلال Query زیاد کرد و به عدد دلخواه رسوند. در SQL Server 2005 وقتی اسم Recursive میاد، اولین چیزی که انسان به خاطر میاره CTE هستش! ابتدا پیاده سازی این کار با CTE گنگ به نظر میرسید اما با توانایی CTE این کار به شکل جالبی انجام شد!


;WITH MyCTE AS
(SELECT t1.* FROM (SELECT 1 AS c1,1 AS Level) t1
UNION ALL
SELECT t2.*,MyCTE.Level+1 FROM (SELECT 1 AS c1) t2 JOIN MyCTE ON t2.c1=MyCTE.c1
WHERE MyCTE.Level<=9999)
SELECT Level FROM MyCTE
OPTION (MAXRECURSION 0)

نکته ظریف کار اینجاست که جداول مجازی t1 و t2 هر دو، عدد ثابت 1 رو بعنوان فیلد c1 تا آخر کار باید استفاده کنند تا همیشه Join بینشون جواب بده.
اما صرف نظر از محقق شدن هدف به این روش، چیزی که من رو غافلگیر کرد Performance قابل توجه CTE بود! اگر چه دستور SET STATISTICS TIME ON میتونه جزئیات زمانی که برای انجام کار صرف شده رو نمایش بده، اما مطالعه نتایج این دستور برای روش اول کمی مشکله چون تعداد زیادی INSERT داره. لذا برای اینکه راحت تر به اختلاف سرعت بین این دو روش پی ببریم از این ترفند قدیمی استفاده میکنیم، مقایسه ساعت قبل و بعد از اجرای دستور:

روش اول:


DECLARE @time1 DATETIME, @time2 DATETIME
SET @time1=GETDATE()

DECLARE @Counter SMALLINT
SET @Counter=1
DECLARE @Output TABLE (c1 SMALLINT)

WHILE @Counter<=10000
BEGIN
INSERT @Output VALUES(@Counter)
SET @Counter=@Counter+1
END

SELECT * FROM @Output

SET @time2=GETDATE()
SELECT DATEDIFF (ms,@time1,@time2)


روش دوم:


DECLARE @time1 DATETIME, @time2 DATETIME
SET @time1=GETDATE()

;WITH MyCTE AS
(SELECT t1.* FROM (SELECT 1 AS c1,1 AS Level) t1
UNION ALL
SELECT t2.*,MyCTE.Level+1 FROM (SELECT 1 AS c1) t2 JOIN MyCTE ON t2.c1=MyCTE.c1
WHERE MyCTE.Level<=9999)
SELECT Level FROM MyCTE
OPTION (MAXRECURSION 0)

SET @time2=GETDATE()
SELECT DATEDIFF (ms,@time1,@time2)


در آخرین دستور، تابع DATEDIFF اختلاف دو زمان رو بر حسب میلی ثانیه نشون میده. تفاوت Performance در Rangeهای بزرگتر به مراتب بیشتر هم میشه.
اما جذابیت CTE به همینجا ختم نمیشه! CTE در توابع و Viewها قابل استفاده هستش و ما میتونیم در کنار Inline Table Valued Functions کار رو به شکل قشنگ تری ارائه کنیم:


CREATE FUNCTION fn_GetRows(@From INT, @TO INT)
RETURNS TABLE AS RETURN
WITH MyCTE AS
(SELECT t1.* FROM (SELECT 1 AS c1,@From AS Level) t1
UNION ALL
SELECT t2.*,MyCTE.Level+1 FROM (SELECT 1 AS c1) t2 JOIN MyCTE ON t2.c1=MyCTE.c1
WHERE MyCTE.Level<=@TO-1)
SELECT Level FROM MyCTE
GO

SELECT * FROM fn_GetRows(1,10000)
OPTION (MAXRECURSION 0)

SELECT * FROM fn_GetRows(-500,500)
OPTION (MAXRECURSION 0)


حالا fn_GetRows رو میشه با جداول دیگه Join کرد.
علت استفاده از OPTION MAXRECURSION این هست که بطور پیش فرض، CTE تا 100 مرتبه تو در تو میشه، اما عدد صفر در OPTION به معنی درخواست برای نامحدود بودن این عمل هست.
داشتن تابعی مثل fn_GetRows در جای خودش نیکوست! مثلا اگر جدول Orders فیلد Identity بعنوان OrderID استفاده کرده و شما قصد دارین OrderIDهای حذف شده (یا به عبارتی Gapهای موجود) رو تشخیص بدین به کمک fn_GetRows و یک Outer Join این کار امکان پذیره. برای نمونه من جدول Gaps رو میسازم و چند عدد که بینشون فاصله هست وارد میکنم و بعد Gapها رو با fn_GetRows بدست میاریم:


CREATE TABLE Gaps(
c1 INT)
GO

INSERT Gaps VALUES(1)
INSERT Gaps VALUES(3)
INSERT Gaps VALUES(6)
INSERT Gaps VALUES(9)
INSERT Gaps VALUES(11)
GO

SELECT f.* FROM
(SELECT (SELECT MIN(c1) FROM Gaps) AS MinC1,(SELECT MAX(c1) FROM Gaps) AS MaxC1) MinMax
CROSS APPLY fn_GetRows(MinC1,MaxC1) f
LEFT JOIN Gaps g ON f.Level=g.c1
WHERE g.c1 IS NULL
OPTION (MAXRECURSION 0)




و در خاتمه:
زبان قدرتمند TSQL در SQL Server 2005 سعی در ارث بری هر چه بیشتر از استاندارد ANSI SQL داشته و به بلوغ خاصی رسیده. دستورات TSQL قابلیتهایی دارن که نه تنها در نگاه اول، بلکه حتی در نگاه دوم هم قابل رویت نیستند!! اما با دقت بیشتر و درک بهتر از نحوه عملکرد اونها کارهای جدیدی امکان پذیر میشن. حتی بعضا این قابلیتها فراتر از ANSI، و البته خاص TSQL هستند. مثلا Table Operatorهایی از جمله PIVOT, UNPIVOT و APPLY (که در مثال بالا استفاده شد) در ANSI SQL وجود ندارند.
پس TSQL را پاس بداریم!

[MajerajooyeKhallagh]

آقای ثباتی ممنون از اینکه تجربیات جالبتونو در اختیارمون میگذارید,مطلب خیلی جالبی بود

[حمیدرضاصادقیان]

البته با اجازه استاد ثباتی این هم لینک خارجیش!!!!!
http://www.sqlservercentral.com/columnists/phe/2926.asp

یک سوال.نمیشه اینکارو در sql2000 انجام داد؟

[AminSobati]

البته با اجازه استاد ثباتی این هم لینک خارجیش!!!!!
http://www.sqlservercentral.com/columnists/phe/2926.asp

یک سوال.نمیشه اینکارو در sql2000 انجام داد؟
ممنون حمیدرضا جان، البته این لینک در مورد پیمایش درختی بود ولی خوب به هر حال از CTE استفاده کرده بود.
در 2000 کدوم کار منظور شماست؟ تولید اعداد یا پیمایش درختی؟

[حمیدرضاصادقیان]

اگر هر دو روش باشه که خیلی عالیه . یعنی هر دو روش الان به شدت مورد نیاز من هست در sql 2000 .

[AminSobati]

برای تولید اعداد که از طریق Query مطلق من راهی براش سراغ ندارم. مگر اینکه از loop استفاده کنین. برای پیمایش درختی از بالا به پایین، باز هم از طریق Loop میتونین اول Select بزنین و Childهای مستقیم یک آیتم رو بریزید داخل جدول موقتی، حالا این جدول رو Join کنین با جدول اصلی و Childهای سری بعدی رو بیارین و الی آخر:


DECLARE @ManagerID int
SET @ManagerID =2

--holds the output treelevel lets us isolate a level in the looped query
DECLARE @outTable table (employeeId int, ManagerID int, treeLevel int, processed bit)

--used to hold the level of the tree we are currently at in the loop
DECLARE @treeLevel as int
SET @treelevel = 1

--get the top level
INSERT into @outTable
SELECT employeeId, ReportsTo, @treelevel as treelevel, 0
FROM dbo.employees as employees
WHERE (employees.ReportsTo = @ManagerID)

WHILE (1 = 1) --imitates do...until construct
BEGIN

INSERT INTO @outTable
SELECT employees.employeeId, employees.ReportsTo,
ht.treelevel + 1 as treelevel,0
FROM dbo.employees as employees
JOIN @outTable as ht
ON employees.ReportsTo = ht.employeeId
--this where isolates a given level of the tree
WHERE ht.processed=0

IF @@rowcount = 0 BREAK


UPDATE @outTable SET processed=1
WHERE treeLevel-1<@treelevel

SET @treelevel = @treelevel + 1
END

--now look at the data in context of the employees we have inserted.
select * from @outTable

[حمیدرضاصادقیان]

http://www.sqlservercentral.com/columnists/jSebastian/2944.asp

[رضا عربلو]

به نظر می رسد که استفاده از Recursive بایتسی با بزرگتر شدن حد بالایی منجر به افزایش تصاعدی تعداد عملیات های لازم و در نتیجه زمان گردد ولی من تست کردم زمان تقریبا بصورت خطی به تعداد اعداد تولید شده بستگی دارد.
چرا؟

[AminSobati]

رضا جان وقتی شما Range بزرگتر نیاز دارین، فقط تعداد Recursion رو به همون تعداد زیاد میکنین، پس تصاعد هندسی در کار نیست

[پرواز]

خیلی جالب بود آقای ثباتی. مقاله شما رو خوندم و کدهایی که نوشته بودین رو تو SQL 2005 اجرا کردم و جواب گرفتم. ولی یه مشکل دارم: اونم اینه که از CTEها هیچی نمی دونم!!!
اگه اینجا جاش هست که یه توضیح مختصر بدین. مثلا رو همون کدهایی که نوشتین توضیح بدین و بگین چیکار می کنه.
با تشکر

[shaghayegh_6113]

سلام
آقای ثباتی می شه لطف کنید و این دستورات رو توی Sql2000 هم بگید

[AminSobati]

متاسفانه روش بهینه و ایده آلی وجود نداره

[s_ahmad]

if exists (select * from sysobjects where id = object_id(N'fn_GetRows') and xtype in (N'FN', N'IF', N'TF'))
drop function fn_GetRows
GO
create function fn_GetRows(@From int, @TO int)
returns table
as
return
with tbl as
(
select @From val
union all
select val + 1 val
from tbl
where val < @TO
)
select val from tbl
GO

select * from fn_GetRows(1, 5000) option (maxrecursion 0)

[AminSobati]

سریعترین روش:



WITH
sq1 as (SELECT 1 AS C1 UNION ALL SELECT 1),
sq2 as (SELECT t1.C1 FROM sq1 t1 CROSS JOIN sq1 t2),
sq3 as (SELECT t1.C1 FROM sq2 t1 CROSS JOIN sq2 t2),
sq4 as (SELECT t1.C1 FROM sq3 t1 CROSS JOIN sq3 t2),
sq5 as (SELECT t1.C1 FROM sq4 t1 CROSS JOIN sq4 t2)
SELECT ROW_NUMBER() OVER(ORDER BY C1) FROM sq5

[تولائی]

سلام
من نمي‌دونم اين CTE چيه و چجوري کار مي‌کنه. مستندات مربوطه رو هم خوندم چيزي سرم نشد. براي خودم اين مسئله جالبي بود. مسئله توليد يک رشته از اعداد.
جناب AminSobati دو راه حل ارائه کرده بودند و با توجه به زمان اجرا گفته بودند که راه حل اول بهتر از راه حل دوم‌ه.
راه حل اول اين‌ه.


DECLARE @Counter SMALLINT
SET @Counter=1
DECLARE @Output TABLE(c1 SMALLINT)
WHILE @Counter<=10000
BEGIN
INSERT @Output VALUES(@Counter)
SET @Counter=@Counter+1
END
SELECT*FROM @Output

اما روش ديگه‌اي براي بدست آوردن رشته اعداد مبتني بر همين روش اول هست که من در زير آورده‌ام:


DECLARE @time1 DATETIME, @time2 DATETIME
SET @time1=GETDATE()
DECLARE @Counter INT
SET @Counter=1
DECLARE @Output TABLE(c1 int)
declare @max asint
select @max = 100000
insert @Output values(@counter)
SET @Counter=@Counter+1
insert @Output values(@counter)
SET @Counter=@Counter+1
insert @Output values(@counter)
WHILE @Counter<=@max
BEGIN
INSERT @Output select @Counter + c1 from @output where @Counter + c1 <= @max
SET @Counter=@Counter*2
END
SELECT*FROM @Output
SET @time2=GETDATE()
SELECTDATEDIFF(ms,@time1,@time2)

سرعت اين روش بخوبي روش دوم‌ه ( با همون استدلال براي رد روش اول).
اما يک راه حل سومي هم ارائه شد. اون اين راه حل بود.


WITH
sq1 as (SELECT 1 AS C1 UNION ALL SELECT 1),
sq2 as (SELECT t1.C1 FROM sq1 t1 CROSS JOIN sq1 t2),
sq3 as (SELECT t1.C1 FROM sq2 t1 CROSS JOIN sq2 t2),
sq4 as (SELECT t1.C1 FROM sq3 t1 CROSS JOIN sq3 t2),
sq5 as (SELECT t1.C1 FROM sq4 t1 CROSS JOIN sq4 t2)
SELECT ROW_NUMBER() OVER(ORDER BY C1) FROM sq5

بنظر مياد که ايده اصلي اين‌ه که بيايم يک جدول رو با خودش Cross join کنيم تا حجم بيشتر 1 رو توليد کنيم. خوب اگه همين ايده رو بگيريم و بدون CTE بخوایم بنویسیم یک همچین چیزی از آب در میاد:


DECLARE @time1 DATETIME, @time2 DATETIME
SET @time1=GETDATE()
declare @maintbl Table(a intNOTNULLIDENTITY(1, 1),
b intnotNULL)
declare @tbl Table(c1 int)
declare @count int
insertinto @tbl select 1 union allselect 1
insertinto @maintbl
select t1.c1 from
(select t1.c1 from
(select t1.c1 from
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t1 crossjoin
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t2) t1 crossjoin
(select t1.c1 from
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t1 crossjoin
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t2) t2) t1 crossjoin
(select t1.c1 from
(select t1.c1 from
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t1 crossjoin
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t2) t1 crossjoin
(select t1.c1 from
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t1 crossjoin
(select t1.c1 from @tbl t1 crossjoin @tbl t2) t2) t2 ) t2
select a from @maintbl
SET @time2=GETDATE()
SELECTDATEDIFF(ms,@time1,@time2)

اگه بجای یک جدول با ستون IDENTITY از row_number استفاده بشه سرعت اين روش هم بخوبي روش ارائه شده توسط آقاي AminSobati می‌شه. فقط تفاوتش تو اين‌‌ه که اين روشي که من استفاده کردم براي کسايي که مي‌خوان با Sql server 2000 کار کنند قابل استفاده‌ست و براي مني که CTE نمي‌دونم قابل فهم‌تره. هرکي مي‌دونه که CTE چيه لطف کنه لينک بزاره وگر نه مجبور مي‌شم يک پست ايجاد کنم.
متشکرم