DayPilot Forums

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

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.

WHERE NOT is slower than WHERE for this query ?

My next application will be a Planning so it will be a performance issue.

Thanks :)

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.

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.

But start with setting indexes on both eventstart and eventend fields, that will speed things up.

Thank you very much for your answers.

How about using SQL BETWEEN?

i.e.

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

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

Any ideas?

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" />

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

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

Yes, exactly - before the DataBind() call.

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.