Connection between Navision and SQL slow???

elwin68

I want to send data between Navision and MSSQL or MySQL. I found a lot of examples about using ADO. This looks what i need.
In my module it can happen 10000 records or more must be sent to SQL. Is ADO quick enough to do this without the user must wait several minutes?

I hope someone has the experience with this.

Thanks in advance.

Marije_Brummel

It can do it in milliseconds.

I have a high availability interface at a customer which is reading 1000ds of records continuously.

It is using Sybase but that is basicaly MSSQL

nunomaia

Yes, ADO works just fine for that.

elwin68

Ok, thanks for your answers.

I know for sure it isn't quick when sending 10000 records from Navision to SQL separately. I have tested that already.
How do I add or modify quickly 10000 records at once in SQL with code in Navision.

Thanks in advance.

Marije_Brummel

What statement do you send to SQL? Do you use cursors or some update statement?

What is the performance when you run the SQL statement in the query analyser?

elwin68

I use the execute-command to add or update the records in SQL.

ADOrs.Open('SELECT * FROM Test',ADOConn,OpenMethod,LockMethod);
fieldstr := STRSUBSTNO('%1', 'field1,field2,field3');
IF TestRec.FINDFIRST THEN
REPEAT
valuestr := STRSUBSTNO('''%1'',''%2'',%3',
TestRec.field1,TestRec.field2,TestRec.field3);
str := STRSUBSTNO('INSERT INTO %1 (%2) VALUES (%3)',
'Test', fieldstr, valuestr);
ADOConn.Execute(str);
UNTIL TestRec.NEXT = 0;

I don't know how to use cursors. I hope someone can help me with this problem.

Marije_Brummel

Well, to start with a findfirst repeat until will create a double read on SQL when running on SQL Server with NAV.

How long does it take to create 10000 records directly on SQL with the query analyser?

What is the structure of the SQL Table? How is it indexed? What is the clustered index?

What is the connection between the SQL Machine and the Navision machine? Is it a testserver or a production machine.

SQL needs to warm up, so the 2nd time might be faster.

elwin68

Hello Mark,

I'm not that good in SQL, so I use commands that I know.
The code in the previous message is just an example to test the connection.

Our customer wants some data from Navision 4 SP3 in a SQL database. This information in the SQL database is used for other purposes.
4 times a day this information must be put in the SQL database.

I made a SQL table with just 3 fields which are all in the primary key (field1+field2+field3). I have tried to get the data from a Navision table with more than 10000 records to the test table in SQL. Then an error occured.
When I decrease the records to 1000 records everything goes right but it's very slow. The procedure takes 47 secords for 1000 records.

I know for sure my solution isn't good enough. I hope someone can give me an example to do this for 10000 records and much faster.

Thanks in advance.

elwin68

I have found a solution myself.

Export the data from Navision to a text file and send a SQL statement to import the data from the text file to the SQL table.

I have tried it on a server installation with a table with 120.000 records and the data was in SQL within 50 seconds. This is acceptable.

When someone knows a quicker solution let me know.

Marije_Brummel

The problem is in the transaction.

If you execute every insert seperately it does a commit every time and that eats away your performance.

This SQL Statement only takes 4 seconds on my server

BEGIN TRAN
declare @Counter as Int

SET @Counter = 1;

while (@Counter < 120000) BEGIN
INSERT INTO ADOTEST2 ([LineNo], [TextData], [ApplicationName]) VALUES (@Counter, 'Text', 'App')		
SET @Counter = @Counter +1;

ara3n

Mark
The your sql statement is not dynamic.
If 'Text', 'App' where different in every statement.
How would you write your statement?