Hi,
We are running Jet Essentials ver.11.1.11356.0 against a Serenic SQL Server Database (MS Dynamics NAV).
I have a general performance question regarding Jet. For a Jet Report we have been working with, it is selecting records from 1 table (G/L Entry) from the Serenic DB for a given date range. We have to wait an hour + for 100,000 records to be returned from the database. Even when selecting 48,000+ records is took 14 mins to run. When limiting the number of colums to 16 and selecting 100,000+ records it is taking 29 mins. If you issue a simular query in SQL Server Management Studio the results are returned in seconds. We are accessing and running Excel/Jet on a Windows Remote Desktop.
Here is the Jet formula we are using =NL("Table","G/L Entry",,"TableName=","G/L Entry","Posting Date",B4)
I also just ran an ODBC query in Excel (on my local machine) and it took seconds to return all the columns in the G/L Entry table for a posting date range of 1 year - 150,000+
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi,
First, I should say that Jet Reports is not a great tool for data dumping. If all you want is a data dump of your G/L Entry table, using SQL is a much better way to get a straight dump of all that data than Jet. Jet is a rich data analysis tool, which allows you to manipulate the data in many ways that would be much harder to do and maintain if you were writing SQL queries.
Jet does not use SQL to retrieve NAV data. It uses C/Front, which allows it to use the actual database schema (as you have seen, the table/field names in SQL are not the same as NAV), have rich company support, support the NAV security model, support Nav specific constructs like flow fields and flow filters, etc. Your SQL dump will not return the flow fields in the table nor allow you to filter those flow fields using the flow filter fields. However, C/Front is not nearly as fast as SQL, as you have seen. And giving Jet the power to do all the rich analysis that it is capable of does make it slower than it would be if it were a straight up data dumping tool (though we try to optimize it as much as possible).
So I guess what I'm trying to say is you should use Jet for what it does well, and use SQL for what it does well. If all you want is a data dump, then use your ODBC connection to the SQL server. That will definitely give you a data dump of the G/L Entry much faster than Jet will. When you want a rich data analysis tool that gives you power and flexibility, then use Jet.
What do you want to see 100,000 rows of every single field in your G/L Entry for anyway? Are you really going to try and read through 5,000,000+ pieces of data? Possibly you could create a few formulas in Jet to more precisely give you the data you are looking for, rather than dumping the entire table?
Regards,
Hughes
Please sign in to leave a comment.