Advertisement
Guest User

T-SQL inequality performance test

a guest
Mar 18th, 2013
234
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.41 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement