|
1 |
/*
|
|
2 |
* Copyright (C) 2011 Emweb bvba, Kessel-Lo, Belgium.
|
|
3 |
*
|
|
4 |
* See the LICENSE file for terms of use.
|
|
5 |
*/
|
|
6 |
|
|
7 |
#include <boost/test/unit_test.hpp>
|
|
8 |
|
|
9 |
#include <Wt/Dbo/Dbo>
|
|
10 |
#include <Wt/WDateTime>
|
|
11 |
#include <Wt/Dbo/WtSqlTraits>
|
|
12 |
#include <Wt/Dbo/QueryModel>
|
|
13 |
|
|
14 |
#include "DboFixture.h"
|
|
15 |
|
|
16 |
namespace dbo = Wt::Dbo;
|
|
17 |
|
|
18 |
namespace {
|
|
19 |
|
|
20 |
const unsigned total_added_objects = 20000;
|
|
21 |
const unsigned start_total_objects = 500;
|
|
22 |
const unsigned benchmark_time_limit = 10; //seconds (for a benchmark round)
|
|
23 |
|
|
24 |
}
|
|
25 |
|
|
26 |
namespace Perf2 {
|
|
27 |
|
|
28 |
class Post {
|
|
29 |
public:
|
|
30 |
int counter;
|
|
31 |
|
|
32 |
template<class Action>
|
|
33 |
void persist(Action& a)
|
|
34 |
{
|
|
35 |
dbo::field(a, counter, "counter");
|
|
36 |
}
|
|
37 |
};
|
|
38 |
|
|
39 |
}
|
|
40 |
|
|
41 |
struct DboBenchmark2Fixture : DboFixtureBase
|
|
42 |
{
|
|
43 |
DboBenchmark2Fixture() :
|
|
44 |
DboFixtureBase(false)
|
|
45 |
{
|
|
46 |
session_->mapClass<Perf2::Post>("post");
|
|
47 |
|
|
48 |
try {
|
|
49 |
session_->dropTables();
|
|
50 |
} catch (...) {
|
|
51 |
}
|
|
52 |
|
|
53 |
session_->createTables();
|
|
54 |
}
|
|
55 |
};
|
|
56 |
|
|
57 |
|
|
58 |
long benchmarkQuery(
|
|
59 |
dbo::Session& session,
|
|
60 |
const std::string& query_sql,
|
|
61 |
int total_objects,
|
|
62 |
int batch_size,
|
|
63 |
long expected,
|
|
64 |
bool force_select_count)
|
|
65 |
{
|
|
66 |
dbo::Transaction t(session);
|
|
67 |
|
|
68 |
/*
|
|
69 |
* clear Dbo Session cache
|
|
70 |
*/
|
|
71 |
session.rereadAll();
|
|
72 |
|
|
73 |
boost::posix_time::ptime start
|
|
74 |
= boost::posix_time::microsec_clock::local_time();
|
|
75 |
|
|
76 |
typedef boost::tuple<dbo::ptr<Perf2::Post>, double> PostWithSum;
|
|
77 |
|
|
78 |
dbo::Query<PostWithSum> query = session.query<PostWithSum>(query_sql);
|
|
79 |
|
|
80 |
dbo::QueryModel< PostWithSum > model;
|
|
81 |
|
|
82 |
model.setQuery(query);
|
|
83 |
model.addAllFieldsAsColumns();
|
|
84 |
|
|
85 |
std::cerr << "QueryModel " << total_objects
|
|
86 |
<< " rows in batches of " << batch_size
|
|
87 |
<< (force_select_count ? " (force count): " : " : ");
|
|
88 |
|
|
89 |
long total_sum = 0L;
|
|
90 |
|
|
91 |
BOOST_REQUIRE(model.columnCount() == 4); // id, version, counter, sum_total
|
|
92 |
|
|
93 |
/*
|
|
94 |
* FOR TESTING ONLY: Force select count by setting batch size to zero
|
|
95 |
*/
|
|
96 |
if (force_select_count)
|
|
97 |
model.setBatchSize(0);
|
|
98 |
else
|
|
99 |
model.setBatchSize(batch_size);
|
|
100 |
|
|
101 |
BOOST_REQUIRE(model.rowCount() == total_objects);
|
|
102 |
|
|
103 |
if (force_select_count)
|
|
104 |
model.setBatchSize(batch_size);
|
|
105 |
|
|
106 |
long sum;
|
|
107 |
dbo::ptr<Perf2::Post> post;
|
|
108 |
for (unsigned i = 0; i < total_objects; i++) {
|
|
109 |
boost::tie(post, sum) = model.resultRow(i);
|
|
110 |
total_sum += sum;
|
|
111 |
}
|
|
112 |
|
|
113 |
boost::posix_time::ptime
|
|
114 |
end = boost::posix_time::microsec_clock::local_time();
|
|
115 |
|
|
116 |
boost::posix_time::time_duration d = end - start;
|
|
117 |
|
|
118 |
std::cerr << (double)d.total_microseconds() / total_objects
|
|
119 |
<< " microseconds per object" << std::endl;
|
|
120 |
|
|
121 |
BOOST_REQUIRE(total_sum == expected);
|
|
122 |
|
|
123 |
return d.total_seconds();
|
|
124 |
}
|
|
125 |
|
|
126 |
BOOST_AUTO_TEST_CASE( performance_test2 )
|
|
127 |
{
|
|
128 |
DboBenchmark2Fixture f;
|
|
129 |
|
|
130 |
dbo::Session &session = *(f.session_);
|
|
131 |
|
|
132 |
/*
|
|
133 |
* This query is expensive to count() because of the WHERE clause,
|
|
134 |
* at least with Postgres and Sqlite3. SELECT statements that use
|
|
135 |
* WITH clauses or WINDOW functions can also be expensive to count.
|
|
136 |
*/
|
|
137 |
std::string query_post_with_sum =
|
|
138 |
"SELECT \"p1\", (SELECT sum(\"p2\".\"counter\") * (avg(\"p2\".\"counter\")) \"sum_total\" "
|
|
139 |
"FROM \"post\" \"p2\" WHERE \"p2\".\"id\" <= \"p1\".\"id\") "
|
|
140 |
"FROM \"post\" \"p1\" "
|
|
141 |
"WHERE (SELECT sum(\"p2\".\"counter\") * (avg(\"p2\".\"counter\")) \"sum_total\" "
|
|
142 |
"FROM \"post\" \"p2\" WHERE \"p2\".\"id\" <= \"p1\".\"id\") > 0";
|
|
143 |
|
|
144 |
long time_required = 0L;
|
|
145 |
long expected;
|
|
146 |
int current_objects = 0;
|
|
147 |
for (int i = start_total_objects;
|
|
148 |
(i <= total_added_objects) && ((time_required * 6) < benchmark_time_limit); i *= 2) {
|
|
149 |
expected = i * (i + 1) / 2;
|
|
150 |
int total_objects = i;
|
|
151 |
|
|
152 |
dbo::Transaction t(session);
|
|
153 |
|
|
154 |
std::cerr << "Loading " << total_objects << " objects in database."
|
|
155 |
<< std::endl;
|
|
156 |
|
|
157 |
for (unsigned i = 0; i < total_objects - current_objects; ++i) {
|
|
158 |
Perf2::Post *p = new Perf2::Post();
|
|
159 |
p->counter = 1;
|
|
160 |
session.add(p);
|
|
161 |
}
|
|
162 |
|
|
163 |
t.commit();
|
|
164 |
current_objects = total_objects;
|
|
165 |
|
|
166 |
/*
|
|
167 |
* Three cases:
|
|
168 |
*
|
|
169 |
* 1. Batch size is greater than total rows in table. One SELECT.
|
|
170 |
* 2. Batch size is equal to number of rows in table. All rows are fetched in
|
|
171 |
* single SELECT, but a second SELECT for count is still required because in
|
|
172 |
* general case, Dbo is not sure whether or not more rows exist.
|
|
173 |
* 3. Batch size is less than number of rows. A SELECT for count is required.
|
|
174 |
*/
|
|
175 |
time_required = benchmarkQuery(
|
|
176 |
session, query_post_with_sum, total_objects, total_objects + 1, expected, 0);
|
|
177 |
time_required = benchmarkQuery(
|
|
178 |
session, query_post_with_sum, total_objects, total_objects + 0, expected, 0);
|
|
179 |
time_required = benchmarkQuery(
|
|
180 |
session, query_post_with_sum, total_objects, total_objects - 1, expected, 0);
|
|
181 |
|
|
182 |
std::cerr << std::endl;
|
|
183 |
|
|
184 |
/*
|
|
185 |
* Repeat above tests, but force a separate select for counting.
|
|
186 |
*/
|
|
187 |
time_required = benchmarkQuery(
|
|
188 |
session, query_post_with_sum, total_objects, total_objects + 1, expected, 1);
|
|
189 |
time_required = benchmarkQuery(
|
|
190 |
session, query_post_with_sum, total_objects, total_objects + 0, expected, 1);
|
|
191 |
time_required = benchmarkQuery(
|
|
192 |
session, query_post_with_sum, total_objects, total_objects - 1, expected, 1);
|
|
193 |
|
|
194 |
std::cerr << std::endl;
|
|
195 |
}
|
|
196 |
}
|
0 |
|
-
|