Long-running queries

[jhoerr]

There are several queries that appear to run relatively frequently, take a long time, and involve data that is infrequently updated. These would be excellent candidates for caching.

1. Fetch all actions with matching BillId (~600ms). This data is updated every 10 minutes on weekdays between 8 AM and 8 PM.

(@P1393 nvarchar(4),@P1392 nvarchar(4), ..., @P2 nvarchar(1),@P1 nvarchar(1))
select * from [Action] 
where [Action].[BillId] in (@P1, @P2, ..., @P1392, @P1393) 
order by [Date] desc

2. Fetch all subjects with matching BillId (~550ms). This data is updated once daily on weekdays at 7:30 AM.

(@P1393 nvarchar(4),@P1392 nvarchar(4), ..., @P2 nvarchar(1),@P1 nvarchar(1))
select [Subject].*
  , [BillSubject].[BillId] as [pivot_BillId]
  , [BillSubject].[SubjectId] as [pivot_SubjectId] from [Subject] 
inner join [BillSubject] on [Subject].[Id] = [BillSubject].[SubjectId] 
where [BillSubject].[BillId] in (@P1, @P2, ..., @P1392, @P1393)
order by [Date] desc

3. Fetch all bills (~500ms). This data is updated once daily on weekdays at 7:30 AM.

select * from [Bill]

[jhoerr]

I think we have at least two options here:

1. Use Laravel's file cache to store query data. Upside: free. Downside: custom logic required to determine when to invalidate cache entries; high likelihood of stale data.

2. Use Laravel's Redis cache. Upside: No cache invalidation logic required; the Azure Functions can externally invalidate the cache entries whenever data is updated. Downside: nominal cost; maybe slower than file-based cache.

[jhoerr]

Some interesting thoughts on using Redis as a cache for JSON blobs. Conventional wisdom is to JSON stringify the entire set of query response data and store that as a single cache entry.

[jhoerr]

Caching/Invalidation plan of attack.

1. Get the views/components working such that we can effectively present all bills and search by bill name/keyword/committee/subject.
2. Cache whatever data is necessary to accomplish this.
3. Take note of the cache keys used, and what data is cached with each key.
4. Let the functions decide when it's appropriate to invalidate each key.

[jhoerr]

64ca3845bf1d169e743d2de939f86a63de95771d introduced caching data for bills and their related subject/committees. c141a585154948dfc6db33238a548b6ccb54a62a improved on that. These changes have dramatically improved responsiveness.

We now have a good mechanism for caching (via the web app) and invalidating the caches (via the functions) so I'll close this up.