• Welcome, Guest. Please login.
 
July 03, 2020, 03:03:36 pm

News:

Welcome to the SQLitening support forums!


Number of Records

Started by JoeByrne, December 14, 2008, 03:33:51 am

Previous topic - Next topic

JoeByrne

I don't recall if I asked this before.  I didn't see anything after doing a quick search:

Depending on the number of records (Rows) in a table, the following snippet can be used to quickly calculate this number:

SQL$ = "SELECT COUNT(*) FROM MyTable;"
slSEL(SQL$,1)
slGetRow(1)
NumberOfRows& = VAL(slF(1,1))
slCloseSet(1)


The SELECT command uses the parameter COUNT to get the total number of records in 'MyTable'  We get the actual value by getting the value from the first 'column' in the record set (slF(1))

If your table contains more than 20 thousand records, this process may take a few seconds to execute.  Is there another way to calculate the number of rows in a table?

Paul Squires

Quote from: JoeByrne on December 14, 2008, 03:33:51 am
If your table contains more than 20 thousand records, this process may take a few seconds to execute.  Is there another way to calculate the number of rows in a table?

From the documentation that I read and the SQLite mailing list, the common solution is to create a Trigger that gets automatically invoked for any INSERT/DELETE type of SQL command. The Trigger updates a row value stored in another table that will hold the value of the number of records. This involves a little longer for every INSERT/DELETE because another table value has to changed but it is also very fast if you have the need to always compute the number of rows in a table.

Paul Squires

You could also name your COUNT:


SQL$ = "SELECT COUNT(*) As RowCount FROM MyTable;"
slSEL(SQL$,1)
slGetRow(1)
NumberOfRows& = VAL(slFN("RowCount",1))
slCloseSet(1)


Bern Ertl

Can this be adapted to count the number of records in a table matching a certain condition?  Or do I have to fetch all the records and then count the number of rows returned in a recordset?

JoeByrne

To the best of my knowledge, there is no 'simple' way to count the number of rows returned.  You'd have to do that manually.

David Warner

QuoteCan this be adapted to count the number of records in a table matching a certain condition?


You can limit the rows being counted by adding a WHERE clause to the SQL...

SQL$ = "SELECT COUNT(*) As RowCount FROM MyTable WHERE SalesCode = 99;"
slSEL(SQL$,1)
slGetRow(1)
NumberOfRows& = VAL(slFN("RowCount",1))
slCloseSet(1)

Bern Ertl

Excellent.  I wasn't sure the WHERE clause worked with the COUNT() function.  Good news for me.  :)

James Klutho

July 09, 2009, 08:14:33 pm #7 Last Edit: July 09, 2009, 08:16:41 pm by James Klutho
I have been disappointed with the count() function speed on a table size of 30,000 records.  This function appears to work faster and it is very fast on a recount with slight alterations to the original set.  Any thoughts?

Jim




FUNCTION GetCaseCount(MyFilter AS STRING) AS LONG
    LOCAL I AS LONG
    I=0
    IF LEN (MyFilter) = 0 THEN
         slSel "select RowID from master",4
         WHILE slGetRow(4)
           INCR I
         WEND
         slCloseSet(4)
      ELSE
         slSel "select RowID from master where " & MyFilter,4
         WHILE slGetRow(4)
           INCR I
         WEND
         slCloseSet(4)
    END IF

    FUNCTION = I

END FUNCTION 


Fred Meier

My tests, against a table with 300,000 records, has count(*) running approx
4 times faster than the slGetRow loop in local mode and approx 23 times faster in remote mode.

Note that Select 1 is slightly faster than Select RowID but still a lot slower than Count(*)

   slSel "Select 1 from parts"
   do while slGetRow
      incr llA
   loop
'============ .14693488 Sec in Local Mode
'============ .84143636 Sec in Remote Mode (LocalHost)


   slSel "Select rowid from parts"
   do while slGetRow
      incr llA
   loop
'============  .16349696 Sec
'============ 1.10434740 Sec in Remote Mode (LocalHost)


   slSel "Select count(*) from parts"
   slGetRow
   llA = val(slF(1))
   slCloseSet
'============== .04250660 Sec
'============== .04699748 Sec in Remote Mode (LocalHost)

James Klutho

July 09, 2009, 11:52:11 pm #9 Last Edit: July 09, 2009, 11:54:51 pm by James Klutho
Fred

I must really have something messed up.  My 30,000 records table takes about 10 to 15 seconds with a

Select count() from master

command to process.

Your times on a 300,000 record table look great.  My table has 82 columns of text type data with one column being a primary integer key- pretty generic.  Generally my indexed selects are fairly fast.  I noticed you had a "*" in the count function which I did not.  Maybe that is my problem.


Thanks
Jim


David Warner

Hi James,

definitely try putting the asterisk in as an argument to the count function.

From the SQLite help page http://www.sqlite.org/lang_aggfunc.html

QuoteThe first form returns a count of the number of times that X is not NULL in a group.

count(X)

The second form (with no argument) returns the total number of rows in the group.

count(*)



I think the 'with no argument' bit is misleading us here and that an asterisk argument is required in the second form.

Best Regards,

David

Fred Meier

I get the same speed with Count() and Count(*) so there must be something different with your table or .... ?

James Klutho

July 10, 2009, 01:05:22 pm #12 Last Edit: July 10, 2009, 02:53:31 pm by TechSupport
Quote
Does count() have to walk though all the records in a database to find the 30,000 in my particular table?


No, it shouldn't. SQLite knows which table to operate based on the Master table. The Master table has a pointer to the first node in the Table that you performing the Count on. The Count should be able to quickly locate the correct Table and then process the results.

I wonder if the database has become overly fragmented from many inserts and deletes. You could try using Vacuum to put the entire database in a more optimized state.

(oops, sorry Jim, looks like I "modified" your post rather than replying to it - Paul Squires)


David Warner


Hi Fred

QuoteI get the same speed with Count() and Count(*)


Count(*) is valid SQL and I'm not sure that Count() should be working at all.

James

Does your table have a primary key constraint or a unique index? If not you could give that a try.

David



James Klutho

The master table has a primary key but no index.  Counting on the primary key may help a little. Reading some forums and answers by Richard Hipp about the speed for count() is not unexpected.  Vaccuuming may help the performance.  I will continue to play with this problem to see if I can goose the speed.