Re: Multiple complex queries

[Malcolm Rook]

Thanks Rick

I was also coming to a similar conclusion and have tested it out. I wrote a stored procedure to produce a "summary" table for the period passing in the Start date/time and the period in seconds. I then queried the summary table for all entries.

Result: Instead of a 2 minute sequence of 360 queries I had the result set back in about 2 seconds.

FYI I always store any datetime fields as UTC without exception. I suffered in the past working on a system (not mine) which collected data as local time in 3 time zones across USA, processed the data at servers in South Africa with additional local time datestamps added, then reported in UK and USA. It was a total disaster until UTC was enforced with local time adjustment on reporting. Since then I refuse to have anything to do with non UTC datetime fields. Handling reporting over the shift to and from daylight saving, or summer time as we brits call it can be awkward but that problem is there whichever time base you start with.