Project

General

Profile

Feature #3621 ยป 0001-Reduce-QueryModel-rowCount-SELECTs-for-some-cases.patch

Bruce Toll, 10/09/2014 05:31 AM

View differences:

src/Wt/Dbo/QueryModel
368 368
  virtual Result resultById(long long id) const;
369 369

  
370 370
private:
371
  void cacheRow(int row) const;
372

  
371 373
  typedef std::vector<boost::any> AnyList;
372 374
  typedef std::map<int, long long> StableResultIdMap;
373 375

  
src/Wt/Dbo/QueryModel_impl.h
103 103
    return 0;
104 104

  
105 105
  if (cachedRowCount_ == -1) {
106
    Transaction transaction(query_.session());
106
    if (batchSize_)
107
      cacheRow(0);
107 108

  
108
    query_.limit(queryLimit_);
109
    query_.offset(queryOffset_);
110
    cachedRowCount_ = static_cast<int>(query_.resultList().size());
109
    if (cachedRowCount_ == -1) {
110
      Transaction transaction(query_.session());
111 111

  
112
    transaction.commit();
112
      query_.limit(queryLimit_);
113
      query_.offset(queryOffset_);
114
      cachedRowCount_ = static_cast<int>(query_.resultList().size());
115

  
116
      transaction.commit();
117
    }
113 118
  }
114 119

  
115 120
  return cachedRowCount_;
......
222 227
template <class Result>
223 228
Result& QueryModel<Result>::resultRow(int row)
224 229
{
230
  cacheRow(row);
231

  
232
  if (row >= cacheStart_ + static_cast<int>(cache_.size()))
233
    throw Exception("QueryModel: geometry inconsistent with database");
234

  
235
  return cache_[row - cacheStart_];
236
}
237

  
238
template <class Result>
239
void QueryModel<Result>::cacheRow(int row) const
240
{
225 241
  if (row < cacheStart_
226 242
      || row >= cacheStart_ + static_cast<int>(cache_.size())) {
227 243
    cacheStart_ = std::max(row - batchSize_ / 4, 0);
......
247 263
      if (id != -1)
248 264
	stableIds_[cacheStart_ + i] = id;
249 265
    }
250

  
251
    if (row >= cacheStart_ + static_cast<int>(cache_.size()))
252
      throw Exception("QueryModel: geometry inconsistent with database");
266
    if (static_cast<int>(cache_.size()) < qLimit
267
        && qOffset == 0 && cachedRowCount_ == -1)
268
      cachedRowCount_ = cache_.size();
253 269

  
254 270
    transaction.commit();
255 271
  }
256

  
257
  return cache_[row - cacheStart_];
258 272
}
259 273

  
260 274
template <class Result>
test/CMakeLists.txt
50 50
      dbo/DboTest2.C
51 51
      dbo/DboTest3.C
52 52
      dbo/Benchmark.C
53
      dbo/Benchmark2.C
53 54
      dbo/JsonTest.C
54 55
      private/DboImplTest.C
55 56
    )
test/dbo/Benchmark2.C
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
- 
    (1-1/1)