Optimize Performance/Speed

[herrld]

My question concerns SQL Server, MS Access, and Crystal reports 8.5.
I usually create my crystal reports using an odbc connection to the SQL server database. This particular report contains so much data, I decided not to stress the server with it each time the user wants a report. Instead, I have linked my SQL server tables into access and created a few new tables. I append to these tables monthly so the data is as current as the last full month.
The question I have revolves around the best way to set up the crystal report for optimal speed.
Should I..
a) Create the query in Access and then report off it in Crystal.
b) Create a connection to the access database
(odbc, ole db, etc..) and bring the tables into the report that way.
c) Any other suggestions?? Linda

 

[synapsevampire]

Of the options, create the query in Access, though if possible, leave Access out of the equation, your SQL Server database will be MUCH faster and it won't blow up as often.

You could create the same summary tables in SQL Server and directly connect to those. Optimal speed should be recoginized by a Stored Procedure against the SQL database, though a View should still be faster than a Access database.

-k

 

[herrld]

I'm afraid I don't have the necessary permissions to create tables on the SQL server. bummer!

Thanks! Linda