0
$\begingroup$

I have a Python Flask application that connects to an Azure Cloud SQL Database, and uses the Pandas read_sql method with SQLAlchemy to perform a select operation on a table and load it into a dataframe.

recordsdf = pd.read_sql(recordstable.select(), connection)

The recordstable has around 5000 records, and the function is taking around 10 seconds to execute (I have to pull all records every time). However, the exact same operation with the same data takes around 0.5 seconds when I'm selecting from a local SQL Server database.

What can I do to reduce the time it takes to load data from Azure to a dataframe? Would moving the entire Python application to Azure serverless help? Thanks

Additional Information

  • Azure database is on Standard tier with 20 DTUs
  • Database region has been configured to be close to my location
  • Ideally looking for the operation to take under 2 seconds
Improve this question
$\endgroup$

1 Answer 1

Reset to default
0
$\begingroup$

You have several phases of the data retrieval process: connection time, download time, and database procesing time.

If you store your data in a csv file in a blob storage then the processing time will be faster (essentially zero). So every day you could save the data from the database to a csv file and then access the file when you need it.

Azure serverless will reduce the connection time and download time (if your internet connection is slow), but will not reduce the processing time of the database.

Improve this answer
$\endgroup$
3
  • $\begingroup$ Hi, thanks for your response. In my case since I'm creating a recommender engine, I'm looking for the data access to be live rather than from a csv. Is there any way, apart from increasing the Azure price tier, that I could improve the database processing performance time? Thanks $\endgroup$
    – Allen Wu
    Commented Apr 25, 2020 at 14:49
  • $\begingroup$ You can make a trigger procedure that will append data to a csv file every time there is an insert in the table. $\endgroup$
    – keiv.fly
    Commented Apr 25, 2020 at 16:53
  • $\begingroup$ Thank you for your help! I will try that. $\endgroup$
    – Allen Wu
    Commented Apr 25, 2020 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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