SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Paul Squires on September 06, 2007, 08:15:14 PM

Title: SQLite3 client/server 0002
Post by: Paul Squires on September 06, 2007, 08:15:14 PM
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)

Title: Re: SQLite3 client/server 0002
Post by: tom cone jr on September 07, 2007, 08:51:59 AM
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? :-[
Title: Re: SQLite3 client/server 0002
Post by: Marc Van Cauwenberghe on September 07, 2007, 09:30:08 AM
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

Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 12:37:32 PM
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.
Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 12:41:13 PM
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.
Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 12:43:17 PM
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.



Title: Re: SQLite3 client/server 0002
Post by: Marc Van Cauwenberghe on September 07, 2007, 01:19:17 PM
'       PRINT #f&, RTRIM$(sData, ANY ", ")
best 0.610

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

time 0.031

:o :o :o

Title: Re: SQLite3 client/server 0002
Post by: Marc Van Cauwenberghe on September 07, 2007, 02:06:32 PM
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
Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 02:38:11 PM
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.

Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 02:42:00 PM
... 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.
Title: Re: SQLite3 client/server 0002
Post by: Marc Van Cauwenberghe on September 07, 2007, 03:48:59 PM
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
Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 05:21:41 PM
You must have a very, very fast network!  :D

Title: Re: SQLite3 client/server 0002
Post by: mikedoty on September 07, 2007, 06:05:22 PM
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.
Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 06:18:15 PM
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. :(

Title: Re: SQLite3 client/server 0002
Post by: mikedoty on September 07, 2007, 06:35:59 PM
After running the catalog program noticed the catalog.db3 file cannot be moved.
Does the file need to be closed?   
Title: Re: SQLite3 client/server 0002
Post by: Paul Squires on September 07, 2007, 07:40:18 PM
Quote from: mikedoty on September 07, 2007, 06:35:59 PM
After running the catalog program noticed the catalog.db3 file cannot be moved.
Does the file need to be closed?  
You will need to shut down the server first because the server is leaving open the connection to the database for performance purposes. Rather than close the database after every connection termination, I am leaving the database open in case a second, third, fourth, etc... connection tries to access the database. In the future, I could certainly add code that polls the connections to see if any have the database in use - if not, then I could close it.