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.
#####################################################################################
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.
#####################################################################################