0

In order to quickly calculate some features for later calculations I have attempted to store a table that allows looking up distances between locations that have been pre-calculated. I am using Python and SQLalchemy. I have 76,807,696 rows in my table. However a single query is taking upwards of 300 seconds.

My Database is defined as:

class LocationsLookupTable(db.Model):
    """Use this class to create the database Model for the locations lookup 
table.
This class is used for creating the database table Model for the locations
lookup table. It will store the ID pair and their distance value. There
will also be a unique ID for each value in the database table.
"""

id = db.Column(db.Integer, primary_key=True, unique=True)
firstId = db.Column(db.Integer, nullable=False)
secondId = db.Column(db.Integer, nullable=False)
firstLocationGeoPointX = db.Column(db.Float)
firstLocationGeoPointY = db.Column(db.Float)
secondLocationGeoPointX = db.Column(db.Float)
secondLocationGeoPointY = db.Column(db.Float)
distanceValue = db.Column(db.Integer)

And I am querying it using the following query:

result = models.LocationsLookupTable.query.filter_by(
            firstId=firstId, secondId=secondId).first()

I have some theories as to why the performance is so bad. The first is that I simply have too many rows. The second is that a database is not suited for this task. The third is that my query is not well formulated. This is however my first attempt at using a Database of any kind so I would like to ask how I can speed up my query before I would make any large changes to my project.

Edit: I solved my problem. @ypercubeᵀᴹ was indeed correct in that the solution was to index my columns that I was querying. SELECT statements are incredibly fast now by a huge margin, taking roughly 0.03 seconds. Thankyou.

closed as too broad by Erik Darling, RDFozz, mustaccio, Mr.Brownstone, McNets Nov 13 '17 at 8:12

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Show us the actual query produced and the table definition (the SQL, not the python code). Are there any indexes on the table? The 4th theory (no indexes) is probably what happens. An index on (firstid, secondid) would likely help, a lot. – ypercubeᵀᴹ Nov 10 '17 at 12:07
  • Originally using SQLite but I'm in the process of moving over to mySQL. I can't seem to figure out how to display the raw SQL query or schema using SQLalchemy it hides a lot from the user. I'm looking at indexing firstID and secondID but I need to rebuild the table now which takes the best part of a day. Is this the schema? tinypic.com/r/1pb6eq/9 – harrison Nov 10 '17 at 15:03
  • What does "rebuild the table" mean? If you mean drop, create an reinsert all the data, you don't have to do that. You can just add an index to the existing table (either using sqlalchemy/alembic or connecting directly to the database) . – ypercubeᵀᴹ Nov 10 '17 at 16:37
3

I have some theories as to why the performance is so bad.

Lets see.

The first is that I simply have too many rows.

Probably not. There is no limit on the number of rows in a (SQL) DBMS. 80 million rows is not that much these days.

The second is that a database is not suited for this task.

Unlikely. We don't know which DBMS you use (Postgres? MySQL? SQLite? SQL Server? There are hundreds of them) but I think any of them could handle what seems to be a simple query.

The third is that my query is not well formulated.

No, it seems good. The query produced will look like:

SELECT id, firstid, secondid, ..., distanceValue
FROM LocationsLookupTable
WHERE firstId = ?
  AND secondId = ? ;

So, what is causing this?

If that is all the code that is creating the table, then what is missing is indexes. Lacking them, the database optimizer/planner has to create a query plan that scans the entire table. While the table is not big, it is not very small either.

Adding an index on (firstid, secondid) will improve performance a lot.

However 300 seconds looks too long, even for a table scan, so the hardware or the DBMS settings should be examined, too.

  • 2
    Not to mention we haven't discussed the server. If he's a student is he sharing server space, or running something local. Could just be contention on the server. But yeah, indexes would be my first suspect – Paul Nov 13 '17 at 13:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.