I'm really at a loss as to how to procede. I have a very large database, and the table I'm accessing has approx. 600,000 records. This database is accessed using an accounting application, which provides the report with the SQL query by which this report accesses the database.
我真的不知道如何处理。我有一个非常大的数据库,我正在访问的表有大约。 600,000条记录。使用记帐应用程序访问此数据库,该应用程序为报告提供此报告访问数据库的SQL查询。
My report has a linked subreport which has restrictions that are placed in the report header. When this report is run, the average time to refresh, using a very base query is 36 minutes. When adding two more items to the query, the report takes 2.5 hours.
我的报告有一个链接的子报表,其中包含放置在报表标题中的限制。运行此报告时,使用基本查询刷新的平均时间为36分钟。在查询中再添加两个项目时,报告需要2.5小时。
Here is what I've tried:
这是我尝试过的:
- cleaned up the report only leaving items in absolutely necessary - no difference
- removed most formulas (removing the remaining formulas makes no time difference)
- tried editing the SQL query - wasn't allowed because of the accounting application
- tried flipping subreport and main report - didn't work
- added other groupings - no difference
- removed groupings - no difference
- checked all the servers for lack of temp disc space - no issue
- tried "on demand" subreport - no change
- checked Parameters (discrete vs. range) and it is as it should be
- tried bursting indexes, grouping on server, etc. - no difference
- the report requires 2 passes. I've tried getting it down to one pass unsuccessfully.
清理报告只留下绝对必要的项目 - 没有区别
删除了大多数公式(删除剩余的公式没有时间差)
尝试编辑SQL查询 - 由于会计应用程序而不允许
试图翻转子报告和主要报告 - 没有用
添加其他分组 - 没有区别
删除分组 - 没有区别
检查所有服务器是否缺少临时磁盘空间 - 没问题
尝试“按需”子报告 - 没有变化
检查参数(离散与范围),它应该是它
尝试了爆破索引,在服务器上分组等等 - 没有区别
该报告需要2次通过。我试过把它降到一次传球失败了。
There must be something I'm missing.
必须有一些我想念的东西。
There does not appear to be any other modifications to the report using regular crystal functions. Is there any way to speed up the accessing of the data without having to go through all 600,000 records? The SQL query that accesses this data is long and has many requests. It is not something I can change.
使用常规晶体功能似乎没有对报告进行任何其他修改。有没有办法加快数据的访问速度,而无需通过所有600,000条记录?访问此数据的SQL查询很长并且有很多请求。这不是我可以改变的。
Can I add something (formula?) that nullifies these requests? I'm reaching now...
我可以添加一些(公式?)来取消这些请求吗?我现在到达了......
4 个解决方案
#1
Couple of things we have had success with is adding indexes to the databases, and instead of importing tables into the report, we instead wrote a stored procedure to retrieve the desired results.
我们取得成功的一些事情是向数据库添加索引,而不是将表导入报表,而是编写了一个存储过程来检索所需的结果。
#2
If indices and stored procedures dont get you where you need to be you have reached the denormalise until it works part of life with a database. You might want to look at creating an MI database with tables optimized for your reporting needs; and some data transformation scripts that can extract the data from production to your MI database. Depending on what it is oracle / ms have tools to help you do this.
如果索引和存储过程没有得到你需要的位置,那么你已经达到了反规范,直到它与数据库一起工作。您可能希望创建一个MI数据库,其中包含针对您的报告需求而优化的表;以及一些数据转换脚本,可以将数据从生产中提取到MI数据库。取决于它是什么oracle / ms有工具来帮助你这样做。
#3
We use Crystal Reports with a billing system, and we had queries in the database that take over 1.5 hours to complete. This doesn't even take into account the rendering/formatting of the reports.
我们将Crystal Reports与计费系统一起使用,我们在数据库中查询需要1.5小时才能完成。这甚至没有考虑报告的呈现/格式。
We created Materialized Views and force the client to refresh them daily. A materialized view is basically a database view that holds the returned dataset. The dataset is not refreshed unless you explicitly tell it to refresh.
我们创建了物化视图并强制客户端每天刷新它们。物化视图基本上是一个包含返回数据集的数据库视图。除非您明确告知数据集刷新,否则不会刷新数据集。
#4
Do you know what the SQL query is? If so, you can move the report outside the accounting application and paste the query directly into the Command in the database expert. I've had to do this in a couple of cases with another application I work with.
你知道SQL查询是什么吗?如果是这样,您可以将报表移到会计应用程序之外,并将查询直接粘贴到数据库专家的Command中。在我使用的另一个应用程序的几个案例中,我不得不这样做。
#1
Couple of things we have had success with is adding indexes to the databases, and instead of importing tables into the report, we instead wrote a stored procedure to retrieve the desired results.
我们取得成功的一些事情是向数据库添加索引,而不是将表导入报表,而是编写了一个存储过程来检索所需的结果。
#2
If indices and stored procedures dont get you where you need to be you have reached the denormalise until it works part of life with a database. You might want to look at creating an MI database with tables optimized for your reporting needs; and some data transformation scripts that can extract the data from production to your MI database. Depending on what it is oracle / ms have tools to help you do this.
如果索引和存储过程没有得到你需要的位置,那么你已经达到了反规范,直到它与数据库一起工作。您可能希望创建一个MI数据库,其中包含针对您的报告需求而优化的表;以及一些数据转换脚本,可以将数据从生产中提取到MI数据库。取决于它是什么oracle / ms有工具来帮助你这样做。
#3
We use Crystal Reports with a billing system, and we had queries in the database that take over 1.5 hours to complete. This doesn't even take into account the rendering/formatting of the reports.
我们将Crystal Reports与计费系统一起使用,我们在数据库中查询需要1.5小时才能完成。这甚至没有考虑报告的呈现/格式。
We created Materialized Views and force the client to refresh them daily. A materialized view is basically a database view that holds the returned dataset. The dataset is not refreshed unless you explicitly tell it to refresh.
我们创建了物化视图并强制客户端每天刷新它们。物化视图基本上是一个包含返回数据集的数据库视图。除非您明确告知数据集刷新,否则不会刷新数据集。
#4
Do you know what the SQL query is? If so, you can move the report outside the accounting application and paste the query directly into the Command in the database expert. I've had to do this in a couple of cases with another application I work with.
你知道SQL查询是什么吗?如果是这样,您可以将报表移到会计应用程序之外,并将查询直接粘贴到数据库专家的Command中。在我使用的另一个应用程序的几个案例中,我不得不这样做。