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

چند روز پیش برای منظور خاصی نیاز داشتم تا سمت سرور تعداد زیادی عدد متوالی در 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 را پاس بداریم!

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

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

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

نقل قول:

---

نوشته شده توسط حمیدرضاصادقیان

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

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

---

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

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

برای تولید اعداد که از طریق 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/colu...stian/2944.asp

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

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

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

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

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

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)

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

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 ALLSELECT 1),

sq2 as(SELECT t1.C1 FROM sq1 t1 CROSSJOIN sq1 t2),

sq3 as(SELECT t1.C1 FROM sq2 t1 CROSSJOIN sq2 t2),

sq4 as(SELECT t1.C1 FROM sq3 t1 CROSSJOIN sq3 t2),

sq5 as(SELECT t1.C1 FROM sq4 t1 CROSSJOIN sq4 t2)

SELECTROW_NUMBER()OVER(ORDERBY 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 چيه لطف کنه لينک بزاره وگر نه مجبور مي‌شم يک پست ايجاد کنم.
متشکرم