Speed

[Pauldohert]

Access XP and SQL Server

I have a form with many records and I am trying to bring accross only the record that are likely to be needed, by date.

I allow the user to select today,this week, this month, etc etc

I was hoping this was quicker than just opening the form and returning say 20,000 records, when today for example only returns 1 record.

I have tried 3 methods

1) having the query get the date parameters from global variables and a function

2) coding a rebuild of the query (ie deleting the old one and building the new)

3) changing the SQL of the existing query.

Unfortunately all 3 are slower than opening the form with all 20,000 records even when the date criteria narrows down the records to 1?

Timings

800 milliseconds plus for options 1- 3 but only 200 milliseconds to open the form with no criteria (these timings do not even count the time used to build the query)

If I reuse the query from options 2 or 3 ( ie not rebulding or changing the SQL) , I do then see a improvement in the speed to 200 milliseconds ,so it seems that the first time the query is run it runs far slower than it does susequently.


Simple question - how can I speed my form up by requesting less records - at the moment I am just making it slower?

Most pissed off - Paul, Thanks!


I have retimed it just using the queries and reusing 2, 3 is quicker than no criteria at all, but still the time to run the query first time is slow.

 

[Louie]

Have you tried using a stored procedure on SQL Server and executing a pass through query with argument parameters from Access? SQL Server will optimize the stored procedure.

 

[Pauldohert]

I need my form to be editable - that rules out a stored procedure doesn't it?

 

[Louie]

You can pass the value of variables from your form to the stored procedure via arguments. If you post something simular to the Select you need to execute, I might be able to help.

 

[Pauldohert]

Its simply a criteria of Between Date1 and Date2 on one of the fields - The query is fields from just one table.

I can pass parameters to a stored procedure using a pass thru query - I thought the result was non editable though which would be no use in this form?

Thanks Paul