Hi Guys,
Is there any tweaking that needs to occur on the SQL Server (2005) side of things to make Jet Reports perform quickly.
To give an example. If I run a report to pull out Item No.,Location Code,Safety Stock from the Stockkeeping unit table for 1000 items and 10 locations (ie. 10000 lines) it takes around 5 minutes. To generate the same report in a SQL Server query copy and paste into Excel takes about 10 seconds.
I am using normal NL and NF functions to pull the data out. I feel that there must be something clogging up the data extraction from the SQL Server end or in the Jet Reports configuration.
Any ideas?
Regards,
Lloyd
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi there.
I found myself asking similar questions when I first started with Jet. My understanding is that Jet uses the Navision CSIDE API (for want of a better term) to connect to the underlying database, even in SQL - i.e. not a direct connection. You probably had to install the Navision SDK to get Jet to work.
In short, Jet is bound by the inefficienies of Navision, things like flowfilters slow things down due to the poor implementation in SQL Server. The only real way I know of to speed up a Jet report is to ensure you are using an appropiate key (or index in SQL). The problem is you can't easily specify which key to use in Jet, so you either have to specifically use the criteria on an existing key, or create a new one in Navision. You must create the key in Navision otherwise it will have no impact. There are lots of KB articles on Jet and Nav keys.
Basically the same things that apply for tuning Nav flow through to Jet. Ensure you're Nav box is setup correctly as per best practices to get best results.
The best way to illistrate the problem is to run SQL profiler against Nav whilst running the report. You will likely see an itirative process being used to pull the data back.
If you're after Nav assistance, have a look at the Dynamics forums, http://dynamicsuser.net
if anybody else has other information about performance tuning, I'd be eager to hear from them.
cheers, Mark -
Jet Reports Historic Posts Thanks for your help on this Mark.
Do people find that Jet Reports alot faster over C/SIDE than with the C/SIDE API over SQL Server?
Regards,
Lloyd -
Jet Reports Historic Posts Hi Again,
To try and speed up the access I decided to connect to the SQL database using the Universal Connector and the Microsoft OLE DB for SQL Server Driver.
I have setup Navision as a SQL ODBC connection although it seems you can't pass a table name with a '$' symbol in it to Nav. For example =NL("Rows","Some Company$Stockkeeping Unit")
You can pull rows from a table without a '$' around it (such as the Company table).
If have also tried =NL("Rows","@@Some Company$Stockkeeping Unit") although this too displays a #VALUE.
Regards,
Lloyd -
Jet Reports Historic Posts If you are bypassing the Navision API and going direct to SQL server via something like ODBC, you will find many incompatibilities with the table names as well as column names, specifically $, period and % characters. As Navision was not designed for SQL the table and column names leave a lot to be desired.
Get your DBA to create an ODBC compatibly view of the SQL table. I believe there is a 'maintain views' option on Nav however I have not used this feature.
Please sign in to leave a comment.