Home Unanswered Active Tags New Question

DayPilot SQL Sample application

Hello,

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 ?

Thanks
Asked by Anonymous 4 years ago.
asp.net
Replies
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 Dan Letecky 4 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 :)
Comment posted by Anonymous 4 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.
Answer posted by Dan Letecky 4 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 4 years ago.
But start with setting indexes on both eventstart and eventend fields, that will speed things up.
Comment posted by Dan Letecky 4 years ago.
Thank you very much for your answers.
Comment posted by Anonymous 4 years ago.

How about using SQL BETWEEN?

i.e.

WHERE
(EventFrom BETWEEN @startDate and @endDate)
OR
(EventToBETWEEN @startDate and @endDate)
)

Comment posted by John 3 years ago.

What I'm not sure of, is how to update the Parameters of the ASP SQLdataSource I use to populate the DayPilotSchedule.

Any ideas?

Comment posted by John 3 years ago.
1. You can set the parameters from the code:
SqlDataSourceEvents.SelectParameters["start"].DefaultValue = DateTime.Today.ToString("s");
2. You can also declare the source in the .aspx:
<asp:ControlParameter Name="start" ControlID="DayPilotCalendar1" PropertyName="StartDate" />

Comment posted by Dan Letecky 3 years ago.

Where would you put this?

Would it go everywhere you call DayPilotScheduler1.DataBind() ?

For example

Private Sub DayPilotScheduler1_Refresh(ByVal sender As Object, ByVal e As DayPilot.Web.Ui.Events.RefreshEventArgs) Handles DayPilotScheduler1.Refresh

'here?
DayPilotScheduler1.DataBind()
DayPilotScheduler1.Update()
End Sub


Comment posted by John 3 years ago.

> Would it go everywhere you call DayPilotScheduler1.DataBind() ?

Yes, exactly - before the DataBind() call.

Comment posted by Dan Letecky 2 years ago.

See also this thread for an alternative SQL SELECT:

forums.daypilot.org/Topic.aspx/69/testing_for_overlapping_events_with_sql

It's called "testing for overlapping events" but it's the same logic.

Comment posted by Dan Letecky 2 years ago.
Need help! Want to post but keep getting crazy message saying code fragment must be XHTML 1.0 compliant!
Comment posted by Ron Sell 1 year ago.

Ron, you need to paste your code in the WYSIWYG mode, not in the source (HTML) mode.

You can also send it to support@daypilot.org.

Comment posted by Dan Letecky 1 year ago.
New Reply
This reply is
Your name (optional):

DayPilot for ASP.NET WebForms, DayPilot for ASP.NET MVC, DayPilot for Java