[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-19
1
Medium Priority
?
987 Views
Last Modified: 2013-11-15
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
0
Comment
Question by:erwin_des
1 Comment
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 23693570
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question