Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tstuart
Starting Member
2 Posts |
 Posted - 2007-03-21 : 09:46:39
|
| A query that runs in a second or so in Query Analyzer requires 20 seconds in a linked Access Project.What's the secret of MS_Access poor performance, and can it be improved?Tom Stuart |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tstuart
Starting Member
2 Posts |
Posted - 2007-03-21 : 13:38:29
|
quote:
And why aren't you using stored proceduresAnd, are all you controls bound?
Thanks for stepping up. I use simple queries when they will suffice because the staff I serve is familiar with them and can modify them.However, I would be grateful for a comparative assessment of the appropriateness of stored procedures.No forms involved, which is what I assume you mean with the reference to bound controls.Here is the very simple SQL: quote:
SELECT CASNumber, MAX(ObservedResult) AS [VP Calculated]FROM dbo.AnalyticalResultsWHERE (AnalyticalProcedure = N'VPC')GROUP BY CASNumber
Several similar (differing only in the constant, N'VPC') queries always complete promptly, whether from the Access ADP or from Query Analyzer, while this one takes 20 seconds about 90% of the time and 1 second 10% of the time. I just picked up this latter variance today. The traffic on the SQL Server 2000 is minimal. The Access version is 2003. |
 |
|
|
|
|
|
Query Execution Time