Skip to content

Latest commit

 

History

History
401 lines (307 loc) · 11.4 KB

35_local_database.md

File metadata and controls

401 lines (307 loc) · 11.4 KB

Reduce Local Database Access

- Understand the speed differences in data retrieval (RAM vs. storage vs. internet).
- Modify code to use cached data instead of repeatedly accessing the database.
- Implement these optimizations in functions such as `add_assessment`, `get_assessment`, and `get_chart`.
- Testing the changes to ensure improved performance.

Remember when we discussed optimisation, we focused on preventing the frontend from unnecessarily retrieving data from the backend. This was a low-hanging fruit in improving our website speed, since the communication between the frontend and the backend is the slowest communication that occurs in our web app.

Planning

Now that we have addressed this, we can look at other slow routes of data communication. To do this we need to understand the different speed of data retrieval:

  1. Fastest - retrieve from RAM (memory)
  2. Medium - retrieve from storage (HDD / SSD / Servers)
  3. Slowest - retrieve over internet

frontend backend

Looking at the diagram of our web app we can see that there are two more places that communication occurs:

  • On the frontend between the application and the device
  • On the backend between the server and the database

The frontend communication occurs by retrieving data from the RAM, so we can't get a significant increase in speed here, but the backend communication involves server retrieval from storage, so we can speed that up.

In our current assessment_service, the database is unnecessarily calls cached data in:

  • add_assessment - user
  • get_assessment - user
  • get_chart - user and chart

In all these cases, the cached data can be passed to the server when the assessment_service method is called.

Code

add_assessment function

Lets start with the add_assessment.

  1. Open the assessment_service module and go to add_assessment
  2. Delete the anvil.users.get_user() call in line 11
  3. Add user as an argument to be passed - like the highlight below
:linenos:
:lineno-start: 9
:emphasize-lines: 2
@anvil.server.callable
def add_assessment(user, subject, details, start_date, due_date):
  app_tables.assessments.add_row(user= user,
                                 subject= subject,
                                 details=details,
                                 start_date=start_date,
                                 due_date=due_date,
                                 completed=False)

:class: notice
**line 10** → adds `user` as a value to be passed when the method is called

Now we have to change the call to add_assessment to include the user

  1. Open the data_access module
  2. Add line 51 below and change line 52
:linenos:
:lineno-start: 47
:emphasize-lines: 5-6
def add_assessment(subject, details, start_date, due_date):
  global __assessments
  
  print("Writing assessment details to the database")
  user = the_user()
  anvil.server.call('add_assessment', user, subject, details, start_date, due_date)
  __assessments = None
  my_assessment()

:class: notice
- **line 51** → retrieves the cached user data
- **line 52** → adds the cached user data to the arguments passed to **add_assessment**

Testing add_assessment function

Launch your website and add a new assessment item.

Notice all the caching messages - think of the time you are saving.

get_assessment function

New we will use caching data in the get_assessment function.

  1. Open the assessment_service module and go to get_assessment
  2. Delete the anvil.users.get_user() call in line 20
  3. Add user as an argument to be passed - like the highlight below
:linenos:
:lineno-start: 18
:emphasize-lines: 2
@anvil.server.callable
def get_assessment(user):
  return app_tables.assessments.search(tables.order_by('due_date'),
                                      user=user,
                                      completed=False)

:class: notice
**line 19** → adds `user` as a value to be passed when the method is called

Now we have to change the call to my_assessment to include the user

  1. Open the data_access module
  2. Add line 39 below and change line 46
:linenos:
:lineno-start: 36
:emphasize-lines: 4, 11
def my_assessment():
  global __assessments

  user = the_user()
  
  if __assessments:
    print("Using cached assessments")
    return __assessments

  print("Accessing assessments from database")
  __assessments = anvil.server.call('get_assessment', user)
  return __assessments

:class: notice
- **line 39** → retrieves the cached user data
- **line 46** → adds the cached user data to the arguments passed to **get_assessment**

Testing get_assessment function

Launch your web app and check that pages that use assessment data:

  • all the assessments load on the Home page
  • the Calendar page

get_chart function

Finally the get_chart function:

  1. Open the assessment_service module and go to
  2. Delete the anvil.users.get_user() call in line 43
  3. Delete the app_tables.assessments.search call in lines 44 - 46
  4. Add assessments as arguments to be passed - like the highlight below
:linenos:
:lineno-start: 40
:emphasize-lines: 2
@anvil.server.callable
def get_chart(assessments):
    
    # Create a DataFrame from the assessments data
    data = []
    for assessment in assessments:
        # adjust for exams
        start_date = assessment['start_date']
        due_date = assessment['due_date']
                
        if start_date == due_date:
            due_date += pd.Timedelta(days=1)
      
        data.append({
            "Subject": assessment['subject'],
            "Details": assessment['details'],
            "Start": assessment['start_date'],
            "Due": due_date
        })
    
    df = pd.DataFrame(data)

:class: notice
**line 19** → adds `assessments` as a value to be passed when the method is called

Now we have to change the call to my_assessment to include the user

  1. Open the data_access module
  2. Add line 82 below and change line 83
:linenos:
:lineno-start: 74
:emphasize-lines: 9, 10
def get_chart():
  global __chart

  if __chart:
    print("Using cached chart")
    return __chart

  print("Building new chart from database")
  assessments = my_assessment()
  __chart = anvil.server.call('get_chart', assessments)
  return __chart

:class: notice
- **line 82** → retrieves the cached assessments data
- **line 83** → adds the cached user data to the arguments passed to **get_chart**

Testing get_chart function

Launch your website, and navigate to the Calendar page.

Final code state

By the end of this tutorial your code should be the same as below:

Final data_access

:linenos:
import anvil.server
import anvil.users
import anvil.tables as tables
import anvil.tables.query as q
from anvil.tables import app_tables

# cached values
__user = None
__assessments = None
__chart = None

def the_user():
  global __user

  if __user:
    print("Using cached user")
    return __user

  print("Accessing user from database")
  __user = anvil.users.get_user()
  return __user

def logout():
  global __user
  __user = None
  anvil.users.logout()

def update_user(first_name, last_name):
  global __user
  
  print("Writing user details to database")
  anvil.server.call('update_user', first_name, last_name)
  __user = None
  __user = the_user()

def my_assessment():
  global __assessments

  user = the_user()
  
  if __assessments:
    print("Using cached assessments")
    return __assessments

  print("Accessing assessments from database")
  __assessments = anvil.server.call('get_assessment', user)
  return __assessments

def add_assessment(subject, details, start_date, due_date):
  global __assessments
  
  print("Writing assessment details to the database")
  user = the_user()
  anvil.server.call('add_assessment', user, subject, details, start_date, due_date)
  __assessments = None
  my_assessment()

def update_assessment(assessment_id, subject, details, start_date, due_date, completed):
  global __assessments

  print("Updating assessment details on the database")
  anvil.server.call('update_assessment',
                    assessment_id,
                    subject,
                    details,
                    start_date,
                    due_date,
                    completed
                   )
  __assessments = None
  __chart = None
  my_assessment()

def get_chart():
  global __chart

  if __chart:
    print("Using cached chart")
    return __chart

  print("Building new chart from database")
  assessments = my_assessment()
  __chart = anvil.server.call('get_chart', assessments)
  return __chart

Final assessment_service

:linenos:
import anvil.users
import anvil.tables as tables
import anvil.tables.query as q
from anvil.tables import app_tables
import anvil.server
import plotly.express as px
import pandas as pd

@anvil.server.callable
def add_assessment(user, subject, details, start_date, due_date): 
  app_tables.assessments.add_row(user= user,
                                 subject= subject,
                                 details=details,
                                 start_date=start_date,
                                 due_date=due_date,
                                 completed=False)

@anvil.server.callable
def get_assessment(user):
  return app_tables.assessments.search(tables.order_by('due_date'),
                                      user=user,
                                      completed=False)

@anvil.server.callable
def update_assessment_completed(assessment_id, completed):
  assessment = app_tables.assessments.get_by_id(assessment_id)
  if assessment:
    assessment["completed"] = completed

@anvil.server.callable
def update_assessment(assessment_id, subject, details, start_date, due_date, completed):
    assessment = app_tables.assessments.get_by_id(assessment_id)
    if assessment:
        assessment['subject'] = subject
        assessment['details'] = details
        assessment['start_date'] = start_date
        assessment['due_date'] = due_date
        assessment['completed'] = completed

@anvil.server.callable
def get_chart(assessments):
    
    # Create a DataFrame from the assessments data
    data = []
    for assessment in assessments:
        # adjust for exams
        start_date = assessment['start_date']
        due_date = assessment['due_date']
                
        if start_date == due_date:
            due_date += pd.Timedelta(days=1)
      
        data.append({
            "Subject": assessment['subject'],
            "Details": assessment['details'],
            "Start": assessment['start_date'],
            "Due": due_date
        })
    
    df = pd.DataFrame(data)
    
    # Create the Gantt chart using Plotly
    fig = px.timeline(df, 
                      x_start="Start", 
                      x_end="Due", 
                      y="Subject", 
                      text="Details", 
                      title="Assessment Schedule"
                     )

    fig.update_yaxes(title_text="") 
    
    return fig