I have a database which is connecting to a SQL server backend. At the moment it is generating reports very slowly e.g. 5-10mins. The tables on the SQL server seem to have already been indexed which I thought may be a solution. Is there anything else that I can try. Also I want to monitor the amount of data being transferred between the Access frontend and the SQL server, can anyone suggest any way to do this.

Thanks in advance

are there any sql command line tools that will check and compress the db?

The main table on the SQL Server has aroung 26,000,000 records stored on it.

Are there any software programs that would allow me to track the amount of data being transferred over the network.

You could de-normalize the back end if this is a commonly run report. For example, instead of using a running total on the report, keep a calculated attribute for the table. I wouldn't recommend this in many cases, but it might be a simpler and safer solution than trying to migrate large amounts of data just to run reports.

I dunno, just a suggestion.

chris.

I was thinking of creating a database maintenance plan to try and free up some space, but I am a bit wary in case it makes changes to the database which might cause data to be lost.

Another option I was considering was archiving some of the data in the largest table, I know this might solve the problem, but this might cause problems in the long term. If I did use this option can anyone advise me as to how to go about this, also is there any way to copy the data to be archived to another table before removing it from the orginal table as I want to see how may records will be removed before I change the orginal table.

Thanks

Write some stored procedures on your SQL server for the queries that are used often and only use the front end to call them. Large looping recordsets or large nested queries tend to run very slow when linking to SQL tables. There was a performance analyzer you used to be able to dl from MS, but I am sure there is something similar out there now. 26K records is nothing for SQL, it is more than likely your front end.

I still haven't found a solution to this. I have archived some of the records and reduced the transaction log but there has still been no improvement in the database performance.

I don't think it is going to be possible to create stored procedres on the sql server and link to them as some of the queries are creating and deleting temporary tables, also the frontend is developed by an external organisation.

I think that it is a problem with the frontend (as mentioned by Rockn), but I am not sure what I could try to speed it up. Are there any alternatives to using ODBC as I was wondering if this was causing the problems.
I have also tried running the database on the server using the ODBC connection and the ms access frontend but it still experienced the same problems.

Any advice or suggestions would be appreciated.

Thanks

Rockn, isn't it 26meg records?

ODBC is generally slow. What MDAC level and version of Access are you at? Ugrading to a newer version of MDAC will also update the Access ODBC drivers if they aren't already. Is it jsut the reports that are slow or are the queries running at the same speed? Try running your queries in the Query Analyzer directly in SQL to see if there is a speed difference between them and the queries directly from Access. Just some thoughts.