سوال در مورد شرط Update

[Reza_Yarahmadi]

سلام به همه دوستان
ميخوايم يه SP‌ بنويسيم كه 2 تا پارامتر ورودي داره يكي id و ديگري DIds ، پارامتر اول از نوع عددي و دومي بصورت رشته اي ، فرمت هر دو متغير به صورت زير

id = 1024
DIds = '1254,245,12545,120,249,3546'حالا بايد تمام ركوردهايي كه فيلد DId اونها برابر يكي از مقدارهاي درون رشته است با مقدار id جايگزين بشه.
بدليل اينكه تعداد دفعات اجراي اين SP بالاست و تعداد ركوردها هم بيش از چند ميليون ميشه سرعت اجرا از اهميت زيادي برخورداره. اين كار رو به روشهايي انجام دادم مثل جدا كردن كدهاي درون رشته و بررسي جدا جدا توي يك حلقه ، ويا شرط رو با استفاده از متد CharIndex پياده سازي كردم. ولي همه اينها زمان اجراي بالايي دارن چون يا بررسي رشته رو دارن يا حلقه.
دنبال روشي هستم كه سريعتر از اينها باشه
دوستان اگه راهنماييم كنيد ممنون ميشم

[بهزادصادقی]

من دارم روی مسئله ات کار می کنم. حالا تا به نتیجه برسه، دو تا سوال:

1. میگی "با مقدار id جایگزین بشه." جی با id جایگزین بشه. کدام ستون؟

2. می گی زمان اجرایی sp تو بالاست. خوب درسته، SQL Server برای پردازش رشته ها ساخته نشده و این نوع عملیات زمان می برند. ولی معمولا سرعت این عملیات نسبت به سرعت یک select خیلی بالا تر است. تو از کجا می دانی کدام قسمت کد stored procedure شما یواش است؟ آیا به execution plan کد خود نگاه کردی؟به نظر من احتمال هست که مشکل عملکرد sp تو در آنجاش نیست که تو فکر می کنی. می شه کد sp ات را برایم بفرستی من یک نگاهی بکنم؟

[AminSobati]

http://www.sommarskog.se/arrays-in-sql.html

[Reza_Yarahmadi]

ممنون از جوابتون
با روش زير يك مقدار بهترش كرديم
declare @DIds varchar(max), @Id bigint

set @DIds = '1254,245,12545,120,249,3546'
set @Id = 1024

declare @query varchar(max),@IdStr Varchar(10)
Set @IdStr = cast(@id as nvarchar)
set @query =
'Update ACC_AccDocRow set DId = ' + @IdStr + 'Where DId in (' + @DIds + ')
Update ACC_YEARS set DId = ' + @IdStr + 'Where DId in (' + @DIds + ')
Update CRDT_UsingCredit set DocId = ' + @IdStr + 'Where DocId in (' + @DIds + ')
Update dbo.ACC_AccDoc set DocIdAtOrg = ' + @IdStr + 'Where DocIdAtOrg in (' + @DIds + ')
Update STK_GoodsExport set DocId = ' + @IdStr + 'Where DocId in (' + @DIds + ')
Update STK_GoodsImport set AccDocId = ' + @IdStr + 'Where AccDocId in (' + @DIds + ')
Delete from ACC_AccDoc Where id in (' + @DIds + ')'
exec (@query)


تو از کجا می دانی کدام قسمت کد stored procedure شما یواش است؟ آیا به execution plan کد خود نگاه کردی؟يك روش ساده (اضافه بر Execution Plan) اينه كه قبل از در ابتدا و انتهاي اسكريپت زمان سرور رو بدست بياري و اختلاف اين دو مقدار تقريبا زمان اجراي دستورات است (البته توي تكرار بالا ميشه به نتيجه درست رسيد)

[aghayex]

UPDATE table1 SET id=@id where id=in(sustring(dids,0,3),sustring(dids,4,3),substr ing(dids,7,3),substrin(dids,10,3)

[بهزادصادقی]

يك روش ساده (اضافه بر Execution Plan) اينه كه قبل از در ابتدا و انتهاي اسكريپت زمان سرور رو بدست بياري و اختلاف اين دو مقدار تقريبا زمان اجراي دستورات است (البته توي تكرار بالا ميشه به نتيجه درست رسيد)

فرمایش شما کاملا صحیح است. شما می توانید از این روش ساده استفاده نمایی و راحت ببینی که کد شما یواش است. ولی این روش به شما نمی گوید که چرا کد شما یواش است، یا اینکه کجای کد شما یواش است.

مثلا، اگر هر یک از این جداولی که شما داری update می کنی ایندکس مناسب نداشته باشند، عمل update آن جدول می تواند خیلی زمان ببرد. خیلی بیشتر از زمانی که SQL Server لازم دارد تا رشته Dids شما را parse کند.

Execution Plan این query دقیقا به شما نشان خواهد داد که کدامین قدم این کد زمان بر است. بعد شما می توانید نسبت به رفع اشکال عملکردی آن اقدام نمایید.

شما می توانی ایندکس های این جداول را چک کنی. برای اینکه سرعت کد شما بالا باشد، هر یک از جداول باید روی آن ستونی که توی قسمت where و set دستور update آن جدول آورده ای یک ایندکس داشته باشند. مثلا، جدول STK_GoodsExport باید روی ستون DocId یک ایندکس داشته باشد.

اگر تعداد سطرهای این جداول زیاد است، من احتمال می دهم شاید مشکل شما به جدول Acc_AccDoc برگردد. چون شما دارید یک بار این جدول را update می کنید و یک بار آن را delete می کنید. برای update، جمله where شما دارد روی ستون DocIdAtOrg شرط اعمال می کند. برای delete، جمله where این کار را دارد روی ستون id انجام می دهد. یعنی برای اینکه این query سریع باشد، جدول Acc_AccDoc حداقل باید دارای دو ایندکس مجزا باشد. یک ایندکس روی ستون id و یک ایندکس روی ستون DocIdAtOrg. من حدس می زنم شما احتمالا فقط یک ایندکس اینحا داری و آن روی ستون id است.

[بهزادصادقی]

این هم کد من برای شما. فرض بر این است که شما ایندکس های مناسب دارید. وگرنه این هم یواش خواهد بود.



/*
create table ACC_AccDocRow (Did int);
insert ACC_AccDocRow select 1254;

create table ACC_YEARS (Did int);
insert ACC_YEARS select 245;

create table CRDT_UsingCredit (DocId int);
insert CRDT_UsingCredit select 12545;

create table ACC_AccDoc (id int, DocIdAtOrg int);
insert ACC_AccDoc select 1, 120;
insert ACC_AccDoc select 120, -1000;

create table STK_GoodsExport (DocId int);
insert STK_GoodsExport select 249;

create table STK_GoodsImport (AccDocId int);
insert STK_GoodsImport select 3546;
*/

if object_id( 'dbo.ProcessDids' ) is not null
drop proc dbo.ProcessDids;
go

create proc dbo.ProcessDids
(
@id int,
@list nvarchar(max)
)
as
begin

declare @dids table( number int );
declare @pos int, @nextpos int, @valuelen int;

select @pos = 0, @nextpos = 1;

while @nextpos > 0 begin

set @nextpos = charindex(',', @list, @pos + 1);
set @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos ELSE len(@list) + 1 END - @pos - 1;
insert @dids (number) VALUES (convert(int, substring(@list, @pos + 1, @valuelen)));
set @pos = @nextpos;

end;

update ACC_AccDocRow set DId = @id from @dids d where Did = d.number;
update ACC_YEARS set DId = @id from @dids d where Did = d.number;
update CRDT_UsingCredit set DocId = @id from @dids d where DocId = d.number;
update dbo.ACC_AccDoc set DocIdAtOrg = @id from @dids d where DocIdAtOrg = d.number;
update STK_GoodsExport set DocId = @id from @dids d where DocId = d.number;
update STK_GoodsImport set AccDocId = @id from @dids d where AccDocId = d.number;
delete ACC_AccDoc from @dids d where id = d.number;

end;
go

/*

select * from ACC_AccDocRow;
select * from ACC_YEARS;
select * from CRDT_UsingCredit;
select * from ACC_AccDoc;
select * from STK_GoodsExport;
select * from STK_GoodsImport;

exec dbo.ProcessDids 1024, '1254,245,12545,120,249,3546';

select * from ACC_AccDocRow;
select * from ACC_YEARS;
select * from CRDT_UsingCredit;
select * from ACC_AccDoc;
select * from STK_GoodsExport;
select * from STK_GoodsImport;
*/

[Reza_Yarahmadi]

از همگی ممنون
کدی که دوستمون aghayex (http://barnamenevis.org/forum/member.php?u=132075) نوشتن یک کد ثابت است اون اعدادی که من گذاشتم نمونه بود ممکن است در اجرا 500 عدد با تعداد ارقام متفاوت به این SP فرستاده شود به همین خاطر کد شما غیر قابل استفاده است.
دوست عزیز بهزادصادقی (http://barnamenevis.org/forum/member.php?u=161058) در مورد توضیح اولتون باید عرض کنم که ایندکش گذاری مناسب روی تمام جدولها به درستی انجام شده و از این نظر مشکلی نداریم و در مورد کدتون هم در پست اول عرض کردم که نمیخوایم از حلقه ها استفاده کنیم چون حلقه وابسته به تعداد است و اگر تعداد بالا زمان اجرا هم وحشتناک بالا میره چون دستورات درون حلقه چندید بار انجام میشن ، برای نمونه باید خدمتتون عرض کنم که اجرای کد به روش شما برای تقریبا 800 کد بیش از 100 ثانیه طول کشید!! در حالی که با کدی که خودم نوشتم کمتر از 5 ثانیه (نتیجه اینکه حلقه منتفی میشه)
خب حالا بازم به نظرتون راهی هست که این زمان رو باز هم کمترش کرد؟
به نظرم اگه بشه این داده ها رو بصورت یک جدول مجازی درآورد و توی شرط آپدیت از Join این جدول مجازی و جدول اصلی استفاده کرد زمانش بازم بهتر بشه ، ولی مشکل اینجاست که نمیدونم چطور به یک جدول مجازی تبدیلش کنم!! نمیخوام اعداد رو تتوی یک TempTable هم بریزم چون ممکنه در هر لحظه بیش از چند صد نفر بخوان این دستور رو صدا بزنن که این مشکل ساز میشه
بازم منتظر نظرات شما هستم

[behrouzlo]

می شه یک تابع Split پیاده سازی کرد که خروجی اش از نوع جدول باشه. من کد این تابع را از یک سایت برداشتم دقیقا یادم نیشت. ولی فکر کنم به درد شما هم بخورد.


Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items Numeric(18))
as
begin
declare @idx int
declare @slice Numeric

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

[Reza_Yarahmadi]

می شه یک تابع Split پیاده سازی کرد که خروجی اش از نوع جدول باشه. من کد این تابع را از یک سایت برداشتم دقیقا یادم نیشت. ولی فکر کنم به درد شما هم بخورد.دوست عزیز خدمتتون عرض کردم که نباید از TempTable استفاده بشه چون فشار زیادی به SQLServer میاره جدول مجازی چیزی مثل View است و فقط از حافظه از رم میگیره ولی TempTable از هارد

[بهزادصادقی]

دوست عزیز بهزادصادقی (http://barnamenevis.org/forum/member.php?u=161058) در مورد توضیح اولتون باید عرض کنم که ایندکش گذاری مناسب روی تمام جدولها به درستی انجام شده و از این نظر مشکلی نداریم و در مورد کدتون هم در پست اول عرض کردم که نمیخوایم از حلقه ها استفاده کنیم چون حلقه وابسته به تعداد است و اگر تعداد بالا زمان اجرا هم وحشتناک بالا میره چون دستورات درون حلقه چندید بار انجام میشن ، برای نمونه باید خدمتتون عرض کنم که اجرای کد به روش شما برای تقریبا 800 کد بیش از 100 ثانیه طول کشید!! در حالی که با کدی که خودم نوشتم کمتر از 5 ثانیه (نتیجه اینکه حلقه منتفی میشه)


آخ ببخشید. من صورت مسئله شما را کاملا وارونه درک کردم. به نظر می رسه مشکل سرعت شما برمی گرده به اینکه شما باید رشته هایی را با طول خیلی زیاد (تعداد زیادی کد) پردازش کنید. من فکر کردم طول رشته های شما کوتاه (مثلا همان پنج شیش کد) می باشد، ولی تعداد سطرهایی که باید این عمل را رویشان انجام دهید زیاد است. یعنی فکر کردم مشکل شما تعداد بالای اجرای این کد می باشد، نه طول عظیم یک رشته برای هر اجرا.

سوالی که برای من پیش می آید این است. این رشته ها را کی و چگونه به کد شما پاس می کند. آیا اینها در یک جدول دیگر ذخیره شده اند؟ آیا یک برنامه client آنها را به شما پاس می کند؟ آیا شما به هیچ وجه هیچ کنترلی روی کدی که دارد آن رشته ها را تولید یا پاس می کند دارید؟

اگر شما هیچ کنترلی روی آن طرف قضیه ندارید، و مجبورید مقدار زیادی string processing را در سمت سرور انجام دهید، اولین راه حلی که به نظر می رسد تولید یک CLR UDF یا CLR stored procedure می باشد که ورودی اش رشته شماست و خروجی اش یک result set جدولی می باشد. سپس شما می توانید آن خروجی را با استفاده از تکنیکی شبیه تکنیک کد من در بالا برای update و delete خود استفاده نمائید. معمولا string processing در CLR، به خصوص با استفاده از regular expressions به مراتب سریعتر از انجام همان کار در T-SQL می باشد.

با عرض پوزش دوباره از اشتباه فهمیدن صورت مسئله شما.