وب سایت سید حامد واحدی | Seyedhamed Vahedi Website

مقایسه کارایی Join جدول ها برای انواع داده ای INT و BIGINT و GUID


برای بررسی کارایی Join ، جداولی همانند زیر بصورت Parent/Detail ایجاد می کنیم که انواع داده ای INT و BIGINT و GUID به یکدیگر مرتبط باشند.
از دستورات زیر برای ایجاد جدول، ایجاد ایندکس و وارد کردن مقادیر تصادفی استفاده می کنیم:
--/// Parent Table
CREATE TABLE parent_int
                            ([pk_col] [INT] NOT NULL PRIMARY KEY CLUSTERED, 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE parent_bigint
                            ([pk_col] [bigint] NOT NULL PRIMARY KEY CLUSTERED, 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE parent_guid
                            ([pk_col] [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY CLUSTERED, 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));


--/// Detail Table
CREATE TABLE detail_int
                            ([pk_col] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
                             [join_col] [INT], 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE detail_bigint
                            ([pk_col] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
                             [join_col] [bigint], 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));

CREATE TABLE detail_guid
                            ([pk_col] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED, 
                             [join_col] [UNIQUEIDENTIFIER], 
                             [col1] [INT], 
                             [col2] [DATETIME], 
                             [col3] [VARCHAR](20));


--/// Add Relation
ALTER TABLE dbo.detail_int
ADD CONSTRAINT FK_detail_int_parent_int
FOREIGN KEY (join_col) REFERENCES dbo.parent_int (pk_col) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION;

ALTER TABLE dbo.detail_bigint
ADD CONSTRAINT FK_detail_bigint_parent_int
FOREIGN KEY (join_col) REFERENCES dbo.parent_bigint (pk_col) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION;

ALTER TABLE dbo.detail_guid 
ADD CONSTRAINT FK_detail_guid_parent_guid
FOREIGN KEY (join_col) REFERENCES dbo.parent_guid (pk_col) 
ON UPDATE  NO ACTION ON DELETE  NO ACTION;


--/// Add Index
CREATE INDEX detail_int_join_col ON dbo.detail_int (join_col);
CREATE INDEX detail_bigint_join_col ON dbo.detail_bigint (join_col);
CREATE INDEX detail_guid_join_col ON dbo.detail_guid (join_col);


--/// Data Load
DECLARE @returnid table (pk_col uniqueidentifier)
DECLARE @val int
DECLARE @RANDval int
DECLARE @val2 int
DECLARE @pkint int
DECLARE @pkguid uniqueidentifier
DECLARE @pkseqguid uniqueidentifier
SELECT @val=1

WHILE @val < 200000 BEGIN 
   INSERT INTO parent_int (pk_col, col1, col2, col3) 
      VALUES (@val, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

   INSERT INTO parent_bigint (pk_col, col1, col2, col3) 
      VALUES (@val, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

   SELECT @pkint = @val;
   SELECT @pkguid = newid();

   INSERT INTO parent_guid (pk_col, col1, col2, col3) 
     VALUES (@pkguid, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

   SELECT @pkseqguid = r.pk_col
FROM @returnid r

   SELECT @RANDval=FLOOR(RAND()*(10-1)+1);
   SELECT @val2=1

   WHILE @val2 <= @RANDval BEGIN
      INSERT INTO detail_int (join_col, col1, col2, col3)
         VALUES (@pkint, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

      INSERT INTO detail_bigint (join_col, col1, col2, col3)
         VALUES (@pkint, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

      INSERT INTO detail_guid (join_col, col1, col2, col3)
         VALUES (@pkguid, ROUND(RAND()*100000, 0), GETDATE(), 'TEST' + CAST(@val AS VARCHAR));

      SELECT @val2=@val2+1
   END
   
   SELECT @val=@val+1
END


حال برای بررسی دستورات واکشی زیر را اجرا می کنیم؛ یک بار همه رکوردها و بار دیگر یک رکورد مشخص:
--/// Fetch All Records in Parent
SELECT *
FROM parent_int p
INNER JOIN detail_int d ON d.join_col=p.pk_col;

SELECT *
FROM parent_bigint p
INNER JOIN detail_bigint d ON d.join_col=p.pk_col;

SELECT *
FROM parent_guid p
INNER JOIN detail_guid d ON d.join_col=p.pk_col;

 
--// Fetch Single Record in Parent
SELECT *
FROM parent_int p
INNER JOIN detail_int d ON d.join_col=p.pk_col 
WHERE p.pk_col=121143;

SELECT *
FROM parent_bigint p 
INNER JOIN detail_bigint d ON d.join_col=p.pk_col 
WHERE p.pk_col=121143;

SELECT *
FROM parent_guid p 
INNER JOIN detail_guid d ON d.join_col=p.pk_col 
WHERE p.pk_col='A10B3C3C-3ABC-47CB-8DDE-22DC1DF89447';

اما نتیجه اجرای پرس و جوها با استفاده از Profiler به صورت زیر است:
Query CPU (ms) Reads Duration (ms)
parent_seqguid/Full Join 1295 6242 10431
parent_int/Full Join 1397 6832 11523
parent_guid/Full Join 1483 8616 14734
parent_bigint/Full Join 2512 8208 14008
parent_seqguid/Single Record 0 27 2
parent_int/Single Record 0 27 1
parent_guid/Single Record 0 27 2
parent_bigint/Single Record 0 27 1
سید حامد واحدی سید حامد واحدی     27 مهر 1396