select count(*) ... where ('1'='1') is slow

[leeyuiwah]

Hi,

We are writing our own server-side logic to do fetching with streaming -- we fetch only N rows at a time. We need to do this because our data sets are huge (more than 10 GB of data). If we don't limit to N rows at a time, all the rows will be read into memory and this causes troubles with the server.

The logic is working, but we still have performance issue. Specifically, we noticed that for each fetch, there is an extra query to count the number of rows in the DB table. Something like:

select count(*) from rs358223955 where ('1'='1')

This query itself is very slow too (e.g. taking about 130 seconds to finish). However, if I tried the same query without the WHERE clause, that is:

select count(*) from rs358223955

This query is much faster (0.016 seconds).

Is there a way for us to force SmartGWT to not do the row counting, or to do it without the WHERE clause?

By the way, in our case, the operationBinding is non-progressiveLoading (i.e. progressiveLoading=false).



Our environment:

1. SmartClient Version: v10.0p_2015-02-03/PowerEdition Deployment (built 2015-02-03)

2. Browser: (not applicable)

3. Server log: (not applicable)

[Isomorphic]

It seems like, from your other thread, you've already discovered that there is a skipRowCount setting that accomplishes this.

However, what DB is this that doesn't realize 1=1 is a no-op? We may want to work around the issue if it's actually a DB other people might use.

[leeyuiwah]

The DB system is MySQL. Thanks!

However, as my other post said. SkipRowCount does not work as expected. The row counting query is still being issued, and, even worse, it gets the wrong idea of tableClause. Can you take a look at that?

[Isomorphic]

We've eliminated unnecessary where clause from row count query. You may download next nightly build (2015-06-24) and try it out.