Database performance?

msuters

msuters

Active Member
Hi all,

We are trying to run a Trial Balance by business unit report (R09410),
but it takes a very long time, probably 9 hours. One aspect of this
seems to be that it tries to run the report over each of the 180,000
accounts in the account master table F0901, so there will probably be
180,000 selects involved in creating the final report.

My question is: is this a reasonable amount of time for this report, of
this size, to run? Can the long time taken to run the report be due to
inadequate database performance? And how should I determine the DB
performance?

For a select * statement on a table (or the same table several times)
suggests the DB returns 17,000 rows per second. But, is that adequate
performance for the DB? How does it compare with the performance other
people's DB servers achieve? (I realise it will be difficult to compare
systems, because of the different hardware, OS, DB servers, and
platforms. But regardless, I wonder what is the speed of data retrieval
that adequately supports other, live systems).

Just for interest, the table already has an index formed on the GMMCU
and GMOBJ columns that the select statement searches on. The actual
select statements used are below:

Initially, the report identifies the elements of Company 00105, which
the TB is for, by this statement:
SELECT * FROM TSDTA.F0901 WHERE ( ( GMFMOD <> 'M' AND GMFMOD <> 'C'
AND GMCO = '00105' ) AND GMLDA <= '8' ) ORDER BY GMCO ASC,GMMCU
ASC,GMOBJ ASC,GMSUB ASC

Then, a series of the following statement are used, where the GMOBJ is
changed to cycle through the values returned from the first query:
SELECT * FROM TSDTA.F0901 WHERE ( GMMCU = ' 100101' ) AND (
GMOBJ >= '1001 ' ) ORDER BY GMMCU ASC,GMOBJ ASC,GMSUB ASC

Thanks,
Mark Suters
ERP8U1, MSSQL2000, Windows 2000 SP3, Citrix XP

------------------------------------------------------------------------
---
Mark Suters - Senior Network and Applications Engineer
Stockland Group
P: 02 9561 2671 F: 02 9020 8323 M: 0417 318 245
E: [email protected]
------------------------------------------------------------------------
---
#####################################################################################
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege
is waived or lost by any mistransmission. If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender. You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. Stockland and any of its subsidiaries each reserve
the right to monitor all e-mail communications through its networks.

Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.

Thank You.
#####################################################################################
 
Mark,

Not being an application person, I can't say how long it should take from the top of my head, but since you have done quite a bit of research work already, can you give us some more details:
- what the timings from the jdedebug.log for those SQL statements are,
- are the jobs executed on the same box as your SQL Server and
- if you already looked at what indexes are being used?

Regards,
Alex.
 
Mark,

Have you run the logs through Performance Analyzer?? This tool,
available free from GSS on the KG will take the logs from this job (if it
runs for 9 hours, the logs may be huge, but that's OK) and point out
timing issues during the execution of the UBE. You'll be able to quickly
determine the SQL that is performing properly, as well as that SQL which
is not. In many cases, you find that there is an index that could be
created to improve performance.

There's also a white paper by Reeves Smith and Bill Calkins "SQL Server
2000 Tuning Guide" which outlines steps to look at for good performance.
When I perform SQL 2000 DB audits, I find that the following usually are
the main issues...

1. Memory is not optimized on the SQL Server...most don't know it, but
you have to force your SQL server to use more than 2GB of memory. If you
have 4GB or greater on the server, and you haven't turned on AWE, your
instance of SQL is only using 2GB.

2. Disk sets are not optimized for SQL...many are using RAID 5, or not
separating their data from the transaction log files onto separate disk
sets.

3. Indexes needed/missing, poorly written custom UBE's. Again,
Performance Analyzer is great for pinpointing these issues.

With the wonderful KG search feature acting as it does, if you can't find
the SQL Tuning Guide and need it, let me know, I'll e-mail it to you.

Regards and good luck,

Jim

________________________________________________________________
The best thing to hit the internet in years - Juno SpeedBand!
Surf the web up to FIVE TIMES FASTER!
Only $14.95/ month - visit www.juno.com to sign up today!
 
Back
Top