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:[email protected]] 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-
>[email protected]] On Behalf Of Olaf Schmidt
>Sent: Friday, 27 October, 2017 12:21
>To: [email protected]
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
