Data Model for Recurring Events -> fine tuning for max. speed

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.



RecurEventst.zip
erwin_desAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
I have no time to go over all these scripts and objects but one think that might help is to create clustered primary keys on both Count and Calendar table.

Avoid using old style joins like
    from
          table1,table2
     where
         ...

especially if you don'y use = operator in where clause. You did that in GetWeekDays CTE in your query_RecurWeekly.txt script.

Other than that make sure you have indexes on all the columns you use in JOINS clauses and WHERE clauses and you should be fine. That query_RecurWeekly query maybe can be improved but is hard to do it without knowing what you're after.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.