_SQLite database speed help

[Champak]

;===Delete stations from the array that we already have their coordinates. This will save query time from the website.
    Dim $GASSTATION_HOLD[0][6]
    Local $aRow, $Found = False
    Local $K = 0
    For $I = UBound($FULL_GASSTATION_LIST) - 1 To 0 Step - 1

        $111 = StringSplit($FULL_GASSTATION_LIST[$I][0],@CR)
        $111[1] = StringReplace($111[1], @CR, "")
        $111[1] = StringReplace($111[1], @LF, "")
        $111[1] = StringReplace($111[1], @CRLF, "")

        _SQLite_QuerySingleRow ( $GasDriving_Log, "SELECT Address, Latitude, Longitude FROM Gas_Station_Coordinates WHERE City=" & _SQLite_FastEscape($City) & " And Address=" & _SQLite_FastEscape($111[1]) & ";", $aRow )
        If @error Then
            $Found = False
            ContinueLoop
        Else
            $Found = True
        EndIf

        If $Found = True Then
            _ArrayAdd($GASSTATION_HOLD, _ArrayExtract($FULL_GASSTATION_LIST, $I, $I))
            $GASSTATION_HOLD[$K][4] = $aRow[1]
            $GASSTATION_HOLD[$K][5] = $aRow[2]
            _ArrayDelete($FULL_GASSTATION_LIST, $I)
            $K += 1
        EndIf
    Next

How can I speed up this loop. The SQLite database query is slowing things down. My purpose for including this database portion was to speed things up. It is a little faster than what it was without it only because it helps me limit the amount of times I have to geolocate from a website, but not as fast as I expected it to be. Is there a better way to do this database query? Or should I transfer the database to an array and do this query (I haven't checked if I can easily convert a database table to an array yet)?

Thanks.

[mLipok]

I think schema for this table would be helpful.

[Champak]

I don't know exactly what you mean. Do you mean the column titles?

State|City|Address|Name|Latitude|Longitude

The size varies, and is updated periodically.

[mLipok]

how you create this database/table ?

[Jochem]

maybe add before the loop:

_SQLite_Exec($GasDriving_Log, "begin immediate;")

and after the loop

_SQLite_Exec($GasDriving_Log, "commit;")

 

Edited January 27, 2015 by Jochem

[jchd]

Enclosing a single select inside an immediate transaction doesn't make it any faster.

But having an index on the required column would speed up things dramatically. Awaiting the schema ("create table ..." string) and average number of rows in table to give more advice.

Edited January 27, 2015 by jchd

[Champak]

Sorry, didn't think this important since its created outside of the loop. Its created the first time the app is ever run, and checked for each time the app is run after that. Average number of rows is really unknown, it will all depend on the amount of stations I accumulate over time based on citys/countys and states I frequent. If I REALLY had to guess, I would say the table shouldn't reach any more that 1000 rows.

_SQLite_Exec($GasDriving_Log, "CREATE TABLE Log_Fuel_Travel (Date,Price,Gallons,Cost,Last_Fill_Distance);")
_SQLite_Exec($GasDriving_Log, "CREATE TABLE Gas_Station_Coordinates (State,City,Address,Name,Latitude,Longitude);")
_SQLite_Exec($GasDriving_Log, "CREATE TABLE Log_Speed_Distance (Date,Average_Speed,Peak_Speed,Distance);")
_SQLite_Exec($GasDriving_Log, "CREATE TABLE Calculate_Fuel (Total_Distance,Recent_Fuel_Up);")
    _SQLite_Exec($GasDriving_Log, "INSERT INTO Calculate_Fuel (Total_Distance,Recent_Fuel_Up) VALUES ('','');");")
_SQLite_Exec($GasDriving_Log, "CREATE TABLE Calculate_SpeedDistance (Date,Speed_Average,Peak_Speed,Calculator_Incriment,Distance);")
    _SQLite_Exec($GasDriving_Log, "INSERT INTO Calculate_SpeedDistance (Date,Calculator_Incriment,Speed_Average,Peak_Speed,Distance) VALUES ('','','','','');");")

Edited January 27, 2015 by Champak

[Champak]

Bump

[jchd]

Sorry for not answering sooner.

You'd have great benefit of creating your columns with SQLite recognized types. From what I can see you're inserting everything as strings (text) which isn't the best choice for numeric data.

For instance if ever you want to locate the nearest station from a given GPS point, having coordinates stored in string form would preclude any effective search. That's just an example. Of course, you'd want to load actual numeric data (not strings) into numeric columns.

Also creating an index on the required column(s) would speed up searches dramatically.

For the select used in post #1 make that:

create index ixCityAddr on Gas_Station_Coordinates (City, Address);

run that once (_SQLite_Exec) and see if it does you any good.

[Champak]

having coordinates stored in string form would preclude any effective search. That's just an example. Of course, you'd want to load actual numeric data (not strings) into numeric columns.

Why? How? Would the search be that much faster for a database table that prob won't exceed 600 rows? I did a search on data types and numerical data and couldn't find an explanation. I've only come across the types of data types.

 

Also creating an index on the required column(s) would speed up searches dramatically.

For the select used in post #1 make that:

create index ixCityAddr on Gas_Station_Coordinates (City, Address);

run that once (_SQLite_Exec) and see if it does you any good.

How will having an index help if I'm searching by address and not by index? Or are you saying I should change how I do the search? Can you show me? And I'm not understanding this "create index ixCityAddr on Gas_Station_Coordinates (City, Address);", are you saying create a whole new table with this layout with an index?

You have me confused right now, lots of questions lol.

[jchd]

For the database engine, an index is quite similar to what it is to, say, a textbook: it helps locate what you're looking for faster by searching through a smaller amount of data.

But contrary to a book where text is put in place logically once for all, a database engine (SQL-ish I mean) stores rows without special ordering: just where it see fits and convenient space in the database container(s). This means that without an index, a SELECT will have to scan whole of the table since it can't know in advance if the result will have zero, one or many rows matching your select criterion.

A full scan of a 600..1000 rows shouldn't take that long anyway but you can greatly help by creating (once for all) the index as I advised. An index is just an appendice to a table which the engine uses when possible to speed searches because the index is maintained in a sorted structure (B-tree), thus the engine can look up the index way faster than the full table. The presence of an index is transparent to operations and you don't need to change anything in your routine operations.

Now about declaring SQLite datatypes in a table and not storing everything as strings. Numbers in numeric form sort quite fast --thanks to their compact binary formats) and correctly but when strored as strings not only comparison is slower but also often provides unwanted results: indeed "100" < "11" while 100 > 11.

[Champak]

Ok, I'll go ahead and look up how to set the data types for the columns.

As far as the index, I'm looking at the table and there is a column with sequential numbers on the left, aren't those index numbers? I didn't create it as far as I know. I understand...from what you say...that making the index will stop the search after the first one is found and make it faster, perfect reason to create it, but I don't understand how the index actually works, especially if you say I don't have to change the way I search. This however is not something I'm too concerned about learning how it works as long as it works and I don't have to change anything.

[jchd]

I don't know which tool you use to look at your DB so I don't know if the numbers you see are sequential row numbers displayed by the tool or row IDs (rowid) which are a column SQLite creates for you when no primary key is declared.

As for the tool you can use to manage your DBs, I warmly recommend SQLite Expert (the free edition will do).

FYI using an index is not limited to the first row matching a WHERE clause: the engine always returns as many rows as requested.

The structure of an index in SQLite is a B-tree, simply said, it's a balanced binary tree.

Something else: your DB seems to contain duplicated data, something best avoided for many reasons.

You have the same data (at least is it how I read your schema) in muliple columns, for instance:

Date, Average_Speed, Peak_Speed, Distance in table Log_Speed_Distance

Date, Speed_Average, Peak_Speed, Calculator_Incriment,Distance in table Calculate_SpeedDistance

You probably would find it much easier to simply load bulk input data in your Log table and create views to make the calculations you wish. Don't forget that you can use the power of built-in and add-on SQLite functions to create views (think of them as tables which are re-built on demand) to display meaningful result in the most convenient ways for human confort.

If you need assistance for anything I'd be pleased to help you make it a better application.

[Champak]

I'm using sqlite expert free.

No, it's not duplicated, although I can understand how it may seem like that. Totally different data and purpose although named similarly. I'll rework the titles if that's an issue.

I don't completely understand this.

You probably would find it much easier to simply load bulk input data in your Log table and create views to make the calculations you wish. Don't forget that you can use the power of built-in and add-on SQLite functions to create views (think of them as tables which are re-built on demand) to display meaningful result in the most convenient ways for human confort.

Sure, your assistance would be appreciated, alway looking to make it a better application.

[Champak]

bump