Champak

_SQLite database speed help

15 posts in this topic

;===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.

Share this post


Link to post
Share on other sites



I think schema for this table would be helpful.


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

how you create this database/table ?


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

maybe add before the loop:

_SQLite_Exec($GasDriving_Log, "begin immediate;")

and after the loop

_SQLite_Exec($GasDriving_Log, "commit;")

 

Edited by Jochem

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by Champak

Share this post


Link to post
Share on other sites

Bump

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

bump

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now