Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Poor Performance
- Thread starter Bensta
- Start date
what are you using to read your data? is it impromptu or powerplay? for impromptu you might want to filter your data based on your requirements only to lessen the result set and for powerplay you might want to recreate your cube using fast cube access.
- Thread starter
- #3
I am using the admin impromptu version of cognos.
IT is very difficult to filter data based on requirements with respect to writing SQL's statements. I am stuck basically with the wizard. I don't know if I could wite my own sql clauses to speed up the process. Again the data source is an Informix database, in which records are processed and stored sequencially. My question is there any way I could speed up the process of generating reports? What if I link to Informix through SQL Server and use cognos as the front-end? Would this improve performances? I would appreciate all kinds of recommendations.
Thank you in advance
IT is very difficult to filter data based on requirements with respect to writing SQL's statements. I am stuck basically with the wizard. I don't know if I could wite my own sql clauses to speed up the process. Again the data source is an Informix database, in which records are processed and stored sequencially. My question is there any way I could speed up the process of generating reports? What if I link to Informix through SQL Server and use cognos as the front-end? Would this improve performances? I would appreciate all kinds of recommendations.
Thank you in advance
Bensta,
If you have SQL server, then you should notice a worthwhile speed improvement, so long as the time taken to 'load' SQL server doesn't exceed the time saving in impromptu. I'm testing SQL server as a small datawarehouse (datashed?) and it cuts a two hour read time to two minutes.
HTH,
lex
If you have SQL server, then you should notice a worthwhile speed improvement, so long as the time taken to 'load' SQL server doesn't exceed the time saving in impromptu. I'm testing SQL server as a small datawarehouse (datashed?) and it cuts a two hour read time to two minutes.
HTH,
lex
- Thread starter
- #5
Thank you drlex for yous inputs.
Is there an easier way I could link to informix database from SQL? I don't want to upgrade the Informix database for three reasons, which are I don;t have access to the tables, and I don't even know the structure of the informix database I am dealing with. And lastly from a copyright point of view and can only retrieve data from the Informix datbase.
Any ideas on what Ic an do?
Thank you again very much
Is there an easier way I could link to informix database from SQL? I don't want to upgrade the Informix database for three reasons, which are I don;t have access to the tables, and I don't even know the structure of the informix database I am dealing with. And lastly from a copyright point of view and can only retrieve data from the Informix datbase.
Any ideas on what Ic an do?
Thank you again very much
Bensta,
I'm afraid I don't have experience of Informix, but hopefully general principles will apply.
If your report is taking a long time to run, it suggests that your PC is having to read and sort a lot of data. Is it the case that your report is compiled from a lot of data, or do you believe that you should only have a small subset from the Informix DB? If it is the former, then I doubt that you can gain a great speed improvement. However, if it is the latter, then you need to look at what SQL is being passed to the DB for data extraction, to confirm that the DB server is doing all it can to reduce your PC load.
An example is a date filtered report. It might be that the date is not being passed in the SQL and hence all records are being returned and the filter being performed locally on the PC.
Another point to consider is join strategy (if more than one DB table is being referenced) Joins on non-indexed fields are slowerthan joins on indexed ones. Also the order of the joins can affect performance. Since you have no information regarding the structure of your DB, this could well be a problem. SQL server might be of assistance, but only if you were considering copying the tables you require from Informix to SQL server - what would be the time implication in doing so?
In Impromptu, select Report, then Query (Ctrl-Q) and then the Profile Tab. Click on the SQL radio button. How does the SQL compare to your filter and your data columns?
Apologies if the foregoing is a bit laboured.
lex
I'm afraid I don't have experience of Informix, but hopefully general principles will apply.
If your report is taking a long time to run, it suggests that your PC is having to read and sort a lot of data. Is it the case that your report is compiled from a lot of data, or do you believe that you should only have a small subset from the Informix DB? If it is the former, then I doubt that you can gain a great speed improvement. However, if it is the latter, then you need to look at what SQL is being passed to the DB for data extraction, to confirm that the DB server is doing all it can to reduce your PC load.
An example is a date filtered report. It might be that the date is not being passed in the SQL and hence all records are being returned and the filter being performed locally on the PC.
Another point to consider is join strategy (if more than one DB table is being referenced) Joins on non-indexed fields are slowerthan joins on indexed ones. Also the order of the joins can affect performance. Since you have no information regarding the structure of your DB, this could well be a problem. SQL server might be of assistance, but only if you were considering copying the tables you require from Informix to SQL server - what would be the time implication in doing so?
In Impromptu, select Report, then Query (Ctrl-Q) and then the Profile Tab. Click on the SQL radio button. How does the SQL compare to your filter and your data columns?
Apologies if the foregoing is a bit laboured.
lex
- Thread starter
- #7
Hi Drlex
Sorry for not replying sooner.
Let me first answer your questions. The implication to copying informix tables to SQL server will not be aproved by the management team. It would not even possible since the database dictionary is not available for that particular database application. There are a lot of limitions due to some beraucracy, unfortunately.
Anyway, I did check the sql under the profile tab, but had no idea as to evalute the expressions? It references the tables aliases, I assume, such as T1, T3, etc. Since I don't have a roadmape to the database it does not help me evalute the sql expressions. Unless you meant something else by your question?
I absolutely agree with you on the fact that the date is not passed in the sql,and as result, all records are processed locally. I have noticed that pretty much all of the reports inlcuded a date range in the filter expression.
How can I force the date well as all the records to be processed on the server as opposed locally? Is there any way to do that?
Thank you Drlex in advance
Sorry for not replying sooner.
Let me first answer your questions. The implication to copying informix tables to SQL server will not be aproved by the management team. It would not even possible since the database dictionary is not available for that particular database application. There are a lot of limitions due to some beraucracy, unfortunately.
Anyway, I did check the sql under the profile tab, but had no idea as to evalute the expressions? It references the tables aliases, I assume, such as T1, T3, etc. Since I don't have a roadmape to the database it does not help me evalute the sql expressions. Unless you meant something else by your question?
I absolutely agree with you on the fact that the date is not passed in the sql,and as result, all records are processed locally. I have noticed that pretty much all of the reports inlcuded a date range in the filter expression.
How can I force the date well as all the records to be processed on the server as opposed locally? Is there any way to do that?
Thank you Drlex in advance
Bensta,
this may be where your DB & hence impromptu's SQL construction may vary from mine (Progress). If there is a date in the impromptu report filter (eg '2003-08-18'), then it ought to show in the SQL. This is true for either type in (prompt) or hard-coded into the filter. If the impromptu function of TODAY or CURDATE is used in the filter, then (in my case certainly), the date filter does not get performed by the DB (even though TODAY is a DB function) and I end up with all the records being processed locally. This can turn a 2 second report into a 45 minute one (yes, I only have a Pentium III and 256MB RAM).
AFAIK, the table descriptions in Impromptu should relate to the DB table names or the view of the DB. Can you not try a simple report that references one table only with a date filter and then check the SQL?
HTH
lex
this may be where your DB & hence impromptu's SQL construction may vary from mine (Progress). If there is a date in the impromptu report filter (eg '2003-08-18'), then it ought to show in the SQL. This is true for either type in (prompt) or hard-coded into the filter. If the impromptu function of TODAY or CURDATE is used in the filter, then (in my case certainly), the date filter does not get performed by the DB (even though TODAY is a DB function) and I end up with all the records being processed locally. This can turn a 2 second report into a 45 minute one (yes, I only have a Pentium III and 256MB RAM).
AFAIK, the table descriptions in Impromptu should relate to the DB table names or the view of the DB. Can you not try a simple report that references one table only with a date filter and then check the SQL?
HTH
lex
Lakshmipathy
Programmer
Can I publich the Impromptu catalog remotely..I did the following steps:
1. Created the Shared directory for Reports which contains the imr report and the catalog file.
2. Mapped the Reports directory in Server and using Distribution Shuttle builder in server packaged the files.
3. Using Reports administrator published the Report.
4. When I check the status its giving following error:
"Publish - errors when publishing the report set." (Eng)
Is it possible to publish catalog remotely?.If yes how?.
1. Created the Shared directory for Reports which contains the imr report and the catalog file.
2. Mapped the Reports directory in Server and using Distribution Shuttle builder in server packaged the files.
3. Using Reports administrator published the Report.
4. When I check the status its giving following error:
"Publish - errors when publishing the report set." (Eng)
Is it possible to publish catalog remotely?.If yes how?.
- Status
Similar threads