Our oracle database is large and it takes a long time to access the view used by my Crystal report even if I want to get only a couple of records. The view itself contains millions of records and I assume the view is populated only when I read it. The selection changes (date range) every time it runs so I do not want to select by a fixed date in the view. The date range for the report is a parameter in Crystal. Can I pass it to the view somehow? Or, is there some other way to speed up the process.
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!
Accessing a oracle view takes too long
- Thread starter dlmlmyers
- Start date
satinsilhouette
Instructor
- Feb 21, 2006
- 625
It sounds like all the processing is occurring on the local machine, which means it is bringing back the millions of records to the report, then filtering out.
You can handle this one of several ways. The best way would, if you are on 10 or XI is to take the current query from the show sql query, copy it and then put it in the sql command. Then apply a filter through there.
You could also be correct in saying that the view is an on demand view. If so, and you access this view on a regular basis, I would ask the dba to make it a view that is refreshed before anyone gets into work and accesses, that way the view is run for that day and the query sent back by Crystal no longer triggers the view to run at runtime.
The combination of both of those will optimize your report considerably.
Last tip, in the show sql query box, if you don't see a where clause with the filters from the select expert for records, this will cause everything to come back to the report, wherever it is located and process there. Making a report that might take less than 5 minutes, now a report that could take an hour or more.
You can handle this one of several ways. The best way would, if you are on 10 or XI is to take the current query from the show sql query, copy it and then put it in the sql command. Then apply a filter through there.
You could also be correct in saying that the view is an on demand view. If so, and you access this view on a regular basis, I would ask the dba to make it a view that is refreshed before anyone gets into work and accesses, that way the view is run for that day and the query sent back by Crystal no longer triggers the view to run at runtime.
The combination of both of those will optimize your report considerably.
Last tip, in the show sql query box, if you don't see a where clause with the filters from the select expert for records, this will cause everything to come back to the report, wherever it is located and process there. Making a report that might take less than 5 minutes, now a report that could take an hour or more.
- Thread starter
- #3
Thanks satinsilhouette,
Starting from the end: I checked the sql query and it does have the correct filters. So, I don't think that is the problem.
I checked with the dba (who is relatively new to this, as am I) and we are checking into materialized views to make this happen. Hopefully, it will solve my problem.
As to your first suggestion, I am afraid I didn't understand how to put the query into the sql command. I don't know where to go to do this. I can find the query and grab the copy -- then what?
Starting from the end: I checked the sql query and it does have the correct filters. So, I don't think that is the problem.
I checked with the dba (who is relatively new to this, as am I) and we are checking into materialized views to make this happen. Hopefully, it will solve my problem.
As to your first suggestion, I am afraid I didn't understand how to put the query into the sql command. I don't know where to go to do this. I can find the query and grab the copy -- then what?
synapsevampire
Programmer
I suggest that you post in the appropriate Oracle forum as this is an Oracle tuning issue, not Crystal.
Youe dba should be able to check the execution plan and determine if there are full table scans going on, and build the appropriate indexes.
You might also consider going to a stored procedure as it should prove the fastest.
-k
Youe dba should be able to check the execution plan and determine if there are full table scans going on, and build the appropriate indexes.
You might also consider going to a stored procedure as it should prove the fastest.
-k
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question