forked from npgsql/npgsql
-
Notifications
You must be signed in to change notification settings - Fork 3
Expand file tree
/
Copy pathPrepare.cs
More file actions
120 lines (105 loc) · 3.54 KB
/
Prepare.cs
File metadata and controls
120 lines (105 loc) · 3.54 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
using System.Linq;
using System.Reflection;
using System.Text;
using BenchmarkDotNet.Attributes;
// ReSharper disable UnusedMember.Global
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable AssignNullToNotNullAttribute.Global
namespace Npgsql.Benchmarks;
public class Prepare
{
NpgsqlConnection _conn = default!, _autoPreparingConn = default!;
static readonly string[] Queries;
string _query = default!;
NpgsqlCommand _preparedCmd = default!;
/// <summary>
/// The more tables are joined, the more complex the query is to plan, and therefore the more
/// impact statement preparation should have.
/// </summary>
[Params(0, 1, 2, 5, 10)]
public int TablesToJoin { get; set; }
[GlobalSetup]
public void GlobalSetup()
{
_conn = BenchmarkEnvironment.OpenConnection();
_autoPreparingConn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(BenchmarkEnvironment.ConnectionString)
{
MaxAutoPrepare = 10
}.ToString());
_autoPreparingConn.Open();
foreach (var conn in new[] { _conn, _autoPreparingConn })
{
using (var cmd = new NpgsqlCommand { Connection = conn })
{
for (var i = 0; i < 100; i++)
{
cmd.CommandText = $@"
CREATE TEMP TABLE table{i} (id INT PRIMARY KEY, data INT);
INSERT INTO table{i} (id, data) VALUES (1, {i});
";
cmd.ExecuteNonQuery();
}
}
}
_query = Queries[TablesToJoin];
_preparedCmd = new NpgsqlCommand(_query, _conn);
_preparedCmd.Prepare();
}
[GlobalCleanup]
public void GlobalCleanup()
=> _conn.Dispose();
public Prepare()
{
// Create tables and data
using (var conn = BenchmarkEnvironment.OpenConnection())
using (var cmd = new NpgsqlCommand {Connection = conn})
{
for (var i = 0; i < TablesToJoinValues.Max(); i++)
{
cmd.CommandText = $@"
DROP TABLE IF EXISTS table{i};
CREATE TABLE table{i} (id INT PRIMARY KEY, data INT);
INSERT INTO table{i} (id, data) VALUES (1, {i});
";
cmd.ExecuteNonQuery();
}
}
}
[Benchmark(Baseline = true)]
public object Unprepared()
{
using (var cmd = new NpgsqlCommand(_query, _conn))
return cmd.ExecuteScalar()!;
}
[Benchmark]
public object AutoPrepared()
{
using (var cmd = new NpgsqlCommand(_query, _autoPreparingConn))
return cmd.ExecuteScalar()!;
}
[Benchmark]
public object Prepared() => _preparedCmd.ExecuteScalar()!;
static Prepare()
{
Queries = new string[TablesToJoinValues.Max() + 1];
Queries[0] = "SELECT 1";
foreach (var tablesToJoin in TablesToJoinValues.Where(i => i != 0))
Queries[tablesToJoin] = GenerateQuery(tablesToJoin);
}
static string GenerateQuery(int tablesToJoin)
{
var sb = new StringBuilder();
sb.AppendLine("SELECT ");
sb.AppendLine(string.Join("+", Enumerable.Range(0, tablesToJoin).Select(i => $"table{i}.data")));
sb.AppendLine("FROM table0");
for (var i = 1; i < tablesToJoin; i++)
sb.AppendLine($"JOIN table{i} ON table{i}.id = table{i - 1}.id");
return sb.ToString();
}
static readonly int[] TablesToJoinValues = typeof(Prepare)
.GetProperty(nameof(TablesToJoin))!
.GetCustomAttribute<ParamsAttribute>()!
.Values
.Cast<int>()
.ToArray();
}