We have been using Jet Reports for about 4-6 weeks now with a lot of progress being made. Presently, I have simple reports that are taking 20 minutes to run, and a more complicated one is taking 9 hours.
They both work, but since we only have 1 year of data at present, I hope these reports won't take twice as long next year!
How can I structure my reports and tables so they run as fast as possible? I have seen a mention in this forum of using SQL to limit dates, but no instructions. My skills are in Excel, Access and a little editing of VBA and SQL code, so I have a a basic undestanding that it is possible, but need a starting point…
I do notice the same data requirements run faster in Table mode than Report mode, so I have been using Tables and Pivot Tables for faster results.
Any other suggestions? …Ferris?
Date
Votes
6 comments
-
Jet Reports Historic Posts Official comment Hi,
Are you reporting straight off SQL server or are you using NAV SQL or something else? You could add indexes to your SQL server which match the queries Jet is doing in your reports. The SQL Server Profiler is a pretty good tool to use for this. You can profile the SQL Server while your reports are running and it can analyze the queries and recommend indexes for you to add to your SQL tables.
When you say it runs faster in "Table mode", I assume you are talking about using the NL(Table) function rather than NL(Rows), right? You still have Design mode and Report mode when using NL(Table), but it is generally faster to use NL(Table) than NL(Rows) although there are some things you can't do just with NL(Table).
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
We are just the end users, and weren't installing it so I don't know which SQL installation is used. We are a small company, and just had it installed with no speciasl requirements if that helps.
We are getting the "cubes" installed shortly, which is presented by our vendor as providing vast improvements in performance specs.
True, I hope? Will the cube be significantly faster? I am comfortable editing existing SQL lines (I've made Union tables in Access) so if the cube has that level of controls, we should be set after the install.
Thanks in advance! -
Jet Reports Historic Posts I guess my question was, are you connecting directly to SQL Server using a Jet Reports Universal data source or are you connecting to Microsoft Navision using a Jet Reports Navision connector? If you are connecting to a universal data source, you can execute SQL queries directly in a Jet function, which might make certain types of reports faster, depending on your comfort and skill writing SQL queries. Of course, if you have a Navision database, then there are many more advantages to using a Navision connector, such as ease of reporting off flow fields and advanced dimensions.
Cubes can definitely make some types of reporting faster depending on the structure of the database. Particularly, numerical reports which contain lots of sums, etc. can be sped up using cubes, but other types of reports as well.
Regards,
Hughes -
Jet Reports Historic Posts Since we're using Nav 2009 R with SP2, we're probably using the Jet Reports Navision connector.
I saw the SQL queries listed in a jet function. I may try a simple SQL SELECT query string to see if it responds on the Table function. If I can, I'll post my results.
Thanks for your help! -
Jet Reports Historic Posts If you're using the Navision connector then that won't work since Jet does not use SQL directly to connect to Navision, so it has no way to send a SQL query to it.
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
You were correct, apparently, as I couldn't get this line:
=NL("Rows","SQL=SELECT No. from Customer,No.")
to return data.
I'll keep using Tables when possible to keep up the performance to acceptable levels until we can use Cubes or SQL for further improvements. Thanks again!
EDIT: With more experience (another week and this forum's help… :) ), I found that my performance increased considerably when I have the the Table or Report return only the rows needed (mostly using filter fields and NL(SUM…) ) . I have to think about our end requirements, then build the report.
-John
Please sign in to leave a comment.