Posted by Brad on 10/01/82 11:20

Erland, Thanks for your response. I will try to answer your
questions.
I am new to MS SQL and may not be using the most efficient tools.
Up until recently, all my sql work was done through the Enterprise
Manager, but have just started working with the Query Analyzer.

We are using MS SQL 2000 on a Dell server that also has MS Access 2003
installed.
At this point everything we are doing is running on the one machine,
with the goal of opening the reporting aspect up to asp pages or an
Access front end.

We have several tables and views in MS SQL and have run many queries
and reports in MS Access against linked tables in the MS SQL database
through an ODBC Driver.

I have written a view (in MS SQL that appears below) that shows a
sample what we need against our 5 million record db. It groups on
several fields and filters on a criteria that I would like to be user
selectable.

The query works fine in MS SQL and seems to be fairly quick, but I
don't know how to allow people to select the IDNumber dynamically.
(There may be several ID numbers and other fields that will be part of
the ultimate criteria.)

When I use Access, I just create a form and have the query point to
the form control and get the criteria from there. I can do that here
also and have Access get the data from a linked MS SQL table or view,
but once I start trying to group and sort this quantity of data in MS
Access (even though it is only linking to the data in SQL) it slows
down dramatically and I don't know how to have Access link to this
view and dynamically insert the criteria.

I hope this explanation helps and you can provide me a little
guidance.

Thanks,
Brad

====================LISTING FROM QUERY ANALYZER
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.qry_report_rec00Grouped
AS
SELECT COUNT(dbo.tblmaster.recNumber) AS RecCount,
SUM(dbo.tblmaster.PmtAmount) AS SumPmtAmt,
dbo.tblmaster.Type,
dbo.tblmaster.State,
dbo.tblmaster.StatCode,
dbo.tblmaster.CTY,
dbo.qry_HCODE_LKUP.HCODE4
FROM dbo.tblmaster LEFT OUTER JOIN
dbo.qry_HCODE_LKUP ON dbo.tblmaster.recNumber =
dbo.qry_HCODE_LKUP.recNumber
WHERE (dbo.tblmaster.IDNumber = '123456')
GROUP BY dbo.tblmaster.StatCode, dbo.tblmaster.Type,
dbo.tblmaster.State,
dbo.tblmaster.CTY, dbo.qry_HCODE_LKUP.HCODE4

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
=====================END OF LISTING

Erland Sommarskog <esquel@sommarskog.se> wrote:

>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.

[Back to original message]