Small Performance Regression (3.21.0)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Small Performance Regression (3.21.0)

Olaf Schmidt-2
The new CoRoutine-approach seems to slow down certain
ViewDefinitions (in comparison to running a Query directly).

FWIW, here's a download-link to an NorthWind-SQLite-DB, which
already contains certain view-definitions:
http://vbRichClient.com/Downloads/NWind.zip
(an "Analyze"-command was already run against that DB).

The one view-def (where the difference is most apparent) is [Invoices]:

Here a: Select * From Invoices
needs about 20msec.

Whereas running the SQL of the View-definition directly,
needs only about 10msec.

A simpler view-definition in the above zipped NWind.db is:
[Order Details Extended]

Which is defined with the following SQL:
SELECT * FROM Products JOIN [Order Details] Using(ProductID)
Order By OrderID

When run directly, it comes up with the result after ~4msec,
whereas: Select * From [Order Details Extended] needs ~6msec.

Using Explain, it showed that in case we run against the
ViewNames, the CoRoutine-approach was present, whereas when
running the SQL directly (and faster), the CoRoutine-preparation
was absent from the Explain-Output...

No biggie here so far - but reporting the behaviour early seemed
like a good idea to me...

Kind Regards,

Olaf


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Keith Medcalf

I don't see any difference in the runtimes, at least not with the current head of trunk ...

sqlite> .once x
sqlite> select * from invoices;
Run Time: real 0.032 user 0.031250 sys 0.000000
sqlite> .once y
sqlite> SELECT ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Orders.CustomerID as CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country,
   ...> Employees.[FirstName] || ' ' || Employees.[LastName] AS Salesperson,
   ...> Orders.OrderID as OrderID, OrderDate, RequiredDate, ShippedDate,
   ...> Shippers.CompanyName, Products.ProductID as ProductID, ProductName, [Order Details].UnitPrice as UnitPrice, Quantity, Round(Discount,4) as DisCount, Round([Order Details].UnitPrice * Quantity * (1-Discount), 2) AS ExtendedPrice, Freight FROM
   ...> Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
   ...>            INNER JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID
   ...>            INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
   ...>            INNER JOIN Products ON Products.ProductID = [Order Details].ProductID
   ...>            INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia
   ...> Order By OrderID;
Run Time: real 0.031 user 0.031250 sys 0.000000
sqlite>


sqlite> .once x
sqlite> SELECT * FROM Products JOIN [Order Details] Using(ProductID)
   ...> Order By OrderID
   ...> ;
Run Time: real 0.015 user 0.015625 sys 0.000000
sqlite> .once y
sqlite> Select * From [Order Details Extended] ;
Run Time: real 0.016 user 0.015625 sys 0.000000
sqlite>




---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Olaf Schmidt
>Sent: Friday, 27 October, 2017 12:21
>To: [hidden email]
>Subject: [sqlite] Small Performance Regression (3.21.0)
>
>The new CoRoutine-approach seems to slow down certain
>ViewDefinitions (in comparison to running a Query directly).
>
>FWIW, here's a download-link to an NorthWind-SQLite-DB, which
>already contains certain view-definitions:
>http://vbRichClient.com/Downloads/NWind.zip
>(an "Analyze"-command was already run against that DB).
>
>The one view-def (where the difference is most apparent) is
>[Invoices]:
>
>Here a: Select * From Invoices
>needs about 20msec.
>
>Whereas running the SQL of the View-definition directly,
>needs only about 10msec.
>
>A simpler view-definition in the above zipped NWind.db is:
>[Order Details Extended]
>
>Which is defined with the following SQL:
>SELECT * FROM Products JOIN [Order Details] Using(ProductID)
>Order By OrderID
>
>When run directly, it comes up with the result after ~4msec,
>whereas: Select * From [Order Details Extended] needs ~6msec.
>
>Using Explain, it showed that in case we run against the
>ViewNames, the CoRoutine-approach was present, whereas when
>running the SQL directly (and faster), the CoRoutine-preparation
>was absent from the Explain-Output...
>
>No biggie here so far - but reporting the behaviour early seemed
>like a good idea to me...
>
>Kind Regards,
>
>Olaf
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Richard Hipp-3
In reply to this post by Olaf Schmidt-2
Thanks for the report.

Do you have any other interesting, complex, or slow queries using your
database that you can send me for testing purposes?

On 10/27/17, Olaf Schmidt <[hidden email]> wrote:

> The new CoRoutine-approach seems to slow down certain
> ViewDefinitions (in comparison to running a Query directly).
>
> FWIW, here's a download-link to an NorthWind-SQLite-DB, which
> already contains certain view-definitions:
> http://vbRichClient.com/Downloads/NWind.zip
> (an "Analyze"-command was already run against that DB).
>
> The one view-def (where the difference is most apparent) is [Invoices]:
>
> Here a: Select * From Invoices
> needs about 20msec.
>
> Whereas running the SQL of the View-definition directly,
> needs only about 10msec.
>
> A simpler view-definition in the above zipped NWind.db is:
> [Order Details Extended]
>
> Which is defined with the following SQL:
> SELECT * FROM Products JOIN [Order Details] Using(ProductID)
> Order By OrderID
>
> When run directly, it comes up with the result after ~4msec,
> whereas: Select * From [Order Details Extended] needs ~6msec.
>
> Using Explain, it showed that in case we run against the
> ViewNames, the CoRoutine-approach was present, whereas when
> running the SQL directly (and faster), the CoRoutine-preparation
> was absent from the Explain-Output...
>
> No biggie here so far - but reporting the behaviour early seemed
> like a good idea to me...
>
> Kind Regards,
>
> Olaf
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

David Raymond
In reply to this post by Keith Medcalf
Also getting 31ms for both. Though looking at the explain output it looks like for the view/subroutine/subquery version it's spending extra machine steps copying every result row from one set of registers to another.



Below this point only gratuitous stats and query plans



From the View directly:
selectid|order|from|detail
1|0|0|SCAN TABLE Orders
1|1|1|SEARCH TABLE Customers USING INDEX sqlite_autoindex_Customers_1 (CustomerID=?)
1|2|2|SEARCH TABLE Employees USING INTEGER PRIMARY KEY (rowid=?)
1|3|3|SEARCH TABLE Order Details USING INDEX idx_Order Details_OrdersOrder Details (OrderID=?)
1|4|4|SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)
1|5|5|SEARCH TABLE Shippers USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|SCAN SUBQUERY 1

Memory Used:                         553496 (max 567800) bytes
Number of Outstanding Allocations:   826 (max 1053)
Number of Pcache Overflow Bytes:     384032 (max 384032) bytes
Largest Allocation:                  120000 bytes
Largest Pcache Allocation:           8352 bytes
Lookaside Slots Used:                45 (max 100)
Successful lookaside attempts:       173893
Lookaside failures due to size:      68
Lookaside failures due to OOM:       11204
Pager Heap Usage:                    384036 bytes
Page cache hits:                     2333
Page cache misses:                   0
Page cache writes:                   0
Schema Heap Usage:                   28160 bytes
Statement Heap/Lookaside Usage:      57480 bytes
Fullscan Steps:                      829
Sort Operations:                     0
Autoindex Inserts:                   0
Virtual Machine Steps:               181881
-------- scanstats --------
Loop  1: SCAN SUBQUERY 1
         nLoop=1        nRow=2155     estRow=2304     estRow/Loop=2304    
-------- subquery 1 -------
Loop  1: SCAN TABLE Orders
         nLoop=1        nRow=830      estRow=768      estRow/Loop=768    
Loop  2: SEARCH TABLE Customers USING INDEX sqlite_autoindex_Customers_1 (CustomerID=?)
         nLoop=830      nRow=830      estRow=768      estRow/Loop=1      
Loop  3: SEARCH TABLE Employees USING INTEGER PRIMARY KEY (rowid=?)
         nLoop=830      nRow=830      estRow=768      estRow/Loop=1      
Loop  4: SEARCH TABLE Order Details USING INDEX idx_Order Details_OrdersOrder Details (OrderID=?)
         nLoop=830      nRow=2155     estRow=2304     estRow/Loop=3      
Loop  5: SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)
         nLoop=2155     nRow=2155     estRow=2304     estRow/Loop=1      
Loop  6: SEARCH TABLE Shippers USING INTEGER PRIMARY KEY (rowid=?)
         nLoop=2155     nRow=2155     estRow=2304     estRow/Loop=1      




From the raw SQL:

selectid|order|from|detail
0|0|0|SCAN TABLE Orders
0|1|1|SEARCH TABLE Customers USING INDEX sqlite_autoindex_Customers_1 (CustomerID=?)
0|2|2|SEARCH TABLE Employees USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE Order Details USING INDEX idx_Order Details_OrdersOrder Details (OrderID=?)
0|4|4|SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)
0|5|5|SEARCH TABLE Shippers USING INTEGER PRIMARY KEY (rowid=?)

Memory Used:                         550592 (max 567800) bytes
Number of Outstanding Allocations:   824 (max 1053)
Number of Pcache Overflow Bytes:     384032 (max 384032) bytes
Largest Allocation:                  120000 bytes
Largest Pcache Allocation:           8352 bytes
Lookaside Slots Used:                43 (max 100)
Successful lookaside attempts:       178452
Lookaside failures due to size:      75
Lookaside failures due to OOM:       11682
Pager Heap Usage:                    384036 bytes
Page cache hits:                     2333
Page cache misses:                   0
Page cache writes:                   0
Schema Heap Usage:                   28160 bytes
Statement Heap/Lookaside Usage:      52176 bytes
Fullscan Steps:                      829
Sort Operations:                     0
Autoindex Inserts:                   0
Virtual Machine Steps:               115071
-------- scanstats --------
Loop  1: SCAN TABLE Orders
         nLoop=1        nRow=830      estRow=768      estRow/Loop=768    
Loop  2: SEARCH TABLE Customers USING INDEX sqlite_autoindex_Customers_1 (CustomerID=?)
         nLoop=830      nRow=830      estRow=768      estRow/Loop=1      
Loop  3: SEARCH TABLE Employees USING INTEGER PRIMARY KEY (rowid=?)
         nLoop=830      nRow=830      estRow=768      estRow/Loop=1      
Loop  4: SEARCH TABLE Order Details USING INDEX idx_Order Details_OrdersOrder Details (OrderID=?)
         nLoop=830      nRow=2155     estRow=2304     estRow/Loop=3      
Loop  5: SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)
         nLoop=2155     nRow=2155     estRow=2304     estRow/Loop=1      
Loop  6: SEARCH TABLE Shippers USING INTEGER PRIMARY KEY (rowid=?)
         nLoop=2155     nRow=2155     estRow=2304     estRow/Loop=1      
---------------------------


From view:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     110   0                    00  Start at 110
1     InitCoroutine  1     76    2                    00  Invoices
2     Noop           7     28    0                    00
3     OpenRead       1     70    0     14             00  root=70 iDb=0; Orders
4     OpenRead       2     30    0     9              00  root=30 iDb=0; Customers
5     OpenRead       8     31    0     k(2,,)         02  root=31 iDb=0; sqlite_autoindex_Customers_1
6     OpenRead       3     34    0     3              00  root=34 iDb=0; Employees
7     OpenRead       4     57    0     5              00  root=57 iDb=0; Order Details
8     OpenRead       9     101   0     k(2,,)         02  root=101 iDb=0; idx_Order Details_OrdersOrder Details
9     OpenRead       5     87    0     2              00  root=87 iDb=0; Products
10    OpenRead       6     88    0     2              00  root=88 iDb=0; Shippers
11    Explain        1     0     0     SCAN TABLE Orders  00
12    Rewind         1     75    0                    00
13      Explain        1     1     1     SEARCH TABLE Customers USING INDEX sqlite_autoindex_Customers_1 (CustomerID=?) 00
14      Column         1     1     2                    00  r[2]=Orders.CustomerID
15      IsNull         2     74    0                    00  if r[2]==NULL goto 74
16      SeekGE         8     74    2     1              00  key=r[2]
17        IdxGT          8     74    2     1              00  key=r[2]
18        DeferredSeek   8     0     2                    00  Move 2 to 8.rowid if needed
19        Explain        1     2     2     SEARCH TABLE Employees USING INTEGER PRIMARY KEY (rowid=?)  00
20        Column         1     2     3                    00  r[3]=Orders.EmployeeID
21        SeekRowid      3     73    3                    00  intkey=r[3]; pk
22        Explain        1     3     3     SEARCH TABLE Order Details USING INDEX idx_Order Details_OrdersOrder Details(OrderID=?)  00
23        Rowid          1     4     0                    00  r[4]=rowid
24        SeekGE         9     73    4     1              00  key=r[4]
25          IdxGT          9     73    4     1              00  key=r[4]
26          DeferredSeek   9     0     4                    00  Move 4 to 9.rowid if needed
27          Explain        1     4     4     SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)  00
28          Column         4     1     5                    00  r[5]=Order Details.ProductID
29          SeekRowid      5     72    5                    00  intkey=r[5]; pk
30          Explain        1     5     5     SEARCH TABLE Shippers USING INTEGER PRIMARY KEY (rowid=?)  00
31          Column         1     6     6                    00  r[6]=Orders.ShipVia
32          SeekRowid      6     72    6                    00  intkey=r[6]; pk
33          Column         1     8     7                    00  r[7]=Orders.ShipName
34          Column         1     9     8                    00  r[8]=Orders.ShipAddress
35          Column         1     10    9                    00  r[9]=Orders.ShipCity
36          Column         1     11    10                   00  r[10]=Orders.ShipRegion
37          Column         1     12    11                   00  r[11]=Orders.ShipPostalCode
38          Column         1     13    12                   00  r[12]=Orders.ShipCountry
39          Column         1     1     13                   00  r[13]=Orders.CustomerID
40          Column         2     1     14                   00  r[14]=Customers.CompanyName
41          Column         2     4     15                   00  r[15]=Customers.Address
42          Column         2     5     16                   00  r[16]=Customers.City
43          Column         2     6     17                   00  r[17]=Customers.Region
44          Column         2     7     18                   00  r[18]=Customers.PostalCode
45          Column         2     8     19                   00  r[19]=Customers.Country
46          Column         3     2     34                   00  r[34]=Employees.FirstName
47          Concat         35    34    33                   00  r[33]=r[34]+r[35]
48          Column         3     1     36                   00  r[36]=Employees.LastName
49          Concat         36    33    20                   00  r[20]=r[33]+r[36]
50          Rowid          1     21    0                    00  r[21]=rowid
51          Column         1     3     22                   00  r[22]=Orders.OrderDate
52          Column         1     4     23                   00  r[23]=Orders.RequiredDate
53          Column         1     5     24                   00  r[24]=Orders.ShippedDate
54          Column         6     1     25                   00  r[25]=Shippers.CompanyName
55          Rowid          5     26    0                    00  r[26]=rowid
56          Column         5     1     27                   00  r[27]=Products.ProductName
57          Column         4     2     28    0              00  r[28]=Order Details.UnitPrice
58          RealAffinity   28    0     0                    00
59          Column         4     3     29    1              00  r[29]=Order Details.Quantity
60          Column         4     4     37    0              00  r[37]=Order Details.Discount
61          RealAffinity   37    0     0                    00
62          Function0      2     37    30    round(2)       02  r[30]=func(r[37..38])
63          Multiply       29    28    33                   00  r[33]=r[29]*r[28]
64          Column         4     4     43    0              00  r[43]=Order Details.Discount
65          RealAffinity   43    0     0                    00
66          Subtract       43    42    41                   00  r[41]=r[42]-r[43]
67          Multiply       41    33    39                   00  r[39]=r[41]*r[33]
68          Function0      2     39    31    round(2)       02  r[31]=func(r[39..40])
69          Column         1     7     32    0              00  r[32]=Orders.Freight
70          RealAffinity   32    0     0                    00
71          Yield          1     0     0                    00
72        Next           9     25    0                    00
73      Next           8     17    0                    00
74    Next           1     13    0                    01
75    EndCoroutine   1     0     0                    00
76    Explain        0     0     0     SCAN SUBQUERY 1  00
77    InitCoroutine  1     0     2                    00
78      Yield          1     109   0                    00  next row of "Invoices"
79      Copy           7     44    0                    00  r[44]=r[7]
80      Copy           8     45    0                    00  r[45]=r[8]
81      Copy           9     46    0                    00  r[46]=r[9]
82      Copy           10    47    0                    00  r[47]=r[10]
83      Copy           11    48    0                    00  r[48]=r[11]
84      Copy           12    49    0                    00  r[49]=r[12]
85      Copy           13    50    0                    00  r[50]=r[13]
86      Copy           14    51    0                    00  r[51]=r[14]
87      Copy           15    52    0                    00  r[52]=r[15]
88      Copy           16    53    0                    00  r[53]=r[16]
89      Copy           17    54    0                    00  r[54]=r[17]
90      Copy           18    55    0                    00  r[55]=r[18]
91      Copy           19    56    0                    00  r[56]=r[19]
92      Copy           20    57    0                    00  r[57]=r[20]
93      Copy           21    58    0                    00  r[58]=r[21]
94      Copy           22    59    0                    00  r[59]=r[22]
95      Copy           23    60    0                    00  r[60]=r[23]
96      Copy           24    61    0                    00  r[61]=r[24]
97      Copy           25    62    0                    00  r[62]=r[25]
98      Copy           26    63    0                    00  r[63]=r[26]
99      Copy           27    64    0                    00  r[64]=r[27]
100     Copy           28    65    0                    00  r[65]=r[28]
101     RealAffinity   65    0     0                    00
102     Copy           29    66    0                    00  r[66]=r[29]
103     Copy           30    67    0                    00  r[67]=r[30]
104     Copy           31    68    0                    00  r[68]=r[31]
105     Copy           32    69    0                    00  r[69]=r[32]
106     RealAffinity   69    0     0                    00
107     ResultRow      44    26    0                    00  output=r[44..69]
108   Goto           0     78    0                    00
109   Halt           0     0     0                    00
110   Transaction    0     0     68    0              01  usesStmtJournal=0
111   String8        0     35    0                    00  r[35]=' '
112   Integer        4     38    0                    00  r[38]=4
113   Integer        1     42    0                    00  r[42]=1
114   Integer        2     40    0                    00  r[40]=2
115   Goto           0     1     0                    00


From straight SQL:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     75    0                    00  Start at 75
1     Noop           6     28    0                    00
2     OpenRead       0     70    0     14             00  root=70 iDb=0; Orders
3     OpenRead       1     30    0     9              00  root=30 iDb=0; Customers
4     OpenRead       7     31    0     k(2,,)         02  root=31 iDb=0; sqlite_autoindex_Customers_1
5     OpenRead       2     34    0     3              00  root=34 iDb=0; Employees
6     OpenRead       3     57    0     5              00  root=57 iDb=0; Order Details
7     OpenRead       8     101   0     k(2,,)         02  root=101 iDb=0; idx_Order Details_OrdersOrder Details
8     OpenRead       4     87    0     2              00  root=87 iDb=0; Products
9     OpenRead       5     88    0     2              00  root=88 iDb=0; Shippers
10    Explain        0     0     0     SCAN TABLE Orders  00
11    Rewind         0     74    0                    00
12      Explain        0     1     1     SEARCH TABLE Customers USING INDEX sqlite_autoindex_Customers_1 (CustomerID=?) 00
13      Column         0     1     1                    00  r[1]=Orders.CustomerID
14      IsNull         1     73    0                    00  if r[1]==NULL goto 73
15      SeekGE         7     73    1     1              00  key=r[1]
16        IdxGT          7     73    1     1              00  key=r[1]
17        DeferredSeek   7     0     1                    00  Move 1 to 7.rowid if needed
18        Explain        0     2     2     SEARCH TABLE Employees USING INTEGER PRIMARY KEY (rowid=?)  00
19        Column         0     2     2                    00  r[2]=Orders.EmployeeID
20        SeekRowid      2     72    2                    00  intkey=r[2]; pk
21        Explain        0     3     3     SEARCH TABLE Order Details USING INDEX idx_Order Details_OrdersOrder Details(OrderID=?)  00
22        Rowid          0     3     0                    00  r[3]=rowid
23        SeekGE         8     72    3     1              00  key=r[3]
24          IdxGT          8     72    3     1              00  key=r[3]
25          DeferredSeek   8     0     3                    00  Move 3 to 8.rowid if needed
26          Explain        0     4     4     SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)  00
27          Column         3     1     4                    00  r[4]=Order Details.ProductID
28          SeekRowid      4     71    4                    00  intkey=r[4]; pk
29          Explain        0     5     5     SEARCH TABLE Shippers USING INTEGER PRIMARY KEY (rowid=?)  00
30          Column         0     6     5                    00  r[5]=Orders.ShipVia
31          SeekRowid      5     71    5                    00  intkey=r[5]; pk
32          Column         0     8     6                    00  r[6]=Orders.ShipName
33          Column         0     9     7                    00  r[7]=Orders.ShipAddress
34          Column         0     10    8                    00  r[8]=Orders.ShipCity
35          Column         0     11    9                    00  r[9]=Orders.ShipRegion
36          Column         0     12    10                   00  r[10]=Orders.ShipPostalCode
37          Column         0     13    11                   00  r[11]=Orders.ShipCountry
38          Column         0     1     12                   00  r[12]=Orders.CustomerID
39          Column         1     1     13                   00  r[13]=Customers.CompanyName
40          Column         1     4     14                   00  r[14]=Customers.Address
41          Column         1     5     15                   00  r[15]=Customers.City
42          Column         1     6     16                   00  r[16]=Customers.Region
43          Column         1     7     17                   00  r[17]=Customers.PostalCode
44          Column         1     8     18                   00  r[18]=Customers.Country
45          Column         2     2     33                   00  r[33]=Employees.FirstName
46          Concat         34    33    32                   00  r[32]=r[33]+r[34]
47          Column         2     1     35                   00  r[35]=Employees.LastName
48          Concat         35    32    19                   00  r[19]=r[32]+r[35]
49          Rowid          0     20    0                    00  r[20]=rowid
50          Column         0     3     21                   00  r[21]=Orders.OrderDate
51          Column         0     4     22                   00  r[22]=Orders.RequiredDate
52          Column         0     5     23                   00  r[23]=Orders.ShippedDate
53          Column         5     1     24                   00  r[24]=Shippers.CompanyName
54          Rowid          4     25    0                    00  r[25]=rowid
55          Column         4     1     26                   00  r[26]=Products.ProductName
56          Column         3     2     27    0              00  r[27]=Order Details.UnitPrice
57          RealAffinity   27    0     0                    00
58          Column         3     3     28    1              00  r[28]=Order Details.Quantity
59          Column         3     4     36    0              00  r[36]=Order Details.Discount
60          RealAffinity   36    0     0                    00
61          Function0      2     36    29    round(2)       02  r[29]=func(r[36..37])
62          Multiply       28    27    32                   00  r[32]=r[28]*r[27]
63          Column         3     4     42    0              00  r[42]=Order Details.Discount
64          RealAffinity   42    0     0                    00
65          Subtract       42    41    40                   00  r[40]=r[41]-r[42]
66          Multiply       40    32    38                   00  r[38]=r[40]*r[32]
67          Function0      2     38    30    round(2)       02  r[30]=func(r[38..39])
68          Column         0     7     31    0              00  r[31]=Orders.Freight
69          RealAffinity   31    0     0                    00
70          ResultRow      6     26    0                    00  output=r[6..31]
71        Next           8     24    0                    00
72      Next           7     16    0                    00
73    Next           0     12    0                    01
74    Halt           0     0     0                    00
75    Transaction    0     0     68    0              01  usesStmtJournal=0
76    String8        0     34    0                    00  r[34]=' '
77    Integer        4     37    0                    00  r[37]=4
78    Integer        1     41    0                    00  r[41]=1
79    Integer        2     39    0                    00  r[39]=2
80    Goto           0     1     0                    00


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Keith Medcalf
Sent: Friday, October 27, 2017 3:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] Small Performance Regression (3.21.0)


I don't see any difference in the runtimes, at least not with the current head of trunk ...

sqlite> .once x
sqlite> select * from invoices;
Run Time: real 0.032 user 0.031250 sys 0.000000
sqlite> .once y
sqlite> SELECT ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Orders.CustomerID as CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country,
   ...> Employees.[FirstName] || ' ' || Employees.[LastName] AS Salesperson,
   ...> Orders.OrderID as OrderID, OrderDate, RequiredDate, ShippedDate,
   ...> Shippers.CompanyName, Products.ProductID as ProductID, ProductName, [Order Details].UnitPrice as UnitPrice, Quantity, Round(Discount,4) as DisCount, Round([Order Details].UnitPrice * Quantity * (1-Discount), 2) AS ExtendedPrice, Freight FROM
   ...> Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
   ...>            INNER JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID
   ...>            INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
   ...>            INNER JOIN Products ON Products.ProductID = [Order Details].ProductID
   ...>            INNER JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia
   ...> Order By OrderID;
Run Time: real 0.031 user 0.031250 sys 0.000000
sqlite>


sqlite> .once x
sqlite> SELECT * FROM Products JOIN [Order Details] Using(ProductID)
   ...> Order By OrderID
   ...> ;
Run Time: real 0.015 user 0.015625 sys 0.000000
sqlite> .once y
sqlite> Select * From [Order Details Extended] ;
Run Time: real 0.016 user 0.015625 sys 0.000000
sqlite>




---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Olaf Schmidt
>Sent: Friday, 27 October, 2017 12:21
>To: [hidden email]
>Subject: [sqlite] Small Performance Regression (3.21.0)
>
>The new CoRoutine-approach seems to slow down certain
>ViewDefinitions (in comparison to running a Query directly).
>
>FWIW, here's a download-link to an NorthWind-SQLite-DB, which
>already contains certain view-definitions:
>http://vbRichClient.com/Downloads/NWind.zip
>(an "Analyze"-command was already run against that DB).
>
>The one view-def (where the difference is most apparent) is
>[Invoices]:
>
>Here a: Select * From Invoices
>needs about 20msec.
>
>Whereas running the SQL of the View-definition directly,
>needs only about 10msec.
>
>A simpler view-definition in the above zipped NWind.db is:
>[Order Details Extended]
>
>Which is defined with the following SQL:
>SELECT * FROM Products JOIN [Order Details] Using(ProductID)
>Order By OrderID
>
>When run directly, it comes up with the result after ~4msec,
>whereas: Select * From [Order Details Extended] needs ~6msec.
>
>Using Explain, it showed that in case we run against the
>ViewNames, the CoRoutine-approach was present, whereas when
>running the SQL directly (and faster), the CoRoutine-preparation
>was absent from the Explain-Output...
>
>No biggie here so far - but reporting the behaviour early seemed
>like a good idea to me...
>
>Kind Regards,
>
>Olaf
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Olaf Schmidt-2
Am 27.10.2017 um 21:59 schrieb David Raymond:
> Also getting 31ms for both...
Thanks for testing that guys...

I was able to get similar timings (about 30msec), when the
(quite large) resultset-output was delegated into a file...

To avoid doing expensive File-IO in that test, I'd recommend
to run it again with the two small Files (SQL1.txt and
SQL2.txt), which I've now included in the ZipFile...
Here again the Link: http://vbRichClient.com/Downloads/NWind.zip

With these two Files, I get the following results (on a Win8.1 machine):

sqlite> .read SQL1.txt
Run Time: real 0.000 user 0.000000 sys 0.000000
Run Time: real 0.010 user 0.000000 sys 0.000000
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>

sqlite> .read SQL2.txt
Run Time: real 0.000 user 0.000000 sys 0.000000
Run Time: real 0.006 user 0.000000 sys 0.000000
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite>

Note: SQL1-Text is copying into an InMemory-Temp-Table
from the Invoices-View - whilst SQL2 is doing the same
from "directly given SQL" (the same as used for the ViewDef).

The timings in both cases vary about +-1msec -
the values as posted represent the median-values.
Not really factor 2 (as encountered when copying into an
inmemory-resultsets of my wrapper-library) - but nearly so.

> Though looking at the explain output it looks for the
> view/subroutine/subquery version it's spending like extra machine
> steps copying every result row from one set of registers to another.

Yep, that Extra-copying of the RowValues seems to be the culprit
(the Select has a decent Field-Count - and these efforts
seem to sum-up over the scanned Records then).

Olaf

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Olaf Schmidt-2
In reply to this post by Richard Hipp-3
Am 27.10.2017 um 21:11 schrieb Richard Hipp:
> Thanks for the report.
>
> Do you have any other interesting, complex, or slow queries
> using your database that you can send me for testing purposes?

With regards to the NWind.db, the Invoices-View is the
one with the largest processing-time.
NWind.db is a direct Import from an (MS-JET-based),
NWind.mdb - and the View-Defs are nearly identical.

The MS-JET-Engine needs about 35msec for the Invoices-View BTW.
whereas SQLite 3.17 (for directly executed SQL, as well as the View):
- 14msec (before "Analyze" -> on a fresh created DB)
- 10msec (after  "Analyze" ran once against the DB-Connection)

and as said, with the new 3.21 I got:
- 24msec (before "Analyze", running the View)
- 15msec (before "Analyze", running direct SQL)
- 19msec (after  "Analyze", running the View)
- 10msec (after  "Analyze", running direct SQL)

Other (larger) File-DBs I use, are only large due to
FTS4/FTS5-Virtual-Tables - and there everything is
"fast enough" (typically I get my resultsets from
those in under 3msec).

Our main-usage of SQLite in Applications is local settings-storage
and InMemory-DBs (for "parking" larger resultsets App-internally,
which came in from https-WebRequests or over ADO->MS-SQLServer).

Kind Regards,

Olaf


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Bart Smissaert
This is interesting, not so much for the performance regression, but for
the fact that the posted Northwind database has
spaces in table names and view names. I wasn't aware this is allowed and it
caused a lot of errors in my app. I have this
nearly fixed now (by adding the square brackets), but I am left with one
problem. This is the fact that for example the view
Product Sales for 1997 has a UDF in the view definition: DatePart.
Obviously this is causing an error:
no such function: DatePart
I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION, but
will only avoid the error for
EXPLAIN and EXPLAIN QUERY PLAN.

Is there a way to handle this with a compile option or otherwise, so that
instead of an error a Null will be produced or just the
original value?

RBS

On Sat, Oct 28, 2017 at 7:05 AM, Olaf Schmidt <[hidden email]> wrote:

> Am 27.10.2017 um 21:11 schrieb Richard Hipp:
>
>> Thanks for the report.
>>
>> Do you have any other interesting, complex, or slow queries using your
>> database that you can send me for testing purposes?
>>
>
> With regards to the NWind.db, the Invoices-View is the
> one with the largest processing-time.
> NWind.db is a direct Import from an (MS-JET-based),
> NWind.mdb - and the View-Defs are nearly identical.
>
> The MS-JET-Engine needs about 35msec for the Invoices-View BTW.
> whereas SQLite 3.17 (for directly executed SQL, as well as the View):
> - 14msec (before "Analyze" -> on a fresh created DB)
> - 10msec (after  "Analyze" ran once against the DB-Connection)
>
> and as said, with the new 3.21 I got:
> - 24msec (before "Analyze", running the View)
> - 15msec (before "Analyze", running direct SQL)
> - 19msec (after  "Analyze", running the View)
> - 10msec (after  "Analyze", running direct SQL)
>
> Other (larger) File-DBs I use, are only large due to
> FTS4/FTS5-Virtual-Tables - and there everything is
> "fast enough" (typically I get my resultsets from
> those in under 3msec).
>
> Our main-usage of SQLite in Applications is local settings-storage
> and InMemory-DBs (for "parking" larger resultsets App-internally,
> which came in from https-WebRequests or over ADO->MS-SQLServer).
>
>
> Kind Regards,
>
> Olaf
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Richard Hipp-3
In reply to this post by Olaf Schmidt-2
On 10/27/17, Olaf Schmidt <[hidden email]> wrote:
> The new CoRoutine-approach seems to slow down certain
> ViewDefinitions (in comparison to running a Query directly).

Can you please download and try the latest "Prerelease Snapshot" from
https://sqlite.org/download.html and let me know whether or not it
clears your issue.  Thanks.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Olaf Schmidt-2
In reply to this post by Bart Smissaert
Am 28.10.2017 um 19:22 schrieb Bart Smissaert:
> This is interesting, not so much for the performance regression, but for
> the fact that the posted Northwind database has
> spaces in table names and view names. I wasn't aware this is allowed
> and it caused a lot of errors in my app. I have this nearly fixed now
> (by adding the square brackets),...

More SQL-Standard-like would be DoubleQuote-chars instead of the
SquareBrackets, but thankfully SQLite allows for [...] (as well
as for SingleQuotes around column-names).

> ...I am left with one problem. This is the fact that for example the view
> Product Sales for 1997 has a UDF in the view definition: DatePart.
> Obviously this is causing an error:
> no such function: DatePart

Yep, sorry about that - the SQLite-COM-wrapper (vbRichClient5.dll)
opens an SQLite-DB with nearly the whole set of MS-JET-engine compatible
Functions (DatePart, DateDiff, Left$, Right$, Mid$, Format$, Instr etc.)
by default (though there is a Parameter in the OpenDB-calls which allows
to switch that off).

The Northwind-DB (introduced by MS as a Demo-DB with MS-Access a
few decades ago) was used deliberately, to show the quite painless
migration from the JET-engine to the SQLite-COM-wrapper in some
Demo-Apps (including nearly compatible View-Defs, along with
compatible Jet-SQL-Functions).

> I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION,
> but will only avoid the error for
> EXPLAIN and EXPLAIN QUERY PLAN.
>
> Is there a way to handle this with a compile option or otherwise, so that
> instead of an error a Null will be produced or just the
> original value?

I for my part would *insist* on SQLite throwing an error, when
it encounters unknown (userdefined) functions or collations
in a Database, which a currently given wrapper-extension does
not support.

How else would you have been able, to stumble over the (for your
wrapper-lib) non-working view-definition?

The question it boils down to (using "VB-speak") is:
"Do we really want an 'On Error Resume Next'-behaviour in SQLite?"...
;-)

Olaf



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Olaf Schmidt-2
In reply to this post by Richard Hipp-3
Am 28.10.2017 um 23:00 schrieb Richard Hipp:
> On 10/27/17, Olaf Schmidt <[hidden email]> wrote:
>> The new CoRoutine-approach seems to slow down certain
>> ViewDefinitions (in comparison to running a Query directly).
>
> Can you please download and try the latest "Prerelease Snapshot" from
> https://sqlite.org/download.html and let me know whether or not it
> clears your issue.  Thanks.

Yep - the issue is solved (just compiled the amalgamation-snapshot).
Query-times are now again identical (at 10msec) for the Invoices-View
and its SQL-representation.

Thank you for reacting that fast...

Kind Regards,

Olaf

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Small Performance Regression (3.21.0)

Bart Smissaert
In reply to this post by Olaf Schmidt-2
> I for my part would *insist* on SQLite throwing an error, when
it encounters unknown (userdefined) functions or collations

Yes, sure, you need to know about the missing UDF or collation. I suppose
there
is no sensible way to know about this and still be able to run the SQL. I
looked at
the DatePart UDF as used in other databases eg:
https://www.w3schools.com/sql/func_sqlserver_datepart.asp
and it looks useful and will just add this in my wrapper.

RBS

On Sat, Oct 28, 2017 at 10:54 PM, Olaf Schmidt <[hidden email]> wrote:

> Am 28.10.2017 um 19:22 schrieb Bart Smissaert:
>
>> This is interesting, not so much for the performance regression, but for
>> the fact that the posted Northwind database has
>> spaces in table names and view names. I wasn't aware this is allowed and
>> it caused a lot of errors in my app. I have this nearly fixed now (by
>> adding the square brackets),...
>>
>
> More SQL-Standard-like would be DoubleQuote-chars instead of the
> SquareBrackets, but thankfully SQLite allows for [...] (as well
> as for SingleQuotes around column-names).
>
> ...I am left with one problem. This is the fact that for example the view
>> Product Sales for 1997 has a UDF in the view definition: DatePart.
>> Obviously this is causing an error:
>> no such function: DatePart
>>
>
> Yep, sorry about that - the SQLite-COM-wrapper (vbRichClient5.dll)
> opens an SQLite-DB with nearly the whole set of MS-JET-engine compatible
> Functions (DatePart, DateDiff, Left$, Right$, Mid$, Format$, Instr etc.)
> by default (though there is a Parameter in the OpenDB-calls which allows
> to switch that off).
>
> The Northwind-DB (introduced by MS as a Demo-DB with MS-Access a
> few decades ago) was used deliberately, to show the quite painless
> migration from the JET-engine to the SQLite-COM-wrapper in some
> Demo-Apps (including nearly compatible View-Defs, along with
> compatible Jet-SQL-Functions).
>
> I know there is the compile option SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION,
>> but will only avoid the error for
>> EXPLAIN and EXPLAIN QUERY PLAN.
>>
>> Is there a way to handle this with a compile option or otherwise, so that
>> instead of an error a Null will be produced or just the
>> original value?
>>
>
> I for my part would *insist* on SQLite throwing an error, when
> it encounters unknown (userdefined) functions or collations
> in a Database, which a currently given wrapper-extension does
> not support.
>
> How else would you have been able, to stumble over the (for your
> wrapper-lib) non-working view-definition?
>
> The question it boils down to (using "VB-speak") is:
> "Do we really want an 'On Error Resume Next'-behaviour in SQLite?"...
> ;-)
>
>
> Olaf
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users