Speeding up a query that is currently running a dateadd calculation

[TaffyO]

Hi guys, found the answers I was looking for on the forum many times, but now time for a first post.



I have a database for a watersports company. One query works out which courses are running today for a display it outputs. It works fine for the start date alone but runs very slowly when I use the number of days added to the start date to find out if we are still running a course (as in this is day three of a four day course.)

SELECT [Courses Running].[Course Reference], [Courses Running].[Start Date], [Course Listing].[Duration (Days)], CDate(DateAdd("d",[Duration (Days)]-1,[Start Date])) AS FinishDate, [Course Listing].Course

WHERE ((([Courses Running].[Start Date])<=Date()) AND ((CDate(DateAdd("d",[Duration (Days)]-1,[Start Date])))>=Date()))

This does work very well - but very slowly as I guess it must be running a dateadd calculation on every line. Any suggestions for making this more efficient?

[June7]

How many records are involved? I have never known DateAdd to cause significant performance issue.

Where is the rest of the SQL statement? Oh well, probably not important.

Should not need the CDate() function. Day unit is default for adding/subtracting with date so don't really need DateAdd() function. So see if this is faster:

WHERE Date() BETWEEN [Start Date] AND [Start Date] + [Duration (Days)] - 1

[TaffyO]

There must be a few hundred records by now. It's on an external SQL server as opposed to a local table which probably accounts for the difference.

I will give that a try now.

[TaffyO]

That worked instantly but returned none of the records it was previously.

The full query now reads

SELECT [Courses Running].[Course Reference], [Courses Running].[Start Date], [Course Listing].[Duration (Days)], CDate(DateAdd("d",[Duration (Days)]-1,[Start Date])) AS FinishDate, [Course Listing].CourseFROM [Courses Running] INNER JOIN [Course Listing] ON [Courses Running].[Course Type] = [Course Listing].ID
WHERE Date() BETWEEN [Start Date] AND [Start Date] + [Duration (Days)] - 1
ORDER BY [Courses Running].[Start Date] DESC;

[June7]

Only a few hundred records? Really don't want to know how slow a few hundred thousand records would be!

Don't know why. Provide sample raw data. If you want to provide db, follow instructions at bottom of my post. Would have to import records into a local table.

[TaffyO]

Cool, so two table are relevant, "course listing" which lists the details of that generic course (duration, course title, cost etc) then the other table "courses running" provides specific information about the course (instructor, start date, time, notes etc.) Example data below

Course Reference	Title	Start Date	Time	Max Wind Force	Notes	Reminder	Filler	Post course	Cancelled	Course Type	Instructor
633		30/03/2016	09:00:00		James booked from cancelled September course.					39	17
634		04/04/2016	09:00:00							51	68

ID	Further Info	Three Letter Code	Number of spaces	Break Even	Price	VAT	Duration (Hours)	Course	Sport	Duration (Days)	Active Course
41	http://colwynbaywatersports.co.uk/pre-course-information/wta/	WTA	10	3	15	0	2	Windsurfing Taster	10	1	0
40	http://colwynbaywatersports.co.uk/pre-course-information/wsw/	WSW	6	2	150	0	16	RYA Start Windsurfing	11	2	0
39	http://colwynbaywatersports.co.uk/pre-course-information/wsi/	WSI	8	5	280	0	40	RYA Windsurfing Start Instructor	3	5	-1

(Course type in the first table is the ID in the second)

[June7]

What are table names?

ID field in bottom table is primary key field? It is saved as foreign key in Course Reference field of top table?

Your date values as displayed do not follow U.S./Access norm of mm/dd/yyyy. If you use international date structure, review: http://allenbrowne.com/ser-36.html

So you might need some function to manipulate date values - either your data or the system date.

[TaffyO]

What are table names?

ID field in bottom table is primary key field? It is saved as foreign key in Course Reference field of top table?

Your date values as displayed do not follow U.S./Access norm of mm/dd/yyyy. If you use international date structure, review: http://allenbrowne.com/ser-36.html

So you might need some function to manipulate date values - either your data or the system date.

ID is the primary key field, and is indexed as a foreign key on the server. It has indeed proved a pain using UK dates in access - you often have to convert back and forward to fix things. I will try messing with the date format now that I am not using dateadd that might well be the problem.

[TaffyO]

So further fiddling around reveals that the following

SELECT [Courses Running].[Course Reference], [Courses Running].[Start Date], [Course Listing].[Duration (Days)], [Start Date]+[Duration (Days)]-1 AS FinishDate, [Course Listing].CourseFROM [Courses Running] INNER JOIN [Course Listing] ON [Courses Running].[Course Type] = [Course Listing].ID
WHERE ((([Courses Running].[Start Date])<=Date()) AND (([Start Date]+[Duration (Days)]-1)>=Date()))
ORDER BY [Courses Running].[Start Date] DESC;

Works very well in terms of speed, however, the second where condition is not working - it's listing all future courses as well. If I add the cdate function to that it then works very well but goes back to working very slowly. Any variation on your suggestion, June7, seems to do the same - works quickly but then the conditionals don't work. You've definitely worked out the root cause - it's a date time issue for sure. As soon as I edit the start date though the system stops handling it as a date but then fixing that has huge performance problems.

[June7]

Then I suppose converting all date values to a string with Format() and doing string comparison will also have performance issues.

[TaffyO]

I hadn't really considered that. Can you elaborate on what you are suggesting? It's definitely the cdate function that's causing all the problems (works slowly with it, doesn't work without it,) so any way around this would be great.

[June7]

See Allen Browne's article about using Format() function with international date.

If you use Format() on both your data and the system date to produce strings with U.S. structure: mmddyyy (eg. 12312015, 04072014), maybe the conditional will work and performance won't be impaired. I do have my doubts. Aside from the speed issue caused by conversion, string comparisons are supposed to be slower than number comparisons. A date is really a number value.

[TaffyO]

Ah right - so it does make it slightly quicker - but it's still very slow. It was 6 seconds as opposed to 8. As no course is longer than a few days (I think the longest is five days) do you think it would be possible write it in a way that filters by say within ten days of the date before running the calculation?

[June7]

What calculation - FinishDate?

How would filtering for 10 day period be any different in performance than the filtering you are now doing? But try it. Remove the calculation from the SELECT clause. Apply your revised filter. Is it faster?

If so, build report based on that query and do the FinishDate calc in textbox.

[TaffyO]

Sorry you are definitely right - - I am grabbing at straws. I am just struggling to understand how a filter based on the date alone is very quick and a filter based on a very simple calculation of that date is so slow. Especially as it's not the calculation that is the slow bit - just converting back into a date/time.