GUID auto-incremental ID table Association comparison

1. Only associated conditions have non-clustered Indexes

 

-- Clear Cache
Dbcc freeproccache with NO_INFOMSGS
Dbcc freesessioncache with NO_INFOMSGS
Dbcc freesystemcache ('all') WITH NO_INFOMSGS
Dbcc dropcleanbuffers with NO_INFOMSGS
 
Set nocount on
 
 
Create table # result
(
Status varchar (20 ),
[Id (MS)] INT,
[Guid (millisecond)] INT
)
 
DECLARE @ start INT
DECLARE @ end INT
SET @ start = 1
SET @ end = 30
 
WHILE (@ start <= @ end)
BEGIN -- starts a loop
 
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
Dbcc freesystemcache ('all ')
DBCC DROPCLEANBUFFERS
 

Declare @ d datetime
Set @ d = getdate ()
 
SELECT *
FROM BKListid
Join studentid
On BKListid. stuid = studentid. stuid
-- Where studentid. stuname in ('Liu ning', 'Liu Zhihua ', 'Liu Liping', 'pan huanqing ')
-- Where studentid. bzktypeid = 85 and studentid. stuname like 'Liu %'
Where BKListid. bzktypeid = 85 and studentid. stuname like 'Liu %'
Declare @ result int
Select @ result = datediff (MS, @ d, getdate ())

Declare @ d1 datetime
Set @ d1 = getdate ()
 
SELECT *
FROM [BKListguid]
Join studentguid
On BKListguid. bkguid = studentguid. stuguid
-- Where studentguid. stuname in ('Liu ning', 'Liu Zhihua ', 'Liu Liping', 'pan huanqing ')
Where BKListguid. bzktypeid = 85 and studentguid. stuname like 'Liu %'
Declare @ result1 int
Select @ result1 = datediff (MS, @ d1, getdate ())
 
Insert into # result
SELECT 'normal', @ result AS 'id (millisecond) ', @ result1 'guid (millisecond )'

 
SET @ start = @ start + 1
 
END
-- Loop ends

 
SELECT *
FROM # result
UNION ALL
SELECT 'minimal', min ([id (millisecond)]), min ([guid (millisecond)])
FROM # result

UNION ALL
SELECT 'average', AVG ([id (millisecond)]), AVG ([guid (millisecond)])
FROM # result
Where [id (millisecond)] not in (select top 2 [id (millisecond)]
From # result
Order by [id (MS)] desc)
And [id (millisecond)] not in (select top 2 [id (millisecond)]
From # result
Order by [id (MS)])
And [guid (millisecond)] not in (select top 2 [guid (millisecond)]
From # result
Order by [guid (millisecond)] desc)
And [guid (millisecond)] not in (select top 2 [guid (millisecond)]
From # result
Order by [guid (MS)])
UNION ALL
SELECT 'maxim', max ([id (millisecond)]), max ([guid (millisecond)])
FROM # result
 
 
-- Drop table # result

 

-- Create index stuname ON studentid (stuname)
-- Create index stuname ON studentguid (stuname)

/*
Create index stuid ON studentid (stuid)
Create index stuid ON bklistid (stuid)

Create index stuguid ON studentguid (stuguid)
Create index bkguid ON bklistguid (bkguid)
Sp_helpindex studentid
Sp_helpindex bklistid
Sp_helpindex studentguid
Sp_helpindex bklistguid
Drop index studentid. stuid
Drop index bklistid. stuid
Drop index studentguid. stuguid
Drop index bklistguid. bkguid
*/

Status id (MS) guid (MS)
Normal 3596 5313
Normal 3450 4893
Normal 3903 4580
Normal 3736 4863
Normal 3523 4626
Normal 3813 4543
Normal 3810 4763
Normal 3763 5086
Normal 3573 4860
Normal 3536 4580
Normal 3626 4930
Normal 3736 4373
Normal 3693 4560
Normal 3566 4530
Normal 3676 4450
Normal 3730 4373
Normal 3523 5020
Normal 3736 4580
Normal 3573 4530
Normal 3860 4500
Normal 3546 4566
Normal 3863 4530
Normal 3496 4610
Normal 3490 4856
Normal 3713 4903
Normal 3753 5076
Normal 3603 6483
Normal 5963 7203
Normal 4060 4990
Normal 3940 4950
Minimum 3450 4373
Average 3687 4736
Maximum 5963 7203

 

GUID failed

Bytes -----------------------------------------------------------------------------------------------------

2. The where condition field also has a non-clustered index with a small amount of data

Declare @ d datetime
Set @ d = getdate ()
 
SELECT *
FROM BKListid
Join studentid
On BKListid. stuid = studentid. stuid
Where studentid. stuname in ('Liu ning', 'Liu Zhihua ', 'Liu Liping', 'pan huanqing ')

Declare @ result int
Select @ result = datediff (MS, @ d, getdate ())

Declare @ d1 datetime
Set @ d1 = getdate ()
 
SELECT *
FROM [BKListguid]
Join studentguid
On BKListguid. bkguid = studentguid. stuguid
Where studentguid. stuname in ('Liu ning', 'Liu Zhihua ', 'Liu Liping', 'pan huanqing ')

----------------------------------------------------------------------------

 

Status id (MS) guid (MS)
Normal 453 370
Normal 426 763
Normal 420 386
Normal 440 376
Normal 426 526
Normal 436 376
Normal 563 576
Normal 430 466
Normal 430 376
Normal 503 376
Normal 436 466
Normal 426 396
Normal 430 430
Normal 426 533
Normal 426 370
Normal 446 410
Normal 426 486
Normal 586 690
Normal 520 383
Normal 530 446
Normal 430 503
Normal 793 433
Normal 436 490
Normal 660 440
Normal 470 440
Normal 460 470
Normal 576 583
Normal 660 376
Normal 470 510
Normal 436 390
Normal 470 470
Normal 430 690
Normal 536 506
Normal 503 420
Normal 436 596
Normal 453 370
Normal 463 583
Normal 463 370
Normal 620 430
Normal 436 656
Normal 826 386
Normal 450 463
Normal 503 426
Normal 466 426
Normal 703 386
Normal 460 383
Normal 646 410
Normal 520 400
Normal 486 616
Normal 633 376
Normal 570 460
Normal 503 1433
Normal 600 383
Normal 440 443
Normal 650 386
Normal 510 440
Normal 550 376
Normal 1120 460
Normal 543 376
Normal 530 493
Minimum 420 370
Average 499 463
Maximum 1120 1433

GUID wins

3. The where condition field also has a non-clustered index and the condition is not a small amount of data.

Declare @ d datetime
Set @ d = getdate ()
 
SELECT *
FROM BKListid
Join studentid
On BKListid. stuid = studentid. stuid
Where studentid. stuname in (select top 100 stuname from SGPZ... student)

Declare @ result int
Select @ result = datediff (MS, @ d, getdate ())

Declare @ d1 datetime
Set @ d1 = getdate ()
 
SELECT *
FROM [BKListguid]
Join studentguid
On BKListguid. bkguid = studentguid. stuguid
Where studentguid. stuname in (select top 100 stuname from SGPZ... student)
 

Bytes ---------------------------------------------------------------------------------------

Status id (MS) guid (MS)
Normal 2716 1603
Normal 2436 1533
Normal 2133 1530
Normal 2160 1543
Normal 2260 1580
Normal 2220 1610
Normal 3206 1500
Normal 2200 1546
Normal 3183 2873
Normal 2140 1483
Normal 2746 2093
Normal 3590 2326
Normal 3853 2170
Normal 3140 2903
Normal 3456 2656
Normal 2183 2623
Normal 2226 1546
Normal 2270 1860
Normal 2813 1500
Normal 2403 1500
Normal 2326 2093
Normal 2230 1686
Normal 2203 1560
Normal 2670 1596
Normal 2246 1706
Normal 2586 1500
Normal 2136 1516
Normal 2460 1490
Normal 2216 1563
Normal 2340 1516
Normal 2316 1623
Normal 2213 1610
Normal 2760 2330
Normal 2236 2046
Normal 2230 1690
Normal 3373 1610
Normal 2406 2110
Normal 2273 1626
Normal 2353 1580
Normal 2263 2193
Minimum 2133 1483
Average 2330 1720
Maximum 3853 2903

 

Guid wins

 

4. Only the correlated conditions have clustered Indexes

Declare @ d datetime
Set @ d = getdate ()
 
SELECT *
FROM BKListid
Join studentid
On BKListid. stuid = studentid. stuid
Where studentid. areaorganID = 36

Declare @ result int
Select @ result = datediff (MS, @ d, getdate ())

Declare @ d1 datetime
Set @ d1 = getdate ()
 
SELECT *
FROM [BKListguid]
Join studentguid
On BKListguid. bkguid = studentguid. stuguid
Where BKListguid. areaorganID = 36
 
Declare @ result1 int
Select @ result1 = datediff (MS, @ d1, getdate ())
 

 

Bytes ----------------------------------------------------------------------------------------

Status id (MS) guid (MS)
Normal 12433 12983
Normal 12373 12716
Normal 12620 12906
Normal 12330 13153
Normal 12126 14463
Normal 12236 12186
Normal 12433 12546
Normal 12900 13296
Normal 12543 12626
Normal 13116 12750
Normal 12626 12660
Normal 12400 12703
Normal 13160 12876
Normal 12476 14813
Normal 17870 14810
Normal 14910 14686
Normal 65936 13750
Normal 12833 12703
Normal 12320 12780
Normal 12246 12653
Normal 12653 12750
Normal 13123 13313
Normal 13610 12873
Normal 12266 13140
Normal 12776 12753
Normal 12273 13483
Normal 13550 12736
Normal 11966 13000
Normal 12106 12723
Normal 12260 12543
Normal 12506 12860
Normal 14770 12796
Normal 13420 14266
Normal 13693 13560
Normal 13260 14110
Normal 14076 12920
Normal 12903 13203
Normal 14446 13513
Normal 12336 12676
Normal 12486 13533
Minimum 11966 12186
Average 12824 13017
Maximum 65936 14813

GUID failed

 

5. Join condition clustered index. where is a condition non-clustered index with a data volume of 9 W

 

Status id (MS) guid (MS)
Normal 12860 14330
Normal 14176 14643
Normal 18403 14343
Normal 13230 13593
Normal 13070 13030
Normal 12470 13226
Normal 12610 13060
Normal 12993 13610
Normal 13110 13576
Normal 12836 12906
Minimum 12470 12906
Average 13575 13631
Maximum 18403 14643

GUID failed

6. Join condition clustered index, where two non-clustered indexes with 5000 data volume

Status id (MS) guid (MS)
Normal 7553 7593
Normal 7250 7046
Normal 7523 8420
Normal 7563 8080
Normal 7766 8526
Normal 8003 8220
Normal 8600 8313
Normal 8353 7576
Normal 13043 7983
Normal 8036 8890
Minimum 7250 7046
Average 8369 8064
Maximum 13043 8890

 

 

7. Join condition clustered index. where is a condition non-clustered index with a small amount of data

Status id (MS) guid (MS)
Normal 170 110
Normal 153 73
Normal 163 73
Normal 153 76
Normal 163 93
Normal 156 73
Normal 153 70
Normal 143 76
Normal 193 100
Normal 153 70
Normal 220 146
Normal 143 66
Normal 156 96
Normal 156 193
Normal 153 70
Normal 153 66
Normal 156 70
Normal 156 76
Normal 170 73
Normal 156 73
Minimum value 143 66
Average 160 87
Maximum 220 193

8. The correlated condition is non-clustered index. where has one clustered index and the other has no index.

Declare @ d datetime
Set @ d = getdate ()
 
SELECT *
FROM BKListid
Join studentid
On BKListid. stuid = studentid. stuid
Where studentid. areaorganID = 36 and studentid. STUNAME like 'Liu %'

Declare @ result int
Select @ result = datediff (MS, @ d, getdate ())

Declare @ d1 datetime
Set @ d1 = getdate ()
 
SELECT *
FROM [BKListguid]
Join studentguid
On BKListguid. bkguid = studentguid. stuguid
Where studentguid. areaorganID = 36 and studentguid. STUNAME like 'Liu %'

 

Bytes -----------------------------------------------------------------------------------

Status id (MS) guid (MS)
Normal 3566 3126
Normal 3590 3283
Normal 3580 3320
Normal 3270 3000
Normal 3373 3360
Normal 3633 3256
Normal 3263 3266
Normal 3216 3270
Normal 3186 3230
Normal 3846 3420
Normal 3643 3140
Normal 3850 3310
Normal 3866 3843
Normal 3766 3436
Normal 3560 3373
Normal 4023 3790
Normal 4056 3690
Normal 4323 3790
Normal 4186 3750
Normal 4340 3906
Minimum 3186 3000
Average 3706 3427
Maximum 4340 3906