>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.
What is execution plan?
MartinaJ