Reduce Local Database Access

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

 9@anvil.server.callable
10def add_assessment(user, subject, details, start_date, due_date):
11  app_tables.assessments.add_row(user= user,
12                                 subject= subject,
13                                 details=details,
14                                 start_date=start_date,
15                                 due_date=due_date,
16                                 completed=False)

Code explaination

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

47def add_assessment(subject, details, start_date, due_date):
48  global __assessments
49  
50  print("Writing assessment details to the database")
51  user = the_user()
52  anvil.server.call('add_assessment', user, subject, details, start_date, due_date)
53  __assessments = None
54  my_assessment()

Code explaination

  • 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

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

Code explaination

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

36def my_assessment():
37  global __assessments
38
39  user = the_user()
40  
41  if __assessments:
42    print("Using cached assessments")
43    return __assessments
44
45  print("Accessing assessments from database")
46  __assessments = anvil.server.call('get_assessment', user)
47  return __assessments

Code explaination

  • 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

40@anvil.server.callable
41def get_chart(assessments):
42    
43    # Create a DataFrame from the assessments data
44    data = []
45    for assessment in assessments:
46        # adjust for exams
47        start_date = assessment['start_date']
48        due_date = assessment['due_date']
49                
50        if start_date == due_date:
51            due_date += pd.Timedelta(days=1)
52      
53        data.append({
54            "Subject": assessment['subject'],
55            "Details": assessment['details'],
56            "Start": assessment['start_date'],
57            "Due": due_date
58        })
59    
60    df = pd.DataFrame(data)

Code explaination

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

74def get_chart():
75  global __chart
76
77  if __chart:
78    print("Using cached chart")
79    return __chart
80
81  print("Building new chart from database")
82  assessments = my_assessment()
83  __chart = anvil.server.call('get_chart', assessments)
84  return __chart

Code explaination

  • 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

 1import anvil.server
 2import anvil.users
 3import anvil.tables as tables
 4import anvil.tables.query as q
 5from anvil.tables import app_tables
 6
 7# cached values
 8__user = None
 9__assessments = None
10__chart = None
11
12def the_user():
13  global __user
14
15  if __user:
16    print("Using cached user")
17    return __user
18
19  print("Accessing user from database")
20  __user = anvil.users.get_user()
21  return __user
22
23def logout():
24  global __user
25  __user = None
26  anvil.users.logout()
27
28def update_user(first_name, last_name):
29  global __user
30  
31  print("Writing user details to database")
32  anvil.server.call('update_user', first_name, last_name)
33  __user = None
34  __user = the_user()
35
36def my_assessment():
37  global __assessments
38
39  user = the_user()
40  
41  if __assessments:
42    print("Using cached assessments")
43    return __assessments
44
45  print("Accessing assessments from database")
46  __assessments = anvil.server.call('get_assessment', user)
47  return __assessments
48
49def add_assessment(subject, details, start_date, due_date):
50  global __assessments
51  
52  print("Writing assessment details to the database")
53  user = the_user()
54  anvil.server.call('add_assessment', user, subject, details, start_date, due_date)
55  __assessments = None
56  my_assessment()
57
58def update_assessment(assessment_id, subject, details, start_date, due_date, completed):
59  global __assessments
60
61  print("Updating assessment details on the database")
62  anvil.server.call('update_assessment',
63                    assessment_id,
64                    subject,
65                    details,
66                    start_date,
67                    due_date,
68                    completed
69                   )
70  __assessments = None
71  __chart = None
72  my_assessment()
73
74def get_chart():
75  global __chart
76
77  if __chart:
78    print("Using cached chart")
79    return __chart
80
81  print("Building new chart from database")
82  assessments = my_assessment()
83  __chart = anvil.server.call('get_chart', assessments)
84  return __chart

Final assessment_service

 1import anvil.users
 2import anvil.tables as tables
 3import anvil.tables.query as q
 4from anvil.tables import app_tables
 5import anvil.server
 6import plotly.express as px
 7import pandas as pd
 8
 9@anvil.server.callable
10def add_assessment(user, subject, details, start_date, due_date): 
11  app_tables.assessments.add_row(user= user,
12                                 subject= subject,
13                                 details=details,
14                                 start_date=start_date,
15                                 due_date=due_date,
16                                 completed=False)
17
18@anvil.server.callable
19def get_assessment(user):
20  return app_tables.assessments.search(tables.order_by('due_date'),
21                                      user=user,
22                                      completed=False)
23
24@anvil.server.callable
25def update_assessment_completed(assessment_id, completed):
26  assessment = app_tables.assessments.get_by_id(assessment_id)
27  if assessment:
28    assessment["completed"] = completed
29
30@anvil.server.callable
31def update_assessment(assessment_id, subject, details, start_date, due_date, completed):
32    assessment = app_tables.assessments.get_by_id(assessment_id)
33    if assessment:
34        assessment['subject'] = subject
35        assessment['details'] = details
36        assessment['start_date'] = start_date
37        assessment['due_date'] = due_date
38        assessment['completed'] = completed
39
40@anvil.server.callable
41def get_chart(assessments):
42    
43    # Create a DataFrame from the assessments data
44    data = []
45    for assessment in assessments:
46        # adjust for exams
47        start_date = assessment['start_date']
48        due_date = assessment['due_date']
49                
50        if start_date == due_date:
51            due_date += pd.Timedelta(days=1)
52      
53        data.append({
54            "Subject": assessment['subject'],
55            "Details": assessment['details'],
56            "Start": assessment['start_date'],
57            "Due": due_date
58        })
59    
60    df = pd.DataFrame(data)
61    
62    # Create the Gantt chart using Plotly
63    fig = px.timeline(df, 
64                      x_start="Start", 
65                      x_end="Due", 
66                      y="Subject", 
67                      text="Details", 
68                      title="Assessment Schedule"
69                     )
70
71    fig.update_yaxes(title_text="") 
72    
73    return fig