بررسی یا عدم بررسی وجود رکورد به هنگام Insert

[supporter]

با سلام،
فرض کنید می خواهیم تعدای رکورد رو از یک جدول در چدول دیگری Insert کنیم
با فرض این که تنها تعداد کمی از رکوردها تکراری هستند به نظر شما این منطقیه که برای افزایش Performance به جای بررسی وجود رکورد در جدول دوم و Insert در صورت عدم وجود رکورد، همه رکوردها رو از جدول اول در جدول دوم Insert کنیم و به جای هزینه بررسی وجود یا عدم وجود رکورد در جدول دوم (باز هم یادآور می شم که در اکثر مواقع رکورد مورد نطر در چدول دوم وجود نداره) هزینه درج Duplicate Key رو (که به ندرت ممکنه اتقاق بیفته) متحمل بشیم.

[rosenth]

اگه بندرت تکراری هستند ، همه رو بفرست .فکر می کنم چون خود server مقایسه رو به جای شما انجام میده.

[SYNDROME]

با سلام

با سلام،
فرض کنید می خواهیم تعدای رکورد رو از یک جدول در چدول دیگری Insert کنیم
با فرض این که تنها تعداد کمی از رکوردها تکراری هستند به نظر شما این منطقیه که برای افزایش Performance به جای بررسی وجود رکورد در جدول دوم و Insert در صورت عدم وجود رکورد، همه رکوردها رو از جدول اول در جدول دوم Insert کنیم و به جای هزینه بررسی وجود یا عدم وجود رکورد در جدول دوم (باز هم یادآور می شم که در اکثر مواقع رکورد مورد نطر در چدول دوم وجود نداره) هزینه درج Duplicate Key رو (که به ندرت ممکنه اتقاق بیفته) متحمل بشیم.
بهتر است اطلاعات تکراری را درج نکنید.
چون درست است اگر به ندرت تکراری است ولی به هر حال تکراری وجود دارد و در بلند مورد اطلاعات تکراری شما بالا می رود و از لحاظ اصول پایگاه داده ها شما دارای افزونگی هستید.
موفق باشید

[supporter]

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

دوست عزیز توجه داشته باشید که رکورد تکراری درج نمی شه
بحث در مورد هزینه بررسی وجود یا عدم وجود رکورد در جدول دوم به هنگام Insert در مقایسه با هزینه Violation of primary key Constraint به هنگام درج رکورد تکراری در شرایط گقته شده (کم بودن تعداد رکوردهای تکراری) است.
در ضمن فرض کنید این شرایط همیشه برقراره.

[Batman]

بهتره اول رکوردهای تکراری رو با distinct فیلتر کنید و بعد با حلقه از جدول اولی به دومی کپی کنید

[AminSobati]

دوست عزیزم،
اگر در جدول مقصد، Primary Key یا Unique Constraint یا Unique Index داشته باشید در هر حال موقع Insert این چک شدن توسط SQL Server انجام خواهد شد. پس چک کردن توسط خود شما یک کار اضافی هست.

[supporter]

جناب ثباتی،
بنابراین به نطر شما تحت هر شرایطی (حتی زیادبودن تعداد رکوردهای تکراری) لازم نیست خودمون این بررسی رو انجام بدیم
من فکر می کردم در این شرایط کدی شبیه



IF NOT EXISTS( SELECT 1 FROM Table2 Where PK = @PK(
INSERT INTO Table2 Values(...)


Overhead کمتری نسبت به حالتی که بدون بررسی عدم وجود رکورد Insert رو انجام می دیم و اجازه می دیم Violation of primary key Constraint اتفاق بیفته داره.
حالا این تصور چه جوری برام پیش اومده خودم هم نمی دونم. شاید به خاطر این که در حالت دوم خطا داده می شه (و اصولا از هر پیغام خطایی می ترسم!) و فکر می کردم به هنگام بروز خطا SQL Server عملیات بیشتری باید انجام بده (درست یا غلطش رو دقیقا نمی دونم، ممنون می شم اگه در این مورد هم راهنمایی کنید) و یا مثلا به خاطر این که فکر می کردم دستور Insert اصولا Resource بیشتری نسبت به Select می گیره این تصور برام پیش اومده.

[AminSobati]

به نظر من Raise شدن یک Exception به مراتب Cost کمتری در مقایسه با Query شما جهت پیدا کردن رکورد تکراری داره! به هر حال در 2005 میتونین Try Catch استفاده کنین تا از بروز خطا هم نترسین

[supporter]

من هر دو حالت رو تست کردم به نظر میرسه اگر چه روش دوم به هنگام Insert (تکراری نبودن رکوردها کمی بیشتر زمان صرف می کند) ولی وقتی تعداد رکوردهای تکراری جدول زیاده
روش اول چندین برابر بیشتر طول می کشه.
من همین تست رو برای حالتی که 50000 رکورد از رکوردهای Insert شونده تکراری بودند تست کردم روش اول حدود 6 دقیقه طول کشید در حالی که روش دوم در حدود 15 ثانیه اجرا شد.




CREATE TABLE TblTest1 (
Code int NOT NULL
CONSTRAINT PK_TblTest1 PRIMARY KEY CLUSTERED
(
Code
)
)
GO
CREATE TABLE TblTest2 (
Code int NOT NULL
CONSTRAINT PK_TblTest2 PRIMARY KEY CLUSTERED
(
Code
)
)
GO

CREATE PROCEDURE SpTest1 (@RecordNO INT)
AS
SET NOCOUNT ON
DECLARE @n INT,
@RC INT,
@DupKeyCount INT
SET @n = 1
SET @DupKeyCount = 0
WHILE @n <= @RecordNO
BEGIN
INSERT INTO TblTest1(Code)
VALUES (@n)
IF @@Error <> 0
SET @DupKeyCount = @DupKeyCount + 1
SET @n = @n + 1
END
SELECT @DupKeyCount
SELECT * FROM TblTest1
GO

CREATE PROCEDURE SpTest2 (@RecordNO INT)
AS
SET NOCOUNT ON
DECLARE @n INT,
@RC INT,
@DupKeyCount INT
SET @n = 1
SET @DupKeyCount = 0
WHILE @n <= @RecordNO
BEGIN
IF EXISTS (SELECT Code FROM TblTest2 WHERE Code = @n)
SET @DupKeyCount = @DupKeyCount + 1
ELSE
INSERT INTO TblTest2(Code)
VALUES (@n)
SET @n = @n + 1
END
SELECT @DupKeyCount
SELECT * FROM TblTest2
GO
EXEC SpTest1 5000
EXEC SpTest1 6000
EXEC SpTest2 5000
EXEC SpTest2 6000
-- DROP Procedure SpTest1
-- DROP Procedure SpTest2
-- Drop Table TblTest1
-- Drop Table TblTest2

[shahroozj]

ببخشید مگه این کد چه اشکالی داره که این همه دارید با هم بحث می کنید :لبخندساده:

insert into Table1(field1, field2)
select field1, field2 from Table2
where code not in (select Code from Table1)

[supporter]

ببخشید مگه این کد چه اشکالی داره که این همه دارید با هم بحث می کنید :لبخندساده:

insert into Table1(field1, field2)
select field1, field2 from Table2
where code not in (select Code from Table1)

دوست عزیز بحت در مورد بررسی یا عدم بررسی وجود رکورد به هنگام Insert (http://barnamenevis.org/forum/showthread.php?t=74113) در یک جدول و تاثیر اون در Performance است و نه پیدا کردن روش Insert.(که اگر قرار بود کدی مثل کد شما برای این کار بنویسیم مسلما به جای IN از EXISTS می کردیم!)
شما می تونید این طور فرض کنید که بنا به دلایلی ما مجبوریم رکوردهایی را یکی یکی در جدولی Insert کنیم حال در این حالت بهتره موفع Insert هر رکورد وجود یا عدم وجود آن را بررسی کنیم یا نه ...

[AminSobati]

من هر دو حالت رو تست کردم به نظر میرسه اگر چه روش دوم به هنگام Insert (تکراری نبودن رکوردها کمی بیشتر زمان صرف می کند) ولی وقتی تعداد رکوردهای تکراری جدول زیاده
روش اول چندین برابر بیشتر طول می کشه.
من همین تست رو برای حالتی که 50000 رکورد از رکوردهای Insert شونده تکراری بودند تست کردم روش اول حدود 6 دقیقه طول کشید در حالی که روش دوم در حدود 15 ثانیه اجرا شد....

دوست عزیز ممنونم از تست خوبی که فرستادین. در حقیقت سرعت پایین در روش اول، ناشی از Raise شدن خطا نیست، بلکه تا Insert انجام نشه، رکورد تکراری هم تشخیص داده نمیشه. در روش دوم بدون هزینه Insert، تکرار تشخیص داده میشه. اما این رو هم در نظر بگیرین که در دفعه دوم اجرای هر کدوم از SPها، تمام 50000 رکورد تکراری هستند که شاید سناریوی شما نباشه. همونطور که حتما تست کردین، در شرایطی که رکوردها تکراری نباشند و تعداد زیاد باشه، روش اول بهتر عمل میکنه

[supporter]

اما این رو هم در نظر بگیرین که در دفعه دوم اجرای هر کدوم از SPها، تمام 50000 رکورد تکراری هستند که شاید سناریوی شما نباشه. همونطور که حتما تست کردین، در شرایطی که رکوردها تکراری نباشند و تعداد زیاد باشه، روش اول بهتر عمل میکنه

در حالت دوم از 100000 رکورد 50000 رکورد تکراری بود. یعنی در مرحله اول کدهای 1 تا 50000 و در مرحله دوم کدهای 1 تا 100000 رو Insert کردم.
همون طور که قبلا هم گفتم سرعت اجرای این دو SP در مرحله دوم اختلاف بسیار زیادی با هم داشتند(در حدود 6 دقیقه روش دوم سریعتر اجرا شد.)
تست دیگه ای که انجام شد به این شکل بود که در مرحله اول کدهای 1 تا 5000 و در مرحله دوم رکوردهای 50001 تا 100000 رو Insert کردم یعنی هیج رکورد تکراری بین رکوردهای درج شونده وجود نداشت در این حالت روش اول تنها 3 ثانیه سریعتر از روش دوم اجرا شد.
من سعی میکنم این وضعیت برای حالت های مختلف تست کنم اگه چیز بدرد بخوری از آب در اومد پست می کنم شاید به درد بقیه دوستان هم خورد.

[سار]

به نظر من Raise شدن یک Exception به مراتب Cost کمتری در مقایسه با Query شما جهت پیدا کردن رکورد تکراری داره! به هر حال در 2005 میتونین Try Catch استفاده کنین تا از بروز خطا هم نترسین

ضمن اینکه معتقدم شما درست میگید ولی اگر وجود و یا عدم وجود رکوردی فقط بستگی به PK نداشته باشه چی؟
مثلن در طراحی که به من دادن هر جدول دارای فیلدی از نوع Bit هست که در واقع با اون عمل Delete رو به شکل منطقی انجام میده در واقع False بودن اون رکورد مشخص میکنه که حذف شده یا نه و بعد به سراغ PK میره.
در چنین حالتی نظر شما در مورد کد زیر چیه و اصلن آیا این روش(حذف منطقی) رومی پسندید یا خیر؟


DECLARE @RId UNIQUEIDENTIFIER
SELECT @RId = R.RequestId FROM Answer R WHERE(R.QuestionID = @QuestionID)and(R.FormId=@FormId)and(Active = 1)
IF(@RId ISNULL)
INSERT INTO Answer
(QuestionID, RequestID, Answer,FormId)
VALUES(@QuestionID,@RequestID,@Answer,@FormId)
else UPDATE Answer
SET Answer = @Answer
where(QuestionID = @QuestionID)and(FormId = @FormId)and(Active = 1)

در بالا QuestionID و FormId با هم کلید هستن.
تشکر.

[AminSobati]

دوست عزیزم،
روش حذف منطقی رو اگر صرفا به خاطر افزایش سرعت باشه، پیشنهاد نمیکنم. این کار معضلات خودش رو هم به دنبال خواهد داشت. اولا همیشه موقع دریافت اطلاعات، باید شرط Flag=1 رو در Query قرار بدین که اگر ایندکس مناسب وجود نداشته باشه، Table Scan میگیرین. از طرفی اگر ایندکس بخواین قرار بدین، باید تعداد زیادی فیلد رو در ایندکس بیارین تا Query رو بتونین Cover کنین.
حذف منطقی برای مقاصد خاصی میتونه استفاده بشه

[supporter]

من چند حالت رو تست کردم؛ شاید به درد دوستان خورد.
دوستان توجه داشته باشند که زمانهای اجرا بر حسب ثانیه هسنند و مقدار صفر به این معنی است که مدت زمان اجرای SP کمتر از یک ثانیه بوده است.
در مجموع نتیجه گیری من این بود که اگه تخمین درستی از تعداد رکوردهای تکراری ندارید حتما قبل از Insert رکورد، تکراری نبودن اون رو بررسی کنید.
اگه دوستان هم نظری دارند ممنون می شم اعلام کنند.

[AminSobati]

به غیر از شرایط خاص، به نظر من نیازی به چک کردن نیست. مثلا زمانی که جدول شما فیلد Identity داره (که اکثرا همینطوره) و IDهای یونیک به صورت اتوماتیک تولید میشن چه نیازی به چک کردن هست؟
شرایط خاص، میتونه Import کردن اطلاعات یا ادغام دو جدول باشه.

[SYNDROME]

با سلام
با تشکر از امین ثابتی همه دوستان که با نظراتشان بنده را راهنمایی کردند.


به غیر از شرایط خاص، به نظر من نیازی به چک کردن نیست.

به نظر من هم زمانی که خود SQLاین کار را به خوبی مدیریت می کند نیازی به نوشتن دستورات از طرف ما نیست.
مشکل من هم حل شد.
موفق و پایدار باشید