Want more features on Pastebin? Sign Up, it's FREE!
Guest

T-SQL inequality performance test

By: a guest on Mar 18th, 2013  |  syntax: T-SQL  |  size: 1.41 KB  |  views: 39  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1.  
  2.  
  3. DECLARE @StartTime DATETIME
  4. SET @StartTime = GETDATE()
  5. DECLARE @i INT
  6. SET @i = 0
  7. WHILE (@i < 1000000)
  8. BEGIN
  9.         DECLARE @A SQL_VARIANT
  10.         DECLARE @B SQL_VARIANT
  11.    
  12.         IF RAND() < 0.1
  13.                 SET @A = NULL
  14.         ELSE
  15.                 SET @A = RAND()
  16.         IF RAND() > 0.9        
  17.                 IF RAND() < 0.1
  18.                                 SET @B = NULL
  19.                         ELSE
  20.                                 SET @B = RAND()
  21.         ELSE
  22.                 SET @B = @A
  23.  
  24.         -- Test 1
  25.          IF (@A <> @B) OR (@A IS NULL AND @B IS NOT NULL) OR (@A IS NOT NULL AND @B IS NULL)  SET @i = @i + 1 ELSE SET @i = @i + 1
  26.         -- End test 1
  27.        
  28.         -- Test 2
  29.         -- IF EXISTS (SELECT @A EXCEPT SELECT @B) SET @i = @i + 1 ELSE SET @i = @i + 1 
  30.         -- End test 2
  31.        
  32.         -- Test 3
  33. --      IF (@A = @B) GOTO Equal ELSE IF @A IS NULL AND @B IS NULL GOTO Equal
  34. --NotEqual:
  35. --Equal:
  36. --      SET @i = @i + 1
  37.         -- End Test3
  38.        
  39.         -- Test 4
  40.         --IF (ISNULL(@A, 0) <> ISNULL(@B, 0)) SET @i = @i + 1 ELSE SET @i = @i + 1
  41.         -- End Test 4
  42. END
  43. PRINT DATEDIFF(ms, @StartTime, GETDATE())
  44.  
  45.  
  46. -- IF (@A <> @B) OR (@A IS NULL AND @B IS NOT NULL) OR (@A IS NOT NULL AND @B IS NULL)
  47. -- 4133
  48. -- 3650
  49. -- 3976
  50. -- 3866
  51. -- 3616
  52. -- AVG = 3848
  53.  
  54. -- IF EXISTS (SELECT @A EXCEPT SELECT @B)
  55. -- 7690
  56. -- 7486
  57. -- 7876
  58. -- 7676
  59. -- 8253
  60. -- AVG = 7796
  61.  
  62. -- IF (@A = @B) GOTO Equal ELSE IF @A IS NULL AND @B IS NULL GOTO Equal
  63. -- 3933
  64. -- 4586
  65. -- 4326
  66. -- 4086
  67. -- 3713
  68. -- AVG = 4140
  69.  
  70. -- IF (ISNULL(@A, 0) <> ISNULL(@B, 0)) SET @i = @i + 1 ELSE SET @i = @i + 1
  71. -- 3636
  72. -- 3913
  73. -- 3763
  74. -- 3973
  75. -- 4426
  76. -- AVG = 3942
clone this paste RAW Paste Data