DayPilot SQL Sample application

Asked by Anonymous
18 years ago.

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

asp.net

Comment posted by Dan Letecky
18 years ago.

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.

Any ideas?

Comment posted by Dan Letecky
18 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 John
17 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 Dan Letecky
17 years ago.

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

Yes, exactly - before the DataBind() call.

Comment posted by Dan Letecky
17 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 Ron Sell
15 years ago.

Need help! Want to post but keep getting crazy message saying code fragment must be XHTML 1.0 compliant!

Comment posted by Dan Letecky
15 years 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.

This question is more than 1 month old and has been closed. Please create a new question if you have anything to add.