December 15, 2011 at 9:06 am
I have a Bookings table as such:-
CREATE TABLE [dbo].[Bookings](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[Start] [smalldatetime] NOT NULL,
[Duration] [smallint] NOT NULL,
[Status] [varchar](50) NOT NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_Status] DEFAULT ('TBA') FOR [Status]
GO
I have the following data:-
insert into bookings (Start, Duration) values ('2011/12/15 10:00', 90)
insert into bookings (Start, Duration, Status) values ('2011/12/15 11:00', 120, 'Covered')
insert into bookings (Start, Duration, Status) values ('2011/12/16 09:00', 240, 'Cancelled')
insert into bookings (Start, Duration) values ('2011/12/17 12:00', 120)
insert into bookings (Start, Duration) values ('2011/12/18 15:00', 120)
I'm trying to find a way to send alert notifications (either email, sms, push notifications, etc) via my application, if one of my bookings is still TBA and has been on the system for longer than 90 minutes.
I would rather stay away from polling the database every minute or so as it degrades performance of my application as i have over a million bookings, so i'm looking for a more elegant solution.
I have been trying to make use of Query Notifications to monitor for changes in my Bookings table, which works to a certain extent, but i'm having issues working out how to notify when a TBA booking has been on the system for longer than 90 minutes.
So far my query is as below. The 2011/12/15 11:00 is the current time that my app dynamically passes to my query.
select Id
from dbo.Bookings
where Status = 'TBA'
and Start between convert(datetime, '2011/12/15 11:00', 20) and dateadd(d, 7, convert(datetime, '2011/12/15 11:00', 20))
and DateCreated < convert(datetime, '2011/12/15 09:30', 20)
order by Start
This does initially return TBA bookings for the next 7 days that were created over an hour and a half ago.
As bookings get created and amended via my app (bookings can be returned back to TBA), then as the date they get created is never going to be prior to the current date time that i pass, then i'll never receive notifications.
Can anyone think of a way to do this?
December 15, 2011 at 10:01 am
Did you try indexing your table for the columns in your query ?
As you are talking performance, with your current design, this query (and almost all queries for that matter) can only get its data by scanning the whole table.
Of course, that is not what you'll want.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 15, 2011 at 10:08 am
Why would you put your CLUSTERED index on the ID field of the table? You're not searching by ID, presumably, but by date or another field. You can do a combined clustered index for multiple columns to get the uniqueness you need for your table, can't you?
FYI: I don't advise calling your IDENTITY column just "ID". It will cause much confusion when someone tries to use the GUI to join tables and SSMS will auto-join on ID fields because they have the same name. Plus it's just not very descriptive in a column list. BookingID would be a better name.
December 15, 2011 at 10:09 am
I forgot to add... Have you looked at using Service Broker for your needs?
December 15, 2011 at 10:25 am
It sounds like what you really need is a related table that is a queue of Bookings in the TBA status, something like BookingTBAQueue.
Insert a row into the BookingTBAQueue table when you insert or update a booking with a status of TBA with a QueueDatetime column for the datetime it went into the table, and then delete them as you change the status. That should leave you with a very small table to scan and give you good performance.
December 15, 2011 at 4:48 pm
Brandie Tarvin (12/15/2011)
Why would you put your CLUSTERED index on the ID field of the table?
To prevent page splits on a massive table.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2011 at 11:42 pm
Jeff Moden (12/15/2011)
Brandie Tarvin (12/15/2011)
Why would you put your CLUSTERED index on the ID field of the table?To prevent page splits on a massive table.
My bet - and this is no offence at all - is the clustering thing is just caused because that's how MS implemented its behavior when assigning the primary key constraint, so not a specific choice by the designer.
The main reason to leave it that way for this case is the one Jeff stated.
However, because OP expects this table to hold a vast amount of rows, there should be payed more attention on that choice and other options to control I/O specific needs.
(filtered) indexes, partitioning, foreign key implementation, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2011 at 2:06 am
That's a great idea. I sort of had that theory in my head, but couldn't for the life of me think how to do it for looking too hard at it, so to speak.
I'll give it a go and let you know how i get on.
December 16, 2011 at 5:10 am
ALZDBA (12/15/2011)
Jeff Moden (12/15/2011)
Brandie Tarvin (12/15/2011)
Why would you put your CLUSTERED index on the ID field of the table?To prevent page splits on a massive table.
My bet - and this is no offence at all - is the clustering thing is just caused because that's how MS implemented its behavior when assigning the primary key constraint, so not a specific choice by the designer.
Which is exactly why I asked the question. Most schema devs don't even think to look at it. They just assume every PK should be clustered because That's The Way Things Are and it's a bad habit to get into.
December 16, 2011 at 7:50 am
dtwilliams (12/16/2011)
That's a great idea. I sort of had that theory in my head, but couldn't for the life of me think how to do it for looking too hard at it, so to speak.I'll give it a go and let you know how i get on.
What's a great idea? There have been a number of suggestions on this thread.
December 16, 2011 at 9:14 am
A BookingTBAQueue table. Seems to be doing what i need it too.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply