en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Milan Zivkovic  
#1 Posted : Monday, September 17, 2012 1:38:23 PM(UTC)
Milan Zivkovic
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/16/2012(UTC)
Posts: 11

I was reading data from Dynamics NAV 5.0 Native table (1 Million rows and 14 columns, no BLOB columns)
thrue ODBC Driver with TARGIT DataReader for Dynamics NAV Native SSIS-Component.

That lasted about 27 minutes.
I was using another application and same SQL query finished in 5 minutes.

Why does SSIS-Component read slow? Any advice for performance improvement?

I have used TARGIT BI Accelerator for Dynamics NAV (2008) v6.1 Build 239.
Dennis de Gier  
#2 Posted : Wednesday, September 19, 2012 11:04:35 AM(UTC)
Dennis de Gier
Rank: Advanced Member

Groups: extranet\Forum
Joined: 10/12/2011(UTC)
Posts: 27

Hi Milan

Does this other application also write the query results to SQL Server?

The query itself might take 5 minutes in the TARGIT DataReader also, but there is other processing going on as the data rows move down the SSIS data pipeline. First of all, the NAV Native database can store data values that are illegal for inserting into SQL Server, e.g. out of range dates. This means that each column of each row needs to be checked for those invalid values depending on the types of the columns in the data set. This is handled internally in the DataReader before the data is passed on.

As the (now cleaned) data is passed on to be inserted into SQL Server on a row by row basis, the insert performance of SQL Server is critical. System resources but also table indexes and data and index page sizes and configuration play a part in how fast data can be inserted. Therefore, the general configuration of SQL Server and SSIS as well as hardware performance play a role.

Considering performance, it is always recommended to design data processing with incremental update in mind, as rows that no longer change in NAV might as well be stored permanently in the Data Warehouse. If only changed rows are transferred on each update, the time it takes to initially extract all data becomes insignificant.

I doubt there is much we can do in improving performance of the NAV Native DataReader as this was already in focus when developing the component. If, though, you want a better chance of having it looked into, you need to go through our official Partner Support channel.

Best Regards,

Dennis de Gier
Head of BI Acceleration @TARGIT
Milan Zivkovic  
#3 Posted : Wednesday, September 19, 2012 3:37:59 PM(UTC)
Milan Zivkovic
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/16/2012(UTC)
Posts: 11

Hi Dennis,

thank you for a comprehensive answer.

My performance test has contained only one Data Flow with TARGIT DataReadet and Multicast component (no destination component was included).

First query
select "Posting_Date","Document_Type","Description","Amount","Source_Code","Debit_Amount","Credit_Amount","Entry_No_","G/L_Account_No_","Document_No_","Job_No_","Gen__Bus__Posting_Group","Gen__Prod__Posting_Group",1 as "Company" from "G/L_Entry"
lasted 27 minutes

Second query with only 2 columns (int and string columns)
select "Document_Type","Description" from "G/L_Entry"
lasted less then 3 minutes.
Dennis de Gier  
#4 Posted : Thursday, September 20, 2012 1:39:58 PM(UTC)
Dennis de Gier
Rank: Advanced Member

Groups: extranet\Forum
Joined: 10/12/2011(UTC)
Posts: 27

Hi Milan

In this case, the increase in time is probably caused by both the size of the rows that need to be handled internally in SSIS and the fact that the "big" query contains a date column, which the smaller one does not. If you want to further zero in on which of these is the cause, you can remove "Posting_Date" from the query to see what the difference in performance is. Not that data without dates is very useful ;-)

/Dennis
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error