SQLitening Support Forum

I just uploaded the newest version of the files (we'll call these Ver 0002) 

http://www.planetsquires.com/files/sqlite_test_0002.zip  (This file is about 3.7 megs)

2007-09-06   ver 0002
Added zLib1 compression.
Added much faster concatenation function that creates the recordset buffer to be sent to the client.
Added catalog.db3 test database (over 51,000 rows)
Added catalog.bas sample code for testing records in catalog.db3
Added PRAGMA synchronous = off when opening a database (allows for faster disk i/o)

Paul, the vers 0002 test kit works fine here, whether connecting through the server or running against the local database directly.  I'm getting timer values on the order of 0.84 seconds.

I'm curious about a couple of things:

Your test table includes a mix of text and numeric data types.  Is SQLite converting the numeric to string value automatically as the recordset is populated?

When building the recordset row by row you're using column numbers to fetch one field (column) at a time.  To do this you use column position numbers rather than column (field) names.  Is it possible to fetch an entire row in one step?  If you have to fetch the row column by column can you use column names?

I guess these questions reveal how little I know about SQL in general and SQLite in particular don't they?

Hello Paul,

just did the latest test.

Started server part on my MS Server 2003 for small business.

On local PC:

Modified catalog.bas
- replaced all ? with msgbox
- sSQL = "SERVER = 10.0.0.5; PORT = 12069; UID = PSS234; PASSWORD = sqlrocks"
Compile and run

Result: Time to iterate 51639 rows 1.484 seconds.

Very nice!!!

Marc

Quote from: tom cone jr on September 07, 2007, 08:51:59 AM
Your test table includes a mix of text and numeric data types.  Is SQLite converting the numeric to string value automatically as the recordset is populated?

Yes. The recordset columns are returned in string form. This makes it easier to work with. You can easily convert to numeric as needed via PB's VAL() function.

SQLite's internal field representations are not always exactly as you specify. Version 3 uses concepts called "Column affinity" or "Manifest Typing" (whereas in Version 2 everything was stored as text only). I think it is better for you to read this then for me to try to explain it!
http://www.sqlite.org/datatype3.html

Quote
When building the recordset row by row you're using column numbers to fetch one field (column) at a time.  To do this you use column position numbers rather than column (field) names.  Is it possible to fetch an entire row in one step?  If you have to fetch the row column by column can you use column names?

Sure, I could easily build a function that retrieves the entire row in one step but of what use would that be? Most of the time I would assume that the programmer needs to act on some field within that row rather the whole row at once. This would entail having the programmer parse the full row to extract the data and that process is not anymore efficient, or time saving, than getting the individual field/column data to begin with?

Quote
I guess these questions reveal how little I know about SQL in general and SQLite in particular don't they?

No worries - I don't know very much about SQL either. I am learning like the rest of us. The best thing about SQL is that it is like a complete programming language onto itself and it is very well documented in many books and tutorials.

Quote from: tom cone jr on September 07, 2007, 08:51:59 AM....If you have to fetch the row column by column can you use column names?

Yes, you can get the column data either by name (sql3_rsGet) or by position/ordinal number (sql3_rsGetAt).

Check out the file sql3Client.inc for a list of the currently available functions that you can call.

Quote from: Marc Van Cauwenberghe on September 07, 2007, 09:30:08 AM
Result: Time to iterate 51639 rows 1.484 seconds.

Very nice!!!

Marc

Excellent! That is pretty damn good results especially considering that there is still some fine tuning that needs to be done.

Actually, I bet that the majority of that time is spent outputting the rows to the "_debug.txt" file. Try commenting out that line and I bet the results will be even much better. I am trying really hard to optimize the send/receive buffers being sent to and from the client/server. The 51639 rows represents over 6 megs of data in the recordset. That's right, 6 megs. That data is compressed on the server and sent to the client where it is decompressed and then a local copy of the recordset is re-built in memory....... all in under a second (which includes transmission time!).

Sweeeeeeeeeet.

'       PRINT #f&, RTRIM$(sData, ANY ", ")
best 0.610

I hope I am getting this right
'          sData = sData & sql3_rsGetAt( rs, j ) & ", "

time 0.031

Paul,

I have tried the same test with

sSQL = "SERVER = localhost; PORT = default; UID = PSS234; PASSWORD = sqlrocks"

I get more or less the same results???
So server started local or on a remote PC = same results

Marc

Yes, they should be VERY similar because all that is being timed is the iteration of the resultset. Basically, the recordset exists at the time the TIMER starts so whether it is the client or the server doing the iteration then it should be the same.

A more fair comparison would be to move the " t1# = Timer " code line just before the "  sSQL = "SELECT * FROM parts;"  " line. Then the time would take into account the actual transmission of time it takes to move the recordset from the server to the client.

... therefore, my previous post about the 6 megs being moved in a second is incorrect (although it still looks cool when I read it) 

The timer should start just before the call to sql3_select. That call sends the request to the server where the server creates the recordset in memory. The server then iterates the recordset creating a long string of text, compresses it, and then sends it back to the client. The client receives the text, decompresses it, and then builds a local copy of the recordset. The recordset is then iterated using the local copy.

I would expect that your timing will probably be about double for the server connection versus the local connection.

Paul,

It would be nice to have a standard way of checking this.
I build in a loop starting before ' Create the session
This is what I get server(=remote PC) <-> local

Server

Time to iterate 51639 rows: 2.375 seconds.
Time to iterate 51639 rows: 2.391 seconds.
Time to iterate 51639 rows: 2.375 seconds.
Time to iterate 51639 rows: 2.390 seconds.
Time to iterate 51639 rows: 2.406 seconds.
Time to iterate 51639 rows: 2.391 seconds.
Time to iterate 51639 rows: 2.407 seconds.
Time to iterate 51639 rows: 2.484 seconds.
Time to iterate 51639 rows: 2.375 seconds.
Time to iterate 51639 rows: 2.390 seconds.

Local

Time to iterate 51639 rows: 2.250 seconds.
Time to iterate 51639 rows: 2.297 seconds.
Time to iterate 51639 rows: 2.297 seconds.
Time to iterate 51639 rows: 2.297 seconds.
Time to iterate 51639 rows: 2.359 seconds.
Time to iterate 51639 rows: 2.297 seconds.
Time to iterate 51639 rows: 2.344 seconds.
Time to iterate 51639 rows: 2.281 seconds.
Time to iterate 51639 rows: 2.282 seconds.
Time to iterate 51639 rows: 2.281 seconds.

Results are very, very similar. 
Marc

You must have a very, very fast network! 

Curious if the Cheetah client/server is also under development.
This is really exciting stuff.  Just bought apollo client/server, but now I have to reconsider.
http://www.vistasoftware.com
Read other posts and I think going with SQL was the right choice.

Hi Mike,

I previously posted a little "explanation" about how all of this came about: http://planetsquires.com/support/index.php?topic=2307.msg11524#msg11524

So, long story short, if this SQLite experiment continues to prove successful then Cheetah client/server is dead.

After running the catalog program noticed the catalog.db3 file cannot be moved.
Does the file need to be closed?