My Database has 3 Large tables-
-VoucherHeader - Common for all vouchers
-VoucherDetailTypeA - Type A voucher detail
-VoucherDetailTypeB - Type B voucher detail
All entries go to Header, but Detail goes to TypeA or TypeB.
All Financial Years data is in these 3 tables.
Inserts are not causing any problem.
***The Ledger Report is causing problems. CPU 100%, RAM 100%
At Month End or Year End more than 100 users constantly open the ledger repeatedly for different accounts. All users view Ledger for same Financial Year and for a small date range.
Query - fetches large amount of data involves self join to show all other opposite accounts except the selected account for each voucher.
Hardware- Sufficient RAM 16 GB, Sufficient CPU 4 cores on a virtual Server
Indexing - Sufficient Indexes to speed up are there.
****Please suggest solution. Is partitioning a solution? but we have Standard edition & can't buy Enterprise edition. But since the report runs for same period so to me it seems partitioning might not help.
Aman
-------
Lets fly away to the Land where there is Love & Peace