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

Posted on 2009-02-19
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.
Question by:erwin_des
    1 Comment
    LVL 26

    Accepted Solution

    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

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now