RowCount takes very long time

texwillerx

Member
Licensed User
Longtime User
I have an sqlite database with 7 columns and approximately 2 million records. When I execute an SQL statement, since I use indexed fields, it takes very little time. But, after execquery statement, the statement

i=cursorsoz.rowcount

takes very long time (execquery=6 seconds, rowcount=44 seconds for 2281 records found).

1. Is this normal to take so long time?
2. Is there a way to speed up rowcount operation (or is tehere another way to speed up going through all records)?

Thanks in advance for all help

Talat
 

texwillerx

Member
Licensed User
Longtime User
Setting cursor position to 0

I tried another thing and I thought the results would be useful.

Instead of getting rowcount, I tried to count all records by going through the result set. But, when I set the cursor position to 0, it took same amount of time as getting rowcount (after setting to 0, the rest of traversing all records took very small amount of time)

Talat
 
Last edited:
Upvote 0

Jack Cole

Well-Known Member
Licensed User
Longtime User
That's quite a few records to load up in a cursor. Are you loading those all into a view?

What happens if you execute the same query that you use to build the cursor, but instead just get the count from a separate query of the database (select count(*) where ... )?

You'll likely have problems if you try to load all that info to views because of the time it will take and android's time limits on processes. I get around this by doing smaller subsets of records into a cursor.
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
I cannot tell whether this delay is natural, I don't even know the immediate steps you take, as Jack said above. But, just a thought, do you use sql BeginTransaction and endTransaction? This makes things faster.
Oh, and in case you just need the rows' number, you can always execute a query with 'select count'
 
Last edited:
Upvote 0

texwillerx

Member
Licensed User
Longtime User
Following is the actual SQL statement I use to retrieve rows.

Select * from DEFINITIONS where FROM_LANG=1 and TERM >= 'true' and TERM <= 'truezzzzzzzzzzzzzzzzzzzzz' order by TERM,TO_LANG

Immediately after this statement, I use the folloowin statement

i=cursorsoz.RowCount

The select statement executes in 7 seconds whereas i=.. executes in 47 seconds. The database contains 7 columns, and I use three columns data for display.

I have to use the above statement because I use data retrieved. As I have said in the previous reply, instead of getting rowcount, if I want to just go over the records, the first time I set the position to zero, it takes 47 seconds.

Insertıng the resultant information into a list view (only column 2, 2281 records) took only a couple of seconds.

Nothing has changed by using begin and endtransaction (İn fact these statements only affect the update statements).

Thanks for every people who helps.

Talat
 
Upvote 0

texwillerx

Member
Licensed User
Longtime User
Removed order by: Only the execquery took slightly less time, no change for rowcount

Indices: I have indices for all search fields (FROM_LANG and TERM)

Timings for different queries
=====================

Select * from DEFINITIONS where FROM_LANG=1 and TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz'
execquery: 6 rowcount: 43 found records: 2,2281

Select * from DEFINITIONS where FROM_LANG=1
execquery: 6 rowcount: 48 found records: 1,438,178

Select DEFINITION from DEFINITIONS where FROM_LANG=1
execquery: 4 rowcount: 44 found records: 1,438,178

As you see, the times for all queries are nearly same, even the resultant set contains 70 times more records.

Here the only problem is with the getting rowcount procedure.

Regards
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
Can you copy-paste the code for all of your queries? I know they are simple, but sometimes it's helpful to review the code.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Did you try getting the record count via the COUNT function:
B4X:
i=SQL1.ExecQuerySingleResult("SELECT count(*) FROM DEFINITIONS")         'any additional clauses
msgbox(i,"Number of records.")
Also, please paste your code as mc73 requested.
 
Upvote 0

texwillerx

Member
Licensed User
Longtime User
Sorry for bothering everybody, but lots of things and wanted to share the results.

The results for several queries are as follows:

SQL string: Select * from DEFINITIONS where FROM_LANG=1 and TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz' order by TERM,TO_LANG
Execquery: 2 Rowcount: 43637 Rows: 2281

SQL string: Select * from DEFINITIONS where FROM_LANG=1 and TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz'
Execquery: 16 Rowcount: 43119 Rows: 2281

SQL string: Select * from DEFINITIONS where TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz'
Execquery: 47 Rowcount: 479 Rows: 2454

SQL string: Select * from DEFINITIONS where FROM_LANG=1
Execquery: 2 Rowcount: 48931 Rows: 1438178

SQL string: Select DEFINITION from DEFINITIONS where FROM_LANG=1
Execquery: 88 Rowcount: 44927 Rows: 1438178

SQL string: Select DEFINITION from DEFINITIONS where TERM='get'
Execquery: 90 Rowcount: 98 Rows: 12

SQL string: Select * from DEFINITIONS where TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz' order by TERM,TO_LANG
Execquery: 1 Rowcount: 567 Rows: 2454

We can see that, when FROM_LANG is included in the query, getting row count takes a long time.

In order to find out whether this is because it returned a large data set or because of any other reason, I tried the followings:

1. I have recreated the database: Same results
2. I have changed the data type of FROM_LANG to text: Same results
3. I have changed the data for FROM_LANG in the database from "1" to "ABC" and run the query as FROM_LANG='ABC': Same results
4. I have recreated the database (FROM_LANG data type is integer) and run the following queries:

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,500
Execquery: 4 Rowcount: 24 Rows: 500

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,5000
Execquery: 1 Rowcount: 213 Rows: 5000

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,50000
Execquery: 1 Rowcount: 1222 Rows: 50000

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,500000
Execquery: 1 Rowcount: 17962 Rows: 500000

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,5000000
Execquery: 3 Rowcount: 53188 Rows: 1438177

Now We are sure that, the time takes to execute RowCount directly proportional to the number of records returned.

RESULT
======
If you want to process huge amount of data, use LIMIT option for SELECT statement and get data by chunks. See the results given below.

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,500
Execquery: 0 Rowcount: 20 Rows: 500

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 501,5000
Execquery: 1 Rowcount: 286 Rows: 5000

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 5001,50000
Execquery: 1 Rowcount: 1506 Rows: 50000

SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 50001,500000
Execquery: 2 Rowcount: 19392 Rows: 500000

Thanks for everybody.

Regards
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
In the previous post there is the following code and result set:
SQL string: Select * from DEFINITIONS where FROM_LANG=1
Execquery: 2 Rowcount: 48931 Rows: 1438178.
This took 2 seconds and returned 48931 records.

And also the following code and result set using LIMIT:
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,5000000
Execquery: 3 Rowcount: 53188 Rows: 1438177.
This took 3 seconds and returned 53188 records.

1. The one not using the LIMIT took less time to execute. Why would you use LIMIT then.
2. Why is the number of records returned by each query different from each other when you are basically querying the entire table in both cases. I never used LIMIT, please explain.
Thanks
 
Upvote 0

texwillerx

Member
Licensed User
Longtime User
I am very sorry for the confusing usage of the wording.

RowCount is the time used for cursor.Rowcount
Rows is the number of found records

Hence, in both cases, Rows are same, hence same number of records are returned (1,438,177 records).

There is very small time difference between the two queries (I think the difference is because of android process management system).

These examples show that, time requied to get the cursor.rowcount depends solely the number of records returned (please refer to the other LIMIT examples).

Again sorry for confusing.

Regards
 
Upvote 0
Top