I enclose hereby a zip that contains the datamodel and some scripts.
The model is only a rough draft... But, I want to check if the query I wrote is fast enough for real world usage => many records.
The most imporant query is the one for weekly_Recur. If this one is fast enough, I copy the same structure for Monthly_Recur.
Please note that a Weekly event is further detailed by a DayList Table. Here can be indicated on which days of the week the event will recur.
The zip contains follwoing scripts :
- RecurEvents.sql => creates de tabloes and views
- FillRecurringPattern.Sql => for creating some records in the database
- query_RecurWeekly.sql for querying the database -> creates a cartesian product with all events propagated in the future.
- Count for filling up the Count table
- Calendar for filling up the calendar
My question : can the datamodel and query for using the weekly recurrent events be adjusted for speed? How can I best measure the speed for real world usage?
Any advice or tips are welcome.