Is datetime special in thuis context or will constant expression hoisting like
this happen for any function?
Pepijn
> Op 22-nov.-2013 om 15:35 heeft Richard Hipp <d...@sqlite.org> het volgende
> geschreven:
>
> The www.sqlite.org server logs are stored in an SQLite database (of
> course). We have a script that is run daily on that database with a dozen
> or so queries that look something like this:
>
> .print
> .print **** Downloads in the past 24 hours ****
> .mode column
> .width -6 -6 100
> .header on
> SELECT count(*) AS count, count(distinct ip) AS IPs, url
> FROM log
> WHERE date>datetime('now','-24 hours')
> AND (url GLOB '*/2013/*'
> OR url GLOB '*/snapshot/*'
> OR url GLOB '*/xfer'
> OR url GLOB '*/blob/*')
> GROUP BY url
> ORDER BY count DESC;
>
> Today's database contains 6 days worth of data, is 590MB in size, and is
> just short of 2 million records. There are no indices, so each query is a
> full table scan. Using SQLite 3.8.1, the query above took 1.793 seconds on
> a recent test run. But the code on trunk (and the latest snapshots at
> http://www.sqlite.org/download.html) took only 0.686 seconds. The
> difference is that the string constants and the datetime() function call
> are factored out of the inner loop in 3.8.2.
>
> To be fair: Our production script does not contain exactly the SQL shown
> above. Rather than using the bare datetime() call, the real script says:
> "(SELECT datetime('now','-24 hours'))". Putting the datetime() call inside
> a subquery is a trick that causes the datetime function to only be invoked
> once even without the new constant-function factoring optimization of
> 3.8.2. And with that trick, the performance difference is not nearly so
> dramatic (though 3.8.2 is still faster by about 10%). Also, the
> performance difference here would only apply to a full table scan that
> spends a lot of time looping inside of the virtual machine. There would
> not be nearly as dramatic a difference if the database held an index on the
> "log.date" column.
>
> Nevertheless, we suspect that queries like the above are common in the
> wild, and so we hope that the upcoming 3.8.2 release will make a big
> difference for some people.
>
> If you try the 3.8.2 pre-release snapshot in your application and see a
> performance improvement, we'd appreciate hearing from you.
>
> For testing and comparison purposes, the optimization that doubles the
> performance of the query above can be disabled using the following API:
>
> sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, 8);
>
> where "db" is the database connection create by sqlite3_open(), or if you
> are using the command-line shell:
>
> .testctrl optimizations 8
>
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
