As Leonel mentioned, index creation will be your best friend in this issue.
I have been using web2py during the last two years, and till now, I had no
concerns about database performance because I was working in intranet
applications without a big volume of data.
In the latest project we had a bigger volumen and I needed to tune the
things up. The process was:
- Identify the slow queries:
- you can just print in the console the db(...)._select(..) (don't
miss the underscore) to get the sql translation of the DAL method.
- another practical way is to include in your view
{{=response.toolbar}} where you can see de sql queries executed and their
timings.
- Once you know where to focus, you can use the database command line
and get the query plan. In sqlite:
- explain query plan SELECT ...... (here you put the query obtained
in the previous step)
- I got something like this:
- 0|0|0|SCAN TABLE articulo AS art_PO
0|1|1|SEARCH TABLE familia USING AUTOMATIC COVERING INDEX (id_familia
=? AND centro=?)
0|2|2|SEARCH TABLE articulo AS art_NA USING AUTOMATIC COVERING INDEX (
familia=?)
0|3|3|SEARCH TABLE vinculo USING AUTOMATIC COVERING INDEX (vinculacion
=? AND articulo_NA=? AND articulo_PO=?)
0|4|4|SEARCH TABLE consulta USING AUTOMATIC COVERING INDEX (vinculo=?
AND masreciente=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
- Those AUTOMATIC COVERING INDEX means that sqlite analyzed the query
and decided it made sense to create a temporary index to get the data
faster, instead of scanning the full table. That temporary index is
deleted
after the query is executed. In englisht it means: *please create an
index for this table with the columns in the parenthesis*, i.e.,
create one index for familia with columns id_famiia and centro.
- Implicit joins vs explicit joins: the sql-92 recommendation is to use
explicit joins. It should be the same, but the last days I learnt that in
DAL, the implicit joins translate sometimes to a query with CROSS JOIN
while explicit joins translate to INNER JOIN. The results are the same
records, but I'm under the impression that the CROSS JOIN make a cartesian
product of the records in the joined tables, and then filters out according
to the WHERE CLAUSE, while INNER JOIN makes a faster query. With a low
number of records you will see no timing difference, but when there is a
big number of records there can be a big difference. I said I'm under the
impression, because it should be the same, but I kept getting a slow query
after creating indexes, and the timing only got right when I converted an
implicit join into an explicit join.
- Here you have an example of implicit versus explicit:
- Implicit join: db(db.dogs.owner == db.owner.id).select()
- This translates to: SELECT * from dogs, owner where dogs.owner =
owner.id , or maybe SELECT * from dogs CROSS JOIN owner where dogs.owner
=
owner.id. I have seen both results with different more complex queries. I
didn't test this example.
- Explicit join:
db(db.dogs.id>0).select(join=[db.owner.on(db.dogs.owner == db.owner.id),
])
- This translates to: SELECT * from dogs JOIN owner ON db.dogs.owner
== db.owner.id
- After this experience, I realized that explicit joins are easier to
read than implicit joins, and in one case it supposed a performance
gain(in
that case there was a main table with a join with a secondary table with
two aliases: see this link
<http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=inner+join#Inner-joins>
)
There is very skilled people in the database field in this group and it
would be great if they could clarify any misunderstanding in this post.
Best regards.
El jueves, 1 de junio de 2017, 11:04:09 (UTC+2), Mike Stephenson escribió:
>
> I see that with a million of rows, the search takes almost 15-20 seconds.
> How do I speed it up?
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
