Report speed affected by server speed?

[lbass]

I'm not sure about my "hardware" language here, or whether this is the right forum, but...

Until recently I had a 56k connection to our Oracle database through a WAN (I guess--the database is housed in another city). Another PC acts as the server to my PC, and I am the only user at this location. I'm pretty sure the driver is the Crystal ODBC driver, and I have a file.dsn connection.

The database has several million records and hundreds of tables. For many reports I use 5-10 tables, and needless to say, my reports crawled (sometimes for hours!), so I decided to upgrade to a 384k line, expecting my reports to become significantly faster. However, they are not. Using a test report with one table and minimal criteria which draws 120,000 records, and having other people run the same report simultaneously at other locations, I found that my report ran between 6 and 10 times slower, even though the other lines were 384k also and had multiple users. Here's my question:

The PC/server is 200 MHz with 16 MB of RAM and my computer is 733 MHz with 128 MB of RAM. Could either the server or both of these be slowing my reports?

Secondly, I noticed that in the screen "Database-Set Location" the userid showing belongs to a different organization at another location, although before the reconfiguration, it identified my location. Could this mean the configuration is wrong?

This might sound silly, but I'm not sure why I need a local server at all. We used to have canned reports that were downloaded from the database to the local servers, but this is no longer the case. When I'm accessing data, it's being drawn from the central database.

Sorry for the length here, and thanks for any insights...

-LB

 

[dgillz]

I would write a stored procedure to get the desired data, then make the SP the datasource for Crystal rather than querying the database directly with Crystal. Many times this can be much faster. Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[lbass]

Thanks for responding. I should have mentioned that users don't have the necessary permissions to create stored procedures. I know the size of the database is a factor in report speed, but I'm wondering why my speed is so much slower than others even when running the same report.

-LB

 

[synapsevampire]

I've no idea why you are using a server either. Perhaps it's a Unix box, given the minimal resources, I hope so.

Are you certain that you're actually getting a 384K connection? It doesn't sound like it.

Pulling 120K rows is a very large report through a 56K pipe, perhaps you would be better served to set up a local database (Oracle, SQL Server or Access) and have data downloaded on a scheduled basis to meet the granularity of the reporting requirements, and then report off of that.

You can download freebie personal/developer editions of both Oracle and SQL Server (although I think they all time out in 120-180 days, so you'd need a long term strategy eventually).

I try to always set up a separate database for reporting (snapshot), which eliminates the burden your reports might place on the database, and it allows you to use stored procedures, create a data warehouse or at least some summary tables to allow for faster reporting.

-k

 

[lbass]

SynapseVampire,

There is a router in my office connecting to the server. I don't know the specifics of the router, but the PC/server is a NEC PowerMate Pro2200. The central help desk tested the new 384k line, and they say the "ping" test shows appropriate speed, but I wasn't sure that it was actually measuring all the way to my computer, or just to the router. They also said that this PC/server should be sufficiently fast, and I guess I'm asking the question here because I have trouble believing that this isn't the problem (they supply the server)! There must be some reason that other people in the network can run the same report so much faster. The same report run centrally (without going over the WAN) was 25 times faster, and someone at the other end of the state ran it between six and 10 times faster.

Does it matter if my 384k is converging on the central database with other lines on the same "route"? I guess I don't think this could be it either, since the test report was still slow in off-hours, when usage is virtually nil.

Your suggestion about downloading data and creating a local database is a good one. A colleague uses Crystal to download daily into Access, and then she reports off that. The problem is that I don't think I have the sophistication to make this solution work. Or the time--reporting/database work is not my primary job--but maybe I'll have to take the time to learn.

Sorry for blathering...

-LB