In your sample there is this SQL string :
SELECT [id]....[allday] FROM [event] WHERE NOT ([eventstart]<=@start OR [eventend] >= (@end))
Is is better for performances to have a select from hours in a different field (date field and hour field) or a full date field ?
Also, why not use a 'WHERE >= <=' statement instead of WHERE NOT ?
I guess there will be no difference in performance between using separated or joint date and time fields. DayPilot needs it in one field so you might loose some nanoseconds when joining them during the select but you won't notice.
It's possible to rewrite the condition like this:
WHERE ([eventstart] >= @start AND [eventstart] <= @end) OR ([eventend] >= @start AND [eventend] <= @end)
This one is probably easier to understand but much longer.
Comment posted by Anonymous 18 years ago.
WHERE NOT is slower than WHERE for this query ?
My next application will be a Planning so it will be a performance issue.
Thanks :)
Answer posted by Dan Letecky 18 years ago.
But now I see that the original code is not quite right, it should read like this:
WHERE NOT (([eventend <= @start) OR ([eventstart] >= @end))
and the alternative should read like this:
WHERE ([eventstart] >= @start AND [eventstart] < @end) OR ([eventend] > @start AND [eventend] <= @end)
I'll fix it in the demo.
Comment posted by Dan Letecky 18 years ago.
The query will go through the SQL Server optimizer first anyway so I'm sure both options will execute with the same speed.
I wouldn't lose much time with the SQL query until the resulting speed is unsatisfactory.
Comment posted by Dan Letecky 18 years ago.
But start with setting indexes on both eventstart and eventend fields, that will speed things up.
Comment posted by Anonymous 18 years ago.
Thank you very much for your answers.
Comment posted by John 18 years ago.
How about using SQL BETWEEN?
i.e.
WHERE
(EventFrom BETWEEN @startDate and @endDate)
OR
(EventToBETWEEN @startDate and @endDate)
)
Comment posted by John 18 years ago.
What I'm not sure of, is how to update the Parameters of the ASP SQLdataSource I use to populate the DayPilotSchedule.
DayPilot