Project

General

Profile

Feature #3621

Dbo: Consider omitting select count(1) in QueryModel rowCount when possible

Added by Bruce Toll almost 8 years ago. Updated almost 8 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Target version:
Start date:
10/09/2014
Due date:
% Done:

0%

Estimated time:

Description

When a QueryModel has a batch size large enough to retrieve all rows, it's possible to skip the initial select count(1) used to obtain the row count. This can significantly improve the efficiency of queries where the cost of a SELECT count(1) is high, e.g. when a query includes expensive window functions, and the result rows fits within a single batch.

Please see the attached patch for additional documentation. The patch has been lightly tested with the github version of Wt, 3.3.3-19-gad17d0b, and passes the existing suite of Dbo tests, as well as an additional test/benchmark included with the patch. Testing has been limited to Postgres and Sqlite3.

NOTE: One area where performance could potentially degrade is for applications that call rowCount() without subsequently retrieving rows. You may catch other issues upon review. Let me know if you'd like any changes.


Files

#1

Updated by Koen Deforche almost 8 years ago

  • Status changed from New to InProgress
  • Assignee set to Koen Deforche
  • Target version set to 3.3.4
#2

Updated by Koen Deforche almost 8 years ago

  • Status changed from InProgress to Resolved

Hey,

Great idea!

To avoid users from suffering a performance hit when only using resultCount(), I've documented that they can avoid this by setting batchSize to 0.

Regards,

koen

#3

Updated by Koen Deforche almost 8 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF