Skip to content

Commit 4835196

Browse files
vbilopavvbilopav
committed
perfomance tests
1 parent 1335899 commit 4835196

39 files changed

Lines changed: 4112 additions & 2205 deletions

‎PerfomanceTests/perf_tests.http‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -189,7 +189,9 @@ content-type: application/json
189189

190190
###
191191

192-
POST http://127.0.0.1:8000/perf_test_record/
192+
// ef endpoint test
193+
194+
POST http://127.0.0.1:5247/api/perf_test/
193195
content-type: application/json
194196

195197
{
@@ -202,7 +204,7 @@ content-type: application/json
202204

203205
###
204206

205-
POST http://127.0.0.1:8000/perf_test_record_arrays/
207+
POST http://127.0.0.1:5247/api/perf_test_arrays
206208
content-type: application/json
207209

208210
{

‎PerfomanceTests/readme.md‎

Lines changed: 180 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -20,30 +20,191 @@ Used API's are from:
2020

2121
## Results
2222

23-
Number of successful requests in 50 seconds (higher is better).
23+
Number of successful requests in 50 seconds **(higher is better)**.
2424

25-
| Records | Function | NpgsqlRest AOT (1) | NpgsqlRest JIT (2) | PostgREST | Ratio (AOT / PostgREST) | Ratio (JIT / PostgREST) |
25+
| Records | Function | AOT [1]() | JIT [2](2) | PostgREST | Ratio (AOT / PostgREST) | Ratio (JIT / PostgREST) |
2626
| ------: | ---------: | ---------: | --------: | --------: | --------: | --------: |
27-
| 10 | `perf_test` | 370,415 | 423,408 | 68,021 | 5.45 | 6.22 |
28-
| 100 | `perf_test` | 352,021 | 400,924 | 59,749 | 5.89 | 6.71 |
29-
| 10 | `perf_test_arrays` | 276,115 | 310,398 | 51,704 | 5.34 | 6.00 |
30-
| 100 | `perf_test_arrays` | 275,891 | 289,838 | 49,760 | 5.54 | 5.82 |
31-
| 10 | `perf_test_record` | 477,001 | 522,165 | 62,392 | 7.65 | 8.37 |
32-
| 100 | `perf_test_record` | 480,127 | 493,580 | 64,619 | 7.43 | 7.64 |
33-
| 10 | `perf_test_record_arrays` | 349,336 | 379,135 | 55,602 | 6.28 | 6.82 |
34-
| 100 | `perf_test_record_arrays` | 356,748 | 362,237 | 51,401 | 6.94 | 7.05 |
35-
36-
1) NpgsqlRest AOT is ahead-of-time (AOT) compiled to native code. AOT has an average **startup time of between 180 to 200 milliseconds.**
37-
2) NpgsqlRest JIT is a Just-In-Time (JIT) compilation of Common Intermediate Language (CIL). JIT has an average **startup time of between 360 to 400 milliseconds.**
27+
| 10 | `perf_test` | 423,515 | 606,410 | 72,305 | 5.86 | 8.39 |
28+
| 100 | `perf_test` | 100,542 | 126,154 | 40,456 | 2.49 | 3.12 |
29+
| 10 | `perf_test_arrays` | 292,489 | 419707 | 55,331 | 5.29 | 7.59 |
30+
| 100 | `perf_test_arrays` | 68,316 | 80,906 | 32,418 | 2.10 | 2.50 |
31+
| 10 | `perf_test_record` | 482,936 | 657,250 | 61,825 | 7.81 | 10.63 |
32+
| 100 | `perf_test_record` | 203,461 | 223,474 | 36,642 | 5.55 | 6.10 |
33+
| 10 | `perf_test_record_arrays` | 380,624 | 474,948 | 50,579 | 7.53 | 9.39 |
34+
| 100 | `perf_test_record_arrays` | 112,542 | 116,399 | 32,619 | 3.45 | 3.57 |
3835

3936
### Other Platforms
4037

41-
| Records | Function | NpgsqlRest AOT | NpgsqlRest JIT | Django REST Framework 4.2.10 on Python 3.8 |
42-
| ------: | ---------: | ---------: | --------: | --------: |
43-
| 10 | `perf_test` | 370,415 | 423,408 | 11,476 |
44-
| 100 | `perf_test` | 352,021 | 400,924 | 11,695 |
45-
| 10 | `perf_test_arrays` | 276,115| 310,398 | 11,784 |
46-
| 100 | `perf_test_arrays` | 275,891| 289,838 | 9,575 |
38+
| Records | Function | AOT (1) | JIT (2) | EF | ADO | Django |
39+
| ------: | ---------: | ---------: | --------: | --------: | --------: |
40+
| 10 | `perf_test` | 423,515 | 606,410 | 337,612 | 440,896 | 21,193 |
41+
| 100 | `perf_test` | 100,542 | 126,154 | 235,331 | 314,198 | 18,345 |
42+
| 10 | `perf_test_arrays` | 292,489 | 419,707 | 254,787 | 309,059 | 19,011 |
43+
| 100 | `perf_test_arrays` | 68,316 | 80,906 | 113,663 | 130,471 | 11,452 |
44+
45+
#### 1) AOT
46+
47+
NpgsqlRest .NET8 AOT build is ahead-of-time (AOT) compiled to native code. AOT has an average **startup time of between 180 to 200 milliseconds.**
48+
49+
#### 2) JIT
50+
51+
NpgsqlRest JIT build is a Just-In-Time (JIT) compilation of Common Intermediate Language (CIL). JIT has an average **startup time of between 360 to 400 milliseconds.**
52+
53+
#### 3) EF
54+
55+
.NET8 Npgsql.EntityFrameworkCore.PostgreSQL 8.0.2
56+
57+
```csharp
58+
var builder = WebApplication.CreateBuilder(args);
59+
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
60+
61+
builder.Services.AddDbContext<DbContext>(options =>
62+
options.UseNpgsql(connectionString));
63+
64+
var app = builder.Build();
65+
66+
67+
app.MapPost("/api/perf_test", (DbContext dbContext, [FromBody]Params p) => dbContext.Database.SqlQuery<Table>(
68+
$"select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from perf_test(_records => {p._records}, _text_param => {p._text_param}, _int_param => {p._int_param}, _ts_param => {p._ts_param}, _bool_param => {p._bool_param})"));
69+
70+
app.MapPost("/api/perf_test_arrays", (DbContext dbContext, [FromBody]Params p) => dbContext.Database.SqlQuery<TableWithArrays>(
71+
$"select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from perf_test_arrays(_records => {p._records}, _text_param => {p._text_param}, _int_param => {p._int_param}, _ts_param => {p._ts_param}, _bool_param => {p._bool_param})"));
72+
73+
app.Run();
74+
```
75+
76+
#### 4) ADO
77+
78+
.NET8 Raw Data ADO Reader:
79+
80+
```csharp
81+
var builder = WebApplication.CreateBuilder(args);
82+
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
83+
84+
var app = builder.Build();
85+
86+
87+
app.MapPost("/api/perf_test", (DbContext dbContext, [FromBody]Params p) => Data.GetTableData(p));
88+
app.MapPost("/api/perf_test_arrays", (DbContext dbContext, [FromBody]Params p) => Data.GetTableArrayData(p));
89+
90+
app.Run();
91+
92+
static class Data
93+
{
94+
public static async IAsyncEnumerable<Table> GetTableData(Params p)
95+
{
96+
using var connection = new NpgsqlConnection(connectionString);
97+
await connection.OpenAsync();
98+
99+
using var command = connection.CreateCommand();
100+
command.CommandText = $"select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from perf_test(@_records, @_text_param, @_int_param, @_ts_param, @_bool_param)";
101+
102+
command.Parameters.AddWithValue("_records", p._records);
103+
command.Parameters.AddWithValue("_text_param", p._text_param);
104+
command.Parameters.AddWithValue("_int_param", p._int_param);
105+
command.Parameters.AddWithValue("_ts_param", p._ts_param);
106+
command.Parameters.AddWithValue("_bool_param", p._bool_param);
107+
108+
using var reader = await command.ExecuteReaderAsync();
109+
110+
while (await reader.ReadAsync())
111+
{
112+
yield return new Table
113+
{
114+
id1 = reader.GetInt32(0),
115+
foo1 = reader.IsDBNull(1) ? null : reader.GetString(1),
116+
bar1 = reader.IsDBNull(2) ? null : reader.GetString(2),
117+
datetime1 = reader.GetDateTime(3),
118+
id2 = reader.GetInt32(4),
119+
foo2 = reader.IsDBNull(5) ? null : reader.GetString(5),
120+
bar2 = reader.IsDBNull(6) ? null : reader.GetString(6),
121+
datetime2 = reader.GetDateTime(7),
122+
long_foo_bar = reader.IsDBNull(8) ? null : reader.GetString(8),
123+
is_foobar = reader.GetBoolean(9)
124+
};
125+
}
126+
}
127+
128+
public static async IAsyncEnumerable<TableWithArrays> GetTableArrayData(Params p)
129+
{
130+
using var connection = new NpgsqlConnection(connectionString);
131+
await connection.OpenAsync();
132+
133+
using var command = connection.CreateCommand();
134+
command.CommandText = $"select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from perf_test_arrays(@_records, @_text_param, @_int_param, @_ts_param, @_bool_param)";
135+
136+
command.Parameters.AddWithValue("_records", p._records);
137+
command.Parameters.AddWithValue("_text_param", p._text_param);
138+
command.Parameters.AddWithValue("_int_param", p._int_param);
139+
command.Parameters.AddWithValue("_ts_param", p._ts_param);
140+
command.Parameters.AddWithValue("_bool_param", p._bool_param);
141+
142+
using var reader = await command.ExecuteReaderAsync();
143+
var result = new List<TableWithArrays>();
144+
145+
while (await reader.ReadAsync())
146+
{
147+
yield return new TableWithArrays
148+
{
149+
id1 = reader.IsDBNull(0) ? null : (int[])reader.GetValue(0),
150+
foo1 = reader.IsDBNull(1) ? null : (string[])reader.GetValue(1),
151+
bar1 = reader.IsDBNull(2) ? null : (string[])reader.GetValue(2),
152+
datetime1 = reader.IsDBNull(3) ? null : (DateTime[])reader.GetValue(3),
153+
id2 = reader.IsDBNull(4) ? null : (int[])reader.GetValue(4),
154+
foo2 = reader.IsDBNull(5) ? null : (string[])reader.GetValue(5),
155+
bar2 = reader.IsDBNull(6) ? null : (string[])reader.GetValue(6),
156+
datetime2 = reader.IsDBNull(7) ? null : (DateTime[])reader.GetValue(7),
157+
long_foo_bar = reader.IsDBNull(8) ? null : (string[])reader.GetValue(8),
158+
is_foobar = reader.IsDBNull(9) ? null : (bool[])reader.GetValue(9)
159+
};
160+
}
161+
}
162+
}
163+
```
164+
165+
#### 5) Django
166+
167+
Django REST Framework 4.2.10 on Python 3.8
168+
169+
```py
170+
from rest_framework.views import APIView
171+
from rest_framework.response import Response
172+
from django.db import connection
173+
174+
class PerfTestView(APIView):
175+
def post(self, request):
176+
records = request.data.get('_records', 10)
177+
text_param = request.data.get('_text_param', 'abcxyz')
178+
int_param = request.data.get('_int_param', 999)
179+
ts_param = request.data.get('_ts_param', '2024-01-01')
180+
bool_param = request.data.get('_bool_param', True)
181+
182+
with connection.cursor() as cursor:
183+
cursor.execute(
184+
"select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from perf_test(_records => %s, _text_param => %s, _int_param => %s, _ts_param => %s, _bool_param => %s)",
185+
[records, text_param, int_param, ts_param, bool_param])
186+
data = cursor.fetchall()
187+
188+
columns = [col[0] for col in cursor.description]
189+
return Response([dict(zip(columns, row)) for row in data])
190+
191+
class PerfTestArrays(APIView):
192+
def post(self, request):
193+
records = request.data.get('_records', 10)
194+
text_param = request.data.get('_text_param', 'abcxyz')
195+
int_param = request.data.get('_int_param', 999)
196+
ts_param = request.data.get('_ts_param', '2024-01-01')
197+
bool_param = request.data.get('_bool_param', True)
198+
199+
with connection.cursor() as cursor:
200+
cursor.execute(
201+
"select id1, foo1, bar1, datetime1, id2, foo2, bar2, datetime2, long_foo_bar, is_foobar from perf_test_arrays(_records => %s, _text_param => %s, _int_param => %s, _ts_param => %s, _bool_param => %s)",
202+
[records, text_param, int_param, ts_param, bool_param])
203+
data = cursor.fetchall()
204+
205+
columns = [col[0] for col in cursor.description]
206+
return Response([dict(zip(columns, row)) for row in data])
207+
```
47208

48209
## Tests Functions
49210

0 commit comments

Comments
 (0)