Query Taking nearly 30 times longer With Param

[DlrMngr]

Hi,



I currently have a queries that runs quite nicely and quickly when i enter the between dates within # tags and runs in approx 2 seconds which is exactly the functionality i need.

Works in 2 Seconds:

Between #12/09/2012# and #13/09/2012#

However, i need the versatility of users to be able to change and select the dates without having to change the hard code so i refered to using Parameters. Nothing fancy just a popup box asking for a value (will add it to form values once working)

The same query, the only thing changed is the between dates and it takes over 60 seconds as i had to increase the default ODBC timeout.

Between [Date To Start] and [Date To End]

i have even tried:

Between DateValue([Date To Start]) and DateValue([Date To End])

Can anyone assist with a quicker way to add this versatility without having to take so long its part of a monitor and 60 seconds of the Access app hanging just cannot happen as i will have to manually update the date everyday?

Regards,
DlrMngr

[June7]

Don't know why so much slower, would have to examine data. Does every record have a date value?

Personally, don't like and don't use parameter input popups in queries. Is this query to be the RecordSource for a form or report? I prefer the WHERE CONDITION argument of OpenForm/OpenReport method:

DoCmd.OpenReport "report name", , , "fieldname BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"

[DlrMngr]

Hi,

Thanks, the query is a record source however there are is a further query between this and the report which performs subqueries which is what the report runs off however i dont think you can send the above, through one query to the second query.

I am currently thinking about revisiting this and writing in vba to perform the queries with QueryDef try it this way as the length of time is too excessive.

The table i am reading from is a 12 month summary table, within each 15 minutes summary entry there could be a few hundred records, evidently this is a lot of data and i am wondering if it is pulling all the data and then applying the "paramters" rather than just executing as is?

Not sure though but its really annoying

[orange]

I agree with june7 that a Form on which you can select -- say Dates from a Calendar control - confirm the selection, then set up OpenArgs etc would give a standard approach and code could ensure the syntax is consistent.

If you have additional queries after the StartDate, Enddate selection --- suggest you put these on a form (may not be the same form) and again adjust record source or openargs behind the scenes, and run the "report/query whatever your procedure is" in a consistent manner.

You should be able to test these pieces to make sure things like indexes, sql structures are "quick' (efficient in your view) before going to production.

Just a few things to consider.

You may find some ideas at martin green's site
http://www.fontstuff.com/access/acctut19.htm

Good luck with your project.