Skip to content

Instantly share code, notes, and snippets.

@EugZol

EugZol/where_exists_benchmark_results.txt

Created September 1, 2015 11:04
Show Gist options
  • Save EugZol/454941a492e5de06d52b to your computer and use it in GitHub Desktop.
Save EugZol/454941a492e5de06d52b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
where_exists_benchmark_results.txt
Using postgresql
-- create_table(:companies, {:force=>true})
-> 0.0075s
-- create_table(:developers, {:force=>true})
-> 0.0077s
-- create_table(:groups, {:force=>true})
-> 0.0066s
-- create_table(:contract, {:force=>true})
-> 0.0157s
Calculating -------------------------------------
Exists on Through: Multiple queries, storing IDs in Ruby Array
2.000 i/100ms
Exists on Through: Using joins
25.000 i/100ms
Exists on Through: Using where_exists
31.000 i/100ms
Not Exists on Through: Multiple queries, storing IDs in Ruby Array
1.000 i/100ms
Not Exists on Through: Using joins
26.000 i/100ms
Not Exists on Through: Using where_not_exists
21.000 i/100ms
-------------------------------------------------
Exists on Through: Multiple queries, storing IDs in Ruby Array
21.657 (± 4.6%) i/s - 110.000
Exists on Through: Using joins
265.655 (± 2.6%) i/s - 1.350k
Exists on Through: Using where_exists
323.482 (± 4.0%) i/s - 1.643k
Not Exists on Through: Multiple queries, storing IDs in Ruby Array
17.386 (± 5.8%) i/s - 87.000
Not Exists on Through: Using joins
266.951 (± 3.4%) i/s - 1.352k
Not Exists on Through: Using where_not_exists
266.856 (± 3.7%) i/s - 1.344k
Comparison:
Exists on Through: Using where_exists: 323.5 i/s
Not Exists on Through: Using joins: 267.0 i/s - 1.21x slower
Not Exists on Through: Using where_not_exists: 266.9 i/s - 1.21x slower
Exists on Through: Using joins: 265.7 i/s - 1.22x slower
Exists on Through: Multiple queries, storing IDs in Ruby Array: 21.7 i/s - 14.94x slower
Not Exists on Through: Multiple queries, storing IDs in Ruby Array: 17.4 i/s - 18.61x slower
Calculating -------------------------------------
Exists: Multiple queries, storing IDs in Ruby Array
6.000 i/100ms
Exists: Using joins 35.000 i/100ms
Exists: Using where_exists
39.000 i/100ms
Not Exists: Multiple queries, storing IDs in Ruby Array
4.000 i/100ms
Not Exists: Using joins
37.000 i/100ms
Not Exists: Using where_not_exists
39.000 i/100ms
-------------------------------------------------
Exists: Multiple queries, storing IDs in Ruby Array
64.269 (± 4.7%) i/s - 324.000
Exists: Using joins 347.030 (± 4.6%) i/s - 1.750k
Exists: Using where_exists
385.573 (± 5.4%) i/s - 1.950k
Not Exists: Multiple queries, storing IDs in Ruby Array
42.382 (± 4.7%) i/s - 212.000
Not Exists: Using joins
363.134 (± 4.4%) i/s - 1.813k
Not Exists: Using where_not_exists
378.870 (± 5.5%) i/s - 1.911k
Comparison:
Exists: Using where_exists: 385.6 i/s
Not Exists: Using where_not_exists: 378.9 i/s - 1.02x slower
Not Exists: Using joins: 363.1 i/s - 1.06x slower
Exists: Using joins: 347.0 i/s - 1.11x slower
Exists: Multiple queries, storing IDs in Ruby Array: 64.3 i/s - 6.00x slower
Not Exists: Multiple queries, storing IDs in Ruby Array: 42.4 i/s - 9.10x slower
Using mysql
-- create_table(:companies, {:force=>true})
-> 0.0418s
-- create_table(:developers, {:force=>true})
-> 0.0150s
-- create_table(:groups, {:force=>true})
-> 0.0370s
-- create_table(:contract, {:force=>true})
-> 0.0183s
Calculating -------------------------------------
Exists on Through: Multiple queries, storing IDs in Ruby Array
2.000 i/100ms
Exists on Through: Using joins
19.000 i/100ms
Exists on Through: Using where_exists
1.000 i/100ms
Not Exists on Through: Multiple queries, storing IDs in Ruby Array
2.000 i/100ms
Not Exists on Through: Using joins
1.000 i/100ms
Not Exists on Through: Using where_not_exists
1.000 i/100ms
-------------------------------------------------
Exists on Through: Multiple queries, storing IDs in Ruby Array
25.058 (± 8.0%) i/s - 126.000
Exists on Through: Using joins
193.589 (±15.0%) i/s - 950.000
Exists on Through: Using where_exists
2.181 (± 0.0%) i/s - 11.000
Not Exists on Through: Multiple queries, storing IDs in Ruby Array
21.050 (± 4.8%) i/s - 106.000
Not Exists on Through: Using joins
5.516 (± 0.0%) i/s - 28.000
Not Exists on Through: Using where_not_exists
2.078 (± 0.0%) i/s - 11.000 in 5.322219s
Comparison:
Exists on Through: Using joins: 193.6 i/s
Exists on Through: Multiple queries, storing IDs in Ruby Array: 25.1 i/s - 7.73x slower
Not Exists on Through: Multiple queries, storing IDs in Ruby Array: 21.1 i/s - 9.20x slower
Not Exists on Through: Using joins: 5.5 i/s - 35.09x slower
Exists on Through: Using where_exists: 2.2 i/s - 88.76x slower
Not Exists on Through: Using where_not_exists: 2.1 i/s - 93.14x slower
Calculating -------------------------------------
Exists: Multiple queries, storing IDs in Ruby Array
5.000 i/100ms
Exists: Using joins 17.000 i/100ms
Exists: Using where_exists
16.000 i/100ms
Not Exists: Multiple queries, storing IDs in Ruby Array
4.000 i/100ms
Not Exists: Using joins
18.000 i/100ms
Not Exists: Using where_not_exists
15.000 i/100ms
-------------------------------------------------
Exists: Multiple queries, storing IDs in Ruby Array
53.823 (± 9.3%) i/s - 270.000
Exists: Using joins 106.888 (±28.1%) i/s - 493.000
Exists: Using where_exists
143.606 (±10.4%) i/s - 720.000
Not Exists: Multiple queries, storing IDs in Ruby Array
45.790 (±10.9%) i/s - 228.000
Not Exists: Using joins
173.433 (±11.5%) i/s - 864.000
Not Exists: Using where_not_exists
143.245 (±18.8%) i/s - 690.000
Comparison:
Not Exists: Using joins: 173.4 i/s
Exists: Using where_exists: 143.6 i/s - 1.21x slower
Not Exists: Using where_not_exists: 143.2 i/s - 1.21x slower
Exists: Using joins: 106.9 i/s - 1.62x slower
Exists: Multiple queries, storing IDs in Ruby Array: 53.8 i/s - 3.22x slower
Not Exists: Multiple queries, storing IDs in Ruby Array: 45.8 i/s - 3.79x slower
Using mysql2
-- create_table(:companies, {:force=>true})
-> 0.0406s
-- create_table(:developers, {:force=>true})
-> 0.0168s
-- create_table(:groups, {:force=>true})
-> 0.0145s
-- create_table(:contract, {:force=>true})
-> 0.0199s
Calculating -------------------------------------
Exists on Through: Multiple queries, storing IDs in Ruby Array
1.000 i/100ms
Exists on Through: Using joins
12.000 i/100ms
Exists on Through: Using where_exists
1.000 i/100ms
Not Exists on Through: Multiple queries, storing IDs in Ruby Array
2.000 i/100ms
Not Exists on Through: Using joins
1.000 i/100ms
Not Exists on Through: Using where_not_exists
1.000 i/100ms
-------------------------------------------------
Exists on Through: Multiple queries, storing IDs in Ruby Array
18.277 (±16.4%) i/s - 89.000
Exists on Through: Using joins
166.086 (± 8.4%) i/s - 828.000
Exists on Through: Using where_exists
2.066 (± 0.0%) i/s - 11.000 in 5.386193s
Not Exists on Through: Multiple queries, storing IDs in Ruby Array
19.622 (± 5.1%) i/s - 98.000
Not Exists on Through: Using joins
5.377 (±18.6%) i/s - 27.000
Not Exists on Through: Using where_not_exists
1.969 (± 0.0%) i/s - 10.000
Comparison:
Exists on Through: Using joins: 166.1 i/s
Not Exists on Through: Multiple queries, storing IDs in Ruby Array: 19.6 i/s - 8.46x slower
Exists on Through: Multiple queries, storing IDs in Ruby Array: 18.3 i/s - 9.09x slower
Not Exists on Through: Using joins: 5.4 i/s - 30.89x slower
Exists on Through: Using where_exists: 2.1 i/s - 80.40x slower
Not Exists on Through: Using where_not_exists: 2.0 i/s - 84.37x slower
Calculating -------------------------------------
Exists: Multiple queries, storing IDs in Ruby Array
5.000 i/100ms
Exists: Using joins 13.000 i/100ms
Exists: Using where_exists
8.000 i/100ms
Not Exists: Multiple queries, storing IDs in Ruby Array
2.000 i/100ms
Not Exists: Using joins
13.000 i/100ms
Not Exists: Using where_not_exists
14.000 i/100ms
-------------------------------------------------
Exists: Multiple queries, storing IDs in Ruby Array
52.843 (± 7.6%) i/s - 265.000
Exists: Using joins 172.114 (± 8.1%) i/s - 858.000
Exists: Using where_exists
152.122 (±11.8%) i/s - 752.000
Not Exists: Multiple queries, storing IDs in Ruby Array
36.653 (±24.6%) i/s - 170.000
Not Exists: Using joins
138.150 (±15.2%) i/s - 676.000
Not Exists: Using where_not_exists
144.151 (±18.0%) i/s - 700.000
Comparison:
Exists: Using joins: 172.1 i/s
Exists: Using where_exists: 152.1 i/s - 1.13x slower
Not Exists: Using where_not_exists: 144.2 i/s - 1.19x slower
Not Exists: Using joins: 138.2 i/s - 1.25x slower
Exists: Multiple queries, storing IDs in Ruby Array: 52.8 i/s - 3.26x slower
Not Exists: Multiple queries, storing IDs in Ruby Array: 36.7 i/s - 4.70x slower
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment