I got a strange idea, i dont know whether it will work or not. I am working on single table has too many columns and i generated a report but its taking long time to execute the report and thought because of too many columns its taking long time. My question is if we reduce the columns( i mean the coulmns which are required for reports only) and create another table which includes only these columns and load the table and then generate the report, will performance increases or not?
In my experience, if you are querying from a single table, it’s rarely the number of columns that is the problem. That would only be a problem if you are performing many functions on each column, like character functions (e.g., SubStr()) or mathematical functions.
If you have only one table and are not performing many functions, then I would look at the database or network to see what might be slowing down your report.
There are 2 things that contribute to the slow response of the report:
the time database needs to process the query used in the report
the number of rows that are fetched into the report
re 1) Check with your DB admin whether the query/queries you use in the report are optimized - it means whether all necessary indexes are used, whether the table should be partitioned (if it’s possible in your DB), … many other things that your DB admin should be able to help you with
re 2) Try to avoid loading too many rows into the report. Have the DB to do all processing and bring as fewer records into the report as possible.
Actually it might impact performance as less rows fit in a so called DB page, DBMS typically read so called pages. The page size needs to be adequate.
There are so many factors to consider for performance, the first bet would be as already suggested to make sure DB indexes have been created, the DB statistics are up to date, and checking if your query can make use of the DB indexes (check via DB explain plan, etc.).
See also this extensive FAQ entry regarding report perfoemance, an oldie but true 24k goldie
That is true, page size might be the problem. But I am assuming that if the query is only reading from one table, adequate indexes have been created on the one table. If so, then probably it is something like character manipulations or other functions that are creating the slow response.
Obviously, working with one table you may be doing multiple passes of that table in your where clause, it certainly sounds that you are not dealing with any kind of designed reporting schema.
we have indexes the columns for where clause conditions and performing sum calculation only one measure column and still its taking about 3 min to execute the query for 5 million of records. How to tackle this situation? I am creating one more table which includes only columns which are used for reports and build indexes on those columns and try to execute the query. Will that help me to increase the performance? Any suggestions plz?
It’s good to have indexes but it’s better if a query uses them
So analyze your queries with the DBA and check whether any index or indexes are used. Check also the explain plan which execution path the DB optimizer chose to process the query.
To answer the earlier question: yes, a narrower table will perform better than a wide table because of the amount of I/O. That is something that has been drilled into my head by our Teradata DBA team. Results may be different for other databases, but their reasons seem like they would be appropriate for other databases as well.
Having multiple indexes on the table doesn’t help if it’s just one table. Most optimizers will only use one index per table. Having only one large table would mean that you only get one index.
(BOB member since 2002-06-20)
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)
(BOB member since 2005-01-06)