Posted by Erland Sommarskog on 07/01/05 00:29

Brad (bmeyynospamnospam@bmegroup.com) writes:
> I have an sql query that has specific criteria (like state='PA' or
> state = 'NJ'...) and would like to be able to have the user specify
> the criteria dynamically either through the web or from MSAccess or
> another tool.
>
> The query also does a GROUP BY the state and other variables that are
> part of the criteria.
>
> I know how to get MSAccess and asp pages to do the sorting and
> selecting against an SQL tbl or view, but when access queries the same
> info as the original sql view, the process takes much longer than when
> the sql view does all of the sorting, selecting and grouping..
>
> The table we are currently using is 5 million records and will be
> growing to 250 million records shortly, so speed is of the essence.
>
> The sql views and MSAccess are both running from the same server so
> there is no issue at this point of a network impacting the MSAccess
> query.
>
> Any suggestions...

I'm afraid that there was very little useful substance in your desciprion.
Which database engine are you using? The only one you mention, but somehow
I suspect that you don't plan to have a 250-million row table in Access.

Assuming that you use Access only as a frontend, and MS SQL Server as
the data store, I still can't tell from your description whether problem
is that there is one single query which is running slowly, or if
Access is issuing many queries. If you don't know this yourself, I would
suggest that you use the Profiler to see what SQL Server gets to work
with.

If you want suggestions to improve the query, I would suggest that you
post the query and CREATE TABLE and CREATE INDEX statements for the
table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]