• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Upgraded database to SQLite3 now working.

Started by David L Morris, September 17, 2007, 07:47:15 AM

Previous topic - Next topic

David L Morris

Hi again Paul. I have my earlier test program and database to work using your examples.  The database has 9 tables.  Basically the program does three steps.  1. User selects a client 2. A product list is presented for the client and the user selects a product 3. A list is presented showing all TV commercials shown across all TV stations in the single week, what stations, time shown and duration. 

I have done this since I retired and have the same program made up using SQLite2 with Don Dickinson's tools, Tsunami and now with SQLite3 and your examples.

The last step 3 is much slower with your code.  The SQL joins several tables and you might like to have a look at the database and code - it is a zip file of 2320kb.  Let me know and I can send it through.

The SQL code and population of the list box is:-

CALLBACK FUNCTION ShowSpotKeys()
    LOCAL sSQL AS STRING, Msg AS STRING, Pad AS STRING , TheResult AS STRING
    LOCAL nRows     AS LONG
    LOCAL nCols     AS LONG
    LOCAL i         AS LONG
    LOCAL j         AS LONG
    LOCAL rs        AS LONG ' holds the recordset
    LOCAL sText AS STRING, lc AS LONG, Res AS LONG
    LOCAL AdvertID AS STRING
    CONTROL SEND ghDlg, 103, %LB_GETCOUNT, 0, 0 TO lc
    IF lc < 1 THEN EXIT FUNCTION
    MOUSEPTR 11
    LockWindowUpdate ghDlg
    LISTBOX RESET ghDlg, 104

    IF CBCTLMSG = %LBN_SELCHANGE THEN
        LISTBOX GET TEXT CBHNDL, 103 TO stext
        AdvertID = TRIM$(MID$(sText,51))
        IF VAL(AdvertID) > 0 THEN
            sSQL = "select SpotDateTime, NetworkName, CallSign, KeyNumber, Duration, ProgramName " + $CRLF _
                   + "from MTV20030105Spots, MTV20030105Stations, MTV20030105Programs "+ $CRLF _
                   + "where MTV20030105Spots.AdvertisementID " + $CRLF _
                   + "in (select AdvertisementID from MTV20030105AdLink where productid = " + AdvertID + ")" + $CRLF _
                   + "and MTV20030105Stations.StationID = MTV20030105Spots.StationID " + $CRLF _
                   + "and MTV20030105Programs.ProgramID = MTV20030105Spots.ProgramID "

          '  msgbox sSql
            errcode = sql3_select( hSession, sSQL, rs )
            IF errcode THEN
               ? "Error:" & STR$(errcode) & " during sql3_select (Select).":  GOTO ExitOut3
            END IF
            nRows = sql3_rsRowCount( rs )
            nCols = sql3_rsColCount( rs )
           ' msgbox str$(nRows) & " Rows found"
           ' Iterate the recordset
            FOR i = 1 TO nRows
               sql3_rsMoveTo rs, i
               ' Get the data for each column in the row.
               TheResult = SPACE$(110)
                   MID$(TheResult,1)  = sql3_rsGetAt( rs, 1)'"SpotDateTime")
                   MID$(TheResult,21) = sql3_rsGetAt( rs, 2)'"NetworkName")
                   MID$(TheResult,31) = sql3_rsGetAt( rs, 3)'"CallSign")
                   MID$(TheResult,41) = sql3_rsGetAt( rs, 4)'"KeyNumber")
                   MID$(TheResult,58) = sql3_rsGetAt( rs, 5)'"Duration")
                   MID$(TheResult,62) = sql3_rsGetAt( rs, 6)'"ProgramName")
                   IF TRIM$(TheResult) <> "" THEN
                        LISTBOX ADD ghDlg, 104, TheResult
                   END IF
            NEXT

        END IF

        Msg$ = STR$(nRows) + " spots displayed"
        CONTROL SET TEXT ghDlg, 105, Msg

    END IF
ExitOut3:
    IF rs THEN sql3_rsDestroy rs
    LockWindowUpdate 0
    MOUSEPTR 0
END FUNCTION
               

Paul Squires

Hi David,

Thanks for the update. If you could email me the files at support@planetsquires.com then I should be able to trace where the bottleneck is occurring. Hopefully the problem area is something that can be optimized.
:)
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

David L Morris

The email has been sent this morning from Sydney, Paul.  Let me know if there is any problem in receipt.

David

Paul Squires

Hi David,

I received your email and I will look at it today.  Thanks!

Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

I'm going to need another day or so to finish my re-coding of the server internals. I expect that the slow down that you are experiencing is due to the number of string concatenations needed when building the result set. I fixed that problem last week with a really fast concatenation routine but I couldn't test your sample with my new server code because I am in the midst of a major rewrite of the buffering of rows in the recordset being sent from the server to the client.
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

David L Morris

Thanks Paul.  I will wait for your solution and had hoped that this example would be a good test for your server development. 

Regards David

Paul Squires

Hi David,

I tested your program under the latest version of the client/server and it still shows a slow response. I will investigate to see if it is (1) The transmission time, (2) The time it takes to compile and run the query, (3) An inefficient SQL statement causing excessive database access, (4) Lack of an index to help speed things up, (5) Some stupid mistake in my code in relation to how the server is functioning.

Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

Hi David,

In your email to me you said "I have also tested the SQL with the free utility SQLiteSpy.exe and the data is returned in an instant. I have found that not to be the case. I ran a query and the 162 rows came back just as slowly using SQLite Expert Personal than it did with our client/server version. I fear that the bottleneck is in the design of the SQL query or lack of necessary indexes to help speed up SQLite's ability to generate the recordset.

Here is the sample SQL statement that I ran:

select SpotDateTime, NetworkName, CallSign, KeyNumber, Duration, ProgramName
  from MTV20030105Spots, MTV20030105Stations, MTV20030105Programs
  where MTV20030105Spots.AdvertisementID
  in (select AdvertisementID from MTV20030105AdLink where productid = 90887)
      and MTV20030105Stations.StationID = MTV20030105Spots.StationID
      and MTV20030105Programs.ProgramID = MTV20030105Spots.ProgramID


I will look at your table structures to see if there is a better and faster way to extract the rows.
                   
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

SQLite3explorer also returns the query results as slowly as SQLite Expert.
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

SQLite Database Browser is also as slow.

This leads me to believe that it is the SQL itself and the table structures that are the bottleneck.

I am not an SQL wizard so this may take a while for me to figure out.  ;)
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Mark Strickland

Paul,

If you suspect the SQL to be a problem try to do a query but prefix with EXPLAIN.

EXPLAIN select * from your_table

I am pretty sure SQLite supports it in at least a very basic fashion and it will tell you how it will approach the query.  It may take a little digging to interpret the results.  One of the good features of MS-SQL is the EXPLAIN is pretty much in plain English on how it will approach the query but what I see from SQLmaestro (my tool of choice) is pretty cryptic. Simple flat selects are pretty obvious but complex joins can cause an index file to not be used and EXPLAIN can show you.

If I can help let me know.

Mark

Mark Strickland, CISSP
www.SimplyBASICsecurity.com

David L Morris

Thanks Paul and others for the tests.  It is interesting, in that I am running SQLiteSpy.exe version 1.5 Beta and the results are returned almost instantly.  I just downloaded version 1.7.0 - released this month -and ran it to find the results are very slow in being returned.  This is a puzzle is it not?  When I use the explain on the SQL I get slightly different figures presented.

I will get back with any new findings. Maybe the more recent versions of SQLite3.exe and SQLite3.dll might be the reason.  Any more test results would be appreciated.

Regards David

David L Morris

I just ran the SQL using my SQLite3.exe 339KB dated 15 Jan 2006 and it also returns the results in an instant.

Paul Squires

Hi David,

I ran your query using SQLite Developer (www.sharpplus.com demo version) and the results were returned instantly. However, even though the generated EXPLAIN code is the same, it appears that the fast response is a bit of an illusion. Let me explain.

The results appear quickly on the screen because the program is only getting the rows as needed. It compiles the SQL statement, executes it, but only returns enough rows to fill the first page of the display grid. If you hit your Page Down key then eventually you will notice a pause as the query continues to execute in order to get more rows to display.

Internally, the "fast" query is using sqlite3_prepare() and sqlite3_step until all records are returned when it finally calls sqlite_finalize. The "slow" query is using sqlite3_get_table to get all of the records into memory at one time (this is the approach that I use and most other sqlite database managers use). The benefit is that after the query executes you will know exactly the number of returned rows. Using the other method you will not know the number of rows until you iterate through the entire recordset. A downside of the sqlite3_get_table approach is that the whole recordset is created in memory.... what if your query retruns a billion records? Ouch, I expect that the query would then fail whereas the other approach would continue to work. There are pros and cons with both approaches.


Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

Having said all that in my previous post, I still think that there must be beeter, more efficient, ways to structure your tables and sql queries. Maybe you could let some other sql gurus take a look at your data (I don't know if it is confidential or not) and they could provide feedback.
I will continue to try to find a solution myself but time and knowledge are my limiting factors.

Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com