درخواست برای بهینه کردن این کویری

[naeeme]

ساختار جدول من به صورت زیر هست:

sdId ( bigint, PK)
ordid (bigint, FK)
baid (tinyInt, FK)
slId (bigint)
یک UK هم روی فیلدهای baId و slID دارم. فیلد sdId هم بطور خودکار مقدار میگیره.
من می خوام slId بطور مرتب از یک شروع و افزایش پیدا کنه به شرطی که برای هر baId، slId از یک شروع بشه و دو فیلد baId و slId در کنار هم مقدار یکتایی داشته باشند.
راه حلی که خودم استفاده کردم استفاده از از این کویری بود



SELECT @slId=max(slId)
FROM tb_examcode
WHERE baId=@baId

IF @slId IS NULL
SELECT @slId=0

SELECT @slId = @slId + 1

INSERT INTO tb_examcode
(odId, baId, slId)
VALUES (@odId,@baId,@slId);

SELECT @sdId=sdId
FROM tb_examcode
WHERE slId=@slId

اما مشکلی که وجود داره این هست که اولا در هنگام ترافیک بالا، این SP دقیق عمل نمی کنه و slId رو بطور مرتب درج نمی کنه و همین طور گاهی خطای درج دو مقدار یکسان رو میده. مشکل دیگه این هست که چون حجم اطلاعات بالا رفته، کند عمل می کنه. به خاطر استفاده از تابع MAX هم تابع کندتر از معمول هست.

دنبال راه حلی هستم که اولا دقیق باشه، یعنی واقعا بطور مرتب slId رو تولید کنه، ثانیا به سرعت جواب بده.
در مورد تعداد رکوردهای این جدول بگم که به راحتی در دو سال آینده به بالای 2 میلیون رکورد خواهد رسید و چون این سیستم حداقل طول عمر 4 ساله داره ژس برای تعداد رکورد بالای 5 میلیون باید به راحتی جواب بده.

[محمد سلیم آبادی]

اگر درست منظورتون را متوجه شده باشم. شما نیازی به یک ستون اضافه ای به نام slID ندارید و متقابلا نیازی به یک composit unique key نیز نیست. بلکه با استفاده از توابع امتیاز دهی (Ranking Functions) می توانید مقدار این ستون را محاسبه کنید (البته در صورت استفاده از نسخه های 2005 و 2008)

بطور مثال فرض کنید ستون baID مقادیر زیر را در خود ذخیره کرده است با اجرای query زیر مقدار ستون slID را نیز دست می آوریم:


/*
baID
-----------
1
1
2
3
3
3
*/
select i as baID
, ROW_NUMBER()
over(partition by i
order by i) as slID
from @t
/*
baID slID
----------- --------------------
1 1
1 2
2 1
3 1
3 2
3 3
*/

[naeeme]

اینطور که من متوجه شدم، این کد در واقع تمام اطلاعات یک جدول رو لیست می کنه و براساس یکی از فیلدها، اون ها رو رتبه بندی می کنه. پس اگر من یک شرط براش بذارم، مقدار slID در هربار تغییر میکنه. همچنین این روش کل رکوردها رو بیرون می کشه و من مثلا باید بروم آخرین رو بخونم.
در حالی که من چنین چیزی رو نمی خوام.

اولا سیستم من الان داره کار می کنه و تا حالا بالای 500هزار رکورد توی این جدول ثبت شده. پس نمی تونم خیلی ریسک کنم.
ثانیا مقدار این فیلد خیلی مهم هست و اگر خطا داشته باشم، ارتباطم با یک سیستم جانبی رو از دست میدم( این فیلد کد ارسال نامه به سیستم دیگه هست)
baId کد واحدی هست که نامه رو ارسال میکنه. هر واحد باید شماره نامه اش از یک شروع بشه.پس دوتا واحد می تونن شماره ارسال یکسانی داشته باشن اما sdId و ordId متفاوتی دارند.
از sdId نمی تونم استفاده کنم چون ممکنه واحد اول ماهی 100 ارسال داشته باشه اما واحد پنجم روزی 400 تا و به همین دلیل ممکنه شماره نامه‌های واحد اول یکی باشه 101 و بعدیش باشه 40006 که این غیر منطقی هست.

[naeeme]

بین این دو SP کدوم بهینه تره و احتمال خطای کمتری داره؟

1) کدی هست که الان داره کار می کنه و اجراش زمان بره.



SELECT @slId=max(slId)
FROM tb_examcode
WHERE baId=@baId

IF @slId IS NULL
SELECT @slId=0

SELECT @slId = @slId + 1

INSERT INTO tb_examcode
(odId, baId, slId)
VALUES (@odId,@baId,@slId);

SELECT @sdId=sdId
FROM tb_examcode
WHERE slId=@slId


2) کدی هست که می خوام جایگزین کد فعلی بکنم.




INSERT INTO tb_examcode
(odId, baId, slId)
select @odId,@baId,max(slId)+1
FROM tb_examcode
WHERE baId=@baId;

Select @sdId = @@IDENTITY

SELECT @slId=slId
FROM tb_examcode
WHERE sdId=@sdId AND odId=@odId