redesign data access to make it run faster

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a one page report that has its Record Source point to a select
statement that joins five sql2k linked tables.
The performance of the report became an issue recently. I am thinking re-do
the data access may help.
This is access 97. I could make an ADO.command calls a sql stored procedure
to return a recordset.
Does Access report bind to a recordset directly as its "Record Source", or I
have to map field by field manually?
Am I barking on the right tree? On the data access, what is the most
efficient way?
Thanks!
 
A97 can't bind a report to an ADO recordset.

And you probably don't want to do that anyway.

Perhaps you could create a View in SQL Server of the
5 joined tables. Then you could link to the view, just
like you link to tables. Then you could use the view
as the report recordsource.

In A97, views are not normally updateable (you can't
edit the recordset), but you don't need that for a report
anyway.

If you have been using "Inner Joins", I doubt if it will
make much difference. If you have been using "Left:"
or "Right" joins, you may well see an improvement.

(david)
 
Thanks a lot.
Then I will need a parameterized view. I will create a view for everything,
then on access side, pass in a parameter when I make the call. Is it viable?
I need to find an example. If you can post something to get me start on that
direction, that will be great!
 
I'm not sure about the use of parameterised views
with reports. If you want to ask specifically about
that, probably you should start a new thread.

But if you just have 5 'joined' tables, there is
no need to use a parameterised view. Just create
a normal view, and select against that: For example:

Select * from myview where idx = 5

Or use a filter/where clause on the report.


Filters and criteria are passed back up to the server
for evaluation anyway. You don't have to create your
own parameterised query to get that behaviour.

(david)
 
Back
Top