I have a project where I store database queries for PostgreSql in the application config database (sqlite).
On startup of the application I load all the (parametrized) queries into a dictionary. So far so good, it works fine since I have developed myself a application to compose the queries into the sqlite database.
My question is: does it make any difference, performance wise, if I leave End Of Line in my queries. Of course it’s easy to filter these out.
I can’t find documentation to back this up, but my understanding is that whitespace within the query (not within strings or identifiers, obviously) is irrelevant.
The database server will do that when parsing the query. I’m sure the time used for that is neglectable compared to processing the query.
+1
makes NO DIFFERENCE… so my input is… add as much whitespace (spaces, tabs, linefeeds) as you think it takes to make the code READABLE.
It takes the database engine a fraction of a second to deal with it … and it only needs to do it once (ie. not for each record)
As a total aside, early incarnations of 4D actually got slower with more comments in code. It became common practice to enclose comments in a if false block to prevent this, a convention that endured even after the problem was fixed.
Yes, I understand but I was curious how much time I would take the database to deal with spaces, end of lines etc. Just for optimization purpose I would like to know. Think I can do a test by measuring ticks launching a large query full of line feeds etc. compared to the same query but without all the line feeds etc. Will post my result here.
I’m going to bet that you find no measurable or significant difference. I am curious though.
I too doubt you will be able to measure any discernable difference… and even if you do it could be “noise” level related to system events/interrupts, hard disk latency and other things you have no control over…
Line endings are no different to the database engine than spaces or tabs. Just another “if charval = 13” test, which it is already doing anyway, so the presence or absence of those characters has no real effect.
I created and SQLite testdatabase from text file “BIG Import Zip Code Data” I got from @Kem Tekinay , put an index on column Zipcode.
With or without spaces and EndOfLines in my selectquery, finding one specific record in the database to have it in a recordset object, execution time is the same. On average I don’t see any difference. Parsing the query seems to have just a very little or no impact on the rest of the process of finding and returning data.
[code] dim start,stop as Double
#pragma BackgroundTasks False
start = Microseconds
// Select query to recordset executed here
stop = Microseconds
#pragma BackgroundTasks True
MsgBox “It took " + Str(stop-start) + " Microseconds”[/code]
Via Xojo.Core.Date and .DateInterval you can even go down to nanoseconds. Helpful if you don’t loop often through your test methods.
[code]Using Xojo.Core
Dim Start As Date = Date.Now
// do Whatever
Dim Duration As DateInterval = Date.Now - start
Msgbox “It took “+ Duration.Nanoseconds.ToText+” Nanoseconds”[/code]
Using Xojo.Core
Dim Start As Date = Date.Now
#pragma BackgroundTasks False
// --------------------------------------
// do the thing
for x as integer = 1 to 1000
dim strTest as string = x.ToText
next
// --------------------------------------
#pragma BackgroundTasks True
Dim Duration As DateInterval = Date.Now - start
Msgbox "It took "+ Duration.Nanoseconds.ToText+" Nanoseconds"
Does not give me the result in nanoseconds that I expected. The result always ends with 3 or 4 zero’s