• Welcome, Guest. Please login.
February 23, 2020, 03:17:50 am


Welcome to the SQLitening support forums!

Select Speed

Started by Fred Meier, November 28, 2007, 02:50:44 pm

Previous topic - Next topic

Fred Meier

November 28, 2007, 02:50:44 pm Last Edit: November 28, 2007, 03:29:31 pm by Fred Meier

I've been using SQLite for sometime using a PB shell I wrote.  I've been
very pleased with the performance and simplicity of SQLite.  It also seems
to do OK when the database is on a local area networked server, but my
usage has been low volume to date.  I'm looking forward to have your c/s
abilities available for any high volume apps that may come in the future. 

I wrote some test programs to compare the speed of selecting records using
my shell vs. your shell in a local environment.  My shell is considerable
faster for most record sets (up to 4 times as fast for RecordSets > 10 records). 
I just assumed that was the overhead for having the ability to run
local or remote. 

I download your source and discovered your are using the Get_Table command
while I use the Prepare and then Step when the next record is requested. 
I realize the Get_Table allows you to move "around" the RS and know the
number of records 'up front' while Prepare-Step only allows "next". 

My question is -- Do you really want that kind of overhead for
functionally that is normally not needed?  On large RS's their would be a
user noticed delay along with the large amount of extra memory used by SQLite
to store all the records. 

Maybe you would want two ways to "Select" -- one which is fast but allows only
"next" and a second which is slow but allows more functionally.

If you would want only one way, then I would prefer the fast
(Prepare-Step) one.  On the few occasions I might need the extra
functionally provided by Get_Table, it could always be provided by my own
routines coded only in the program that needs it. 

Thanks for your consideration and thank you for this project.  Let my know
if I can help in any way.


Paul Squires

Hi Fred,

You have hit on a topic of design that Jose mentioned to me a while back as well. I know of the both methods and realistically I expect that I will switch the code to use the Prepare/Step method. I used the GetTable method in the beginning more so just to get the entire code base working. It does require that the entire recordset be read when executed (which allows the rowcount to be known). I agree that it may be a measure slower than the Prepare/Step method.

You can be sure that this area of design will be addressed. It "should be" relatively easy to make the switch.

Thanks for your input. Don't be shy expressing your opinion. The more people that use the code and speak up about it, the faster it will be developed and problem areas fixed. Also, interest in the project by others keeps my interest in it as well.  ;)

Fred Meier


Thanks for your reply.  I need to pay more attention to prior posts.  I
now see that this was discussed before and I agree with what Jose said. 

Yes, it will be easy to change from Get_Table to Prepare/Step.  I think you
will find Prepare/Step to be the easiest of all the ways to process all of
the SQLite commands (Select, Exe, etc.).