Detect Record Conflict:

In a table or 'shifts' for employees i'm trying to test a proposed new shift
for conflicts, the employee's shifts must have 12 hours between them,
This makes sense to me but, is it going to be efficiant considering the
table could have ten's of thousands of shifts.
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
My basic concern is the use of the criteria on two seperate fields: START &
END is it possible to use indexes to maintain performance?
Kind regards,
Graeme.You can definitely increase performance by having, clustered index on
operatorid = and non clustered indedx on EndTime and Start Time
USE BETWEEN -AND so that the = is also taken into consideration
Regards
R.D
IF EXISTS
(
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid = @.NEW_OPID AND
(R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
R.StartTime < DATEADD(hh, 12, @.NEW_END))
)
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> In a table or 'shifts' for employees i'm trying to test a proposed new shi
ft
> for conflicts, the employee's shifts must have 12 hours between them,
> This makes sense to me but, is it going to be efficiant considering the
> table could have ten's of thousands of shifts.
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> My basic concern is the use of the criteria on two seperate fields: START
&
> END is it possible to use indexes to maintain performance?
>
> Kind regards,
> Graeme.
>
>|||Hi R.D.
thanks for your response.. does this make sense?
Assumeing I calculate 12h before the proposed shifts start as 'A' and
12 after the proposed shifts end as 'B'
Method 1:
if the DB looks for shifts with a START < B
and END > A [this is simple criteria but would it cause massive table
scaning to determine results, it woiuld also locate very long shifts (months
even) that conflicted]
it would have to check every shift that starts before B to see if the shift
ends after A would this be done using two (seperatoe) index ss [on two
differnt DB Fields, (START & END) and then find matchese.
Method 2:
I''m going to have to assume the shift can't be more than 12h (or say 24 to
be safe) and then use BETWEEN on START AND BETWEEN on END.
for instance:
... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
R.StartTime BETWEEN A-24 and B)....
Notice the 24 hours beeing added/subtracted to limit the window of matched
records. as I'm restricting the results by OperatorID i'm only going to
expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
CONFLICT, more than 1 conflicts should be very rare!
Is this sort of thing necessary or does the Query Optimizer do this
automaticly? somehow?
Thanks for you help so far,
Kind regards,
Graeme.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
> You can definitely increase performance by having, clustered index on
> operatorid = and non clustered indedx on EndTime and Start Time
> USE BETWEEN -AND so that the = is also taken into consideration
> Regards
> R.D
> IF EXISTS
> (
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid = @.NEW_OPID AND
> (R.EndTime > DATEADD(hh, -12, @.NEW_START) AND
> R.StartTime < DATEADD(hh, 12, @.NEW_END))
> )
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Graeme Stow
Query optimiser generally does everything if you have indexes. However the
final selection can be made only by analysing the execution plans. When you
have index on that, It would not choose Table scan.
Try different combination and select the best one using execution plan.
Thumb rule is thst execution plan should avoid( unless you are selecting
everything) table scan, book marks, filters and should use Index s or
clustered s.
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R.D.
> thanks for your response.. does this make sense?
> Assumeing I calculate 12h before the proposed shifts start as 'A' and
> 12 after the proposed shifts end as 'B'
> Method 1:
> if the DB looks for shifts with a START < B
> and END > A [this is simple criteria but would it cause massive table
> scaning to determine results, it woiuld also locate very long shifts (mont
hs
> even) that conflicted]
>
> it would have to check every shift that starts before B to see if the shif
t
> ends after A would this be done using two (seperatoe) index ss [on two
> differnt DB Fields, (START & END) and then find matchese.
> Method 2:
> I''m going to have to assume the shift can't be more than 12h (or say 24 t
o
> be safe) and then use BETWEEN on START AND BETWEEN on END.
> for instance:
> .... WHERE ... ( R.EndTime BETWEEN A and B+24 AND
> R.StartTime BETWEEN A-24 and B)....
> Notice the 24 hours beeing added/subtracted to limit the window of matched
> records. as I'm restricting the results by OperatorID i'm only going to
> expec that 90% of the time the DB will find no CONFLICTS and 10% on 1
> CONFLICT, more than 1 conflicts should be very rare!
>
> Is this sort of thing necessary or does the Query Optimizer do this
> automaticly? somehow?
> Thanks for you help so far,
> Kind regards,
> Graeme.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:0A5159C7-A1D2-4469-9417-84A9DDEDA3ED@.microsoft.com...
>
>|||Graeme Stow
I missed something in my first response. I think R.operatorid is not
unique. And If combination of ShiftID and operatorid is unique(should be)
try creating clustered primary index on this. And Non clustered index on
start and end dates.
Regards
R.D
SELECT R.ShiftID
FROM tblshiftrecords R
WHERE R.operatorid
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Graeme Stow
> Query optimiser generally does everything if you have indexes. However the
> final selection can be made only by analysing the execution plans. When yo
u
> have index on that, It would not choose Table scan.
> Try different combination and select the best one using execution plan.
> Thumb rule is thst execution plan should avoid( unless you are selecting
> everything) table scan, book marks, filters and should use Index s or
> clustered s.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Graeme Stow" wrote:
>|||Hi R,
After creating the following indexs:
create index tblShiftRecords_IX1
on tblShiftRecords (OperatorID, StartTime)
create index tblShiftRecords_IX2
on tblShiftRecords (EndTime)
Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
would it be better to have that as NON-CLUSTERED and have a more usefull
index as clustered like StartTime?
The folowing [testing] query
SELECT *
FROM tblshiftrecords
WHERE operatorid = 546 AND
StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh,
12, '20050516 17:00:00') AND
EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
36, '20050516 17:00:00')
Produces -
1)Index S - cost 0.003327 (98%)
2)Bookmark Lookup - 0.000070 (2%)
3)Filter - 0.000001 (0%)
It's looking better,
Cheers for your help.
Kind regards,
p.s. the query plan is ignoring IX2 on EndTime.
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
> Graeme Stow
> I missed something in my first response. I think R.operatorid is not
> unique. And If combination of ShiftID and operatorid is unique(should be)
> try creating clustered primary index on this. And Non clustered index on
> start and end dates.
> Regards
> R.D
> SELECT R.ShiftID
> FROM tblshiftrecords R
> WHERE R.operatorid
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>|||Graeme
Thats great. You can last do one thing.
have covering index. create individual non clustered indexes on operators
id, start time and end time and see you can eliminate book mark.
--
Regards
R.D
--Knowledge gets doubled when shared
"Graeme Stow" wrote:

> Hi R,
> After creating the following indexs:
> create index tblShiftRecords_IX1
> on tblShiftRecords (OperatorID, StartTime)
> create index tblShiftRecords_IX2
> on tblShiftRecords (EndTime)
>
> Note: i've already got a clustered index on ShiftID (It's IDENTITY(1,1)
> would it be better to have that as NON-CLUSTERED and have a more usefull
> index as clustered like StartTime?
> The folowing [testing] query
> SELECT *
> FROM tblshiftrecords
> WHERE operatorid = 546 AND
> StartTime BETWEEN DATEADD(hh, -24, '20050516 08:02:00') AND DATEADD(hh
,
> 12, '20050516 17:00:00') AND
> EndTime BETWEEN DATEADD(hh, -12, '20050516 08:02:00') AND DATEADD(hh,
> 36, '20050516 17:00:00')
> Produces -
> 1)Index S - cost 0.003327 (98%)
> 2)Bookmark Lookup - 0.000070 (2%)
> 3)Filter - 0.000001 (0%)
> It's looking better,
> Cheers for your help.
> Kind regards,
> p.s. the query plan is ignoring IX2 on EndTime.
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:28FFAED1-9F90-420E-900D-A5654AE5DCD4@.microsoft.com...
>
>