Report
- Thread starter lemara
- Start date
First of all : 6 million records is a lot. Don't forget that.
But you should really check the indexes that are defined on that particular table. If you're query doesn't use an optimized index for that query, you are already loosing time.
Maybe you can compile your program with xref option. Look for 'whole-index' clauses in the resulting xref file...
Don't know how you're report is build : but for example displaying a counter while you're query is running takes also a lot of time. If it's really necessary to display a counter while running, try to count in blocks : every 10000 records, display the counter, else : do nothing.
What is the output format of you're query. Excel? Other tool?
But you should really check the indexes that are defined on that particular table. If you're query doesn't use an optimized index for that query, you are already loosing time.
Maybe you can compile your program with xref option. Look for 'whole-index' clauses in the resulting xref file...
Don't know how you're report is build : but for example displaying a counter while you're query is running takes also a lot of time. If it's really necessary to display a counter while running, try to count in blocks : every 10000 records, display the counter, else : do nothing.
What is the output format of you're query. Excel? Other tool?
just a few more question:
how many records or lines do you expect the query to return ?
it'd be nice to know more about the query and the tables/indices involved ?
is it a product or homegrown database ?
even if the table has millions of records you're right to expect an immediate response for small queries.
if we'll get enough information we may even be able to rewrite it.
how many records or lines do you expect the query to return ?
it'd be nice to know more about the query and the tables/indices involved ?
is it a product or homegrown database ?
even if the table has millions of records you're right to expect an immediate response for small queries.
if we'll get enough information we may even be able to rewrite it.
Hi,
I have checked the indeces, the query is optimised. I have also done the xref to check which index query is using and any "whole-index"..everything seems to be ok.
This is actually for a call center and everytime clients/users call a record is created.
The table is called "call_hist", we have primary index i.e "idx_call". Fields in the index are : 1. call_date 2. call_sys.
We have many switches(hardware) to record calls and "call_sys" identifies the switch.
Its outputting to excel and roughly around 3-5 lakhs per switch. This is a homegrown db.
I have checked the indeces, the query is optimised. I have also done the xref to check which index query is using and any "whole-index"..everything seems to be ok.
This is actually for a call center and everytime clients/users call a record is created.
The table is called "call_hist", we have primary index i.e "idx_call". Fields in the index are : 1. call_date 2. call_sys.
We have many switches(hardware) to record calls and "call_sys" identifies the switch.
Its outputting to excel and roughly around 3-5 lakhs per switch. This is a homegrown db.
could be a little more complicated then "whole-index or not.
and the indices used may be misleading. in some cases not all the index fields you think are used actually are.
another important thing to watch for is that the criteria in the query may be dynamic and the indices may not be suitable for every case.
how about sharing the xref and maybe a simplified form of the query.
the question still stands, how many lines do you expect the query to return ?
if it's small, let's say in the thousands, then finding the most substantial index bracket/s could make a huge difference.
if it's big, let's say hundreds of thousands, finding the most efficient way to traverse the records, like, non-unique indices could have the biggest impact.
of course things could be a little more complicated ;-)
is that the only index ?
or better yet office xml schemas
which will also be a good start to open xml, office upcoming default format.