We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
999 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
Comment
Watch Question

CERTIFIED EXPERT
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.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.