c/odbc performance

PoweRoy

I've got some performance issues with Navision 3.70 and the ODBC driver. the driver doesnt support datasets, i have to get them 1 by 1.
test370 is a remote Database
the sample is pointing to a local database
My code (C# .net):

OdbcConnection myConn = new OdbcConnection(); 
myConn.ConnectionString = "DSN=test370;
//myConn.ConnectionString = "DSN=Sample C/ODBC 32 bit;"; 
try 
{
	//connecting
	Console.WriteLine("Connecting " + DateTime.Now);
	myConn.Open();
	Console.WriteLine("Connected " + DateTime.Now);

	string SQLstring = "SELECT * FROM Productiestuklijstregel";
	OdbcCommand myCommand = new OdbcCommand(SQLstring ,myConn);
	Console.WriteLine("Execute query " + DateTime.Now);

	//execute query
	OdbcDataReader myReader = myCommand.ExecuteReader();
	Console.WriteLine("Query executed " + DateTime.Now);
				
	//table
	Console.WriteLine("Showing data " + DateTime.Now);
	DataTable dTable = new DataTable("List Records");
	
	//columns
	for (int fCount = 0; fCount < myReader.FieldCount; fCount++)
	{
		dTable.Columns.Add(new DataColumn(myReader.GetName(fCount)));
	}
	
	//data in table
	DataRow dRow;
	int i = 0; 
	while (myReader.Read()) 
	{ 
		dRow = dTable.NewRow();
		for (int k = 0; k < myReader.FieldCount; k++)
		{
			dRow[k] = myReader.GetValue(k);
		}
		dTable.Rows.Add(dRow);
		i++; 					
	}
	
	//showing data
	dataGrid1.SetDataBinding(dTable,null);
	Console.WriteLine("Done showing data " + DateTime.Now);
	Console.WriteLine(dTable.Rows.Count + " records fetched");
	
	//disconnecting
	myConn.Close();


}
catch (Exception k)
{
	MessageBox.Show(k.ToString());
}

local database

Connecting 25-4-2006 8:54:28
Connected 25-4-2006 8:54:29
Execute query 25-4-2006 8:54:29
Query executed 25-4-2006 8:54:40
Showing data 25-4-2006 8:54:40
'temp.exe': Loaded 'c:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
Done showing data 25-4-2006 8:54:51
8685 records fetched

executing query takes 11 seconds and 11 seconds to show the data in a datagrid

remote database

Connecting 25-4-2006 8:57:34
Connected 25-4-2006 8:57:34
Execute Query 25-4-2006 8:57:34
Query executed 25-4-2006 8:57:39
Showing data 25-4-2006 8:57:39
'temp.exe': Loaded 'c:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
Done showing data 25-4-2006 8:58:52
9319 records fetched

executing query takes 5 seconds and 1min 13seconds to show the data in a datagrid <-- like wtf

I think the read function is executed on the server and then it sends the data 1 field at a time to the client.
How can i optimise this?

kine

One thing - was someone using the DB server when you tested this? You need to calc with some time to fill cache etc. when first connection is executed with the server...

PoweRoy

its a testdatabase and currently i am the only one who's using it

i've tested the performance couple of times.
Connect + execute and then execute again (connection is still open) but no performance gain.

I've set the cache to 30000KB (max).

Is there maybe a way to fetch the data in 1 function instead of going trough the records 1 by 1 (and fields 1 by 1) ?

kine

Connect + execute and then execute again (connection is still open) but no performance gain.

Have you been disconnected between the two attempts? If yes, try to open Navision client and let him connected to the database without anz activity (to prevent the DB Server to close database after last connection is closed...)

PoweRoy

i opened navision and used the same odbc driver to the testdatabase.

I've commented the myConn.Close(); line

To execute a 2nd time the query the reader must be closed by myReader.Close(). at that time i get a System error.

An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll

so i apparently the connection was closed @ the 2nd time.

But i dont know why there's a problem with the connection. Only the Read part is really slow.

aci

You must use "ORDER BY" in your SQL statement to make it execute faster.

PoweRoy

Connecting 25-4-2006 16:25:31
Connected 25-4-2006 16:25:31
execute Query 25-4-2006 16:25:32
Query executed 25-4-2006 16:25:47
Showing data 25-4-2006 16:25:47
'temp.exe': Loaded 'c:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
Done showing data 25-4-2006 16:27:03
9321 records fetched

query

SELECT * FROM Productiestuklijstregel ORDER BY Nr_

in english this is:
SELECT * FROM Production BOM Line ORDER BY No_

No gain

aci

Okay, try this:

SELECT * FROM Productiestuklijstregel ORDER BY Type, Nr_

aci

The reason I suggested using "ORDER BY Type, No_" instead of just "ORDER BY No_" is because the list of keys for that particular table don't have a key that begins with "No_", so the C/ODBC driver was sorting them manually which takes a long time.

Use ORDER BY to suggest to the C/ODBC driver what key to use. Some keys have long series of fields, you don't have to specify all of them, just specify the first few.

Example, in "Sales Invoice Line" table, you will find the following keys:

Blanket Order No.,Blanket Order Line No.
Sell-to Customer No.,Type,Document No.
Sell-to Customer No.

Lets say you wanted a posted invoice with a certain "Document No.", you cannot use:

ORDER BY Document No_     <-- Slow

If you do, a manual sort will occur because none of the keys above starts with "Document No.". You will have to use the following:

ORDER BY Sell-to Customer No_, Type, Document No_      <-- Fast

In case you don't need "Document No.", you can omit it, but you cannot omit "Type" and keep "Document No.", that is, omitting a field in the beginning or in the middle of a key without removing the rest of the key.

Note: To see the list of keys for a particular table, go to Object Designer, open a table using "Run" button, then click on the sort icon on the toolbar. You could also see it in File-->Database-->Information-->Tables.

PoweRoy

the keys on the production BOM line are:
Production BOM no. , Version Code , Line No
Type, No

so i changed the sql query:
SELECT * FROM Production BOM Line ORDER BY Type, No_

Connecting 26-4-2006 8:49:53
Connected 26-4-2006 8:49:53
execute Query 26-4-2006 8:49:54
Query executed 26-4-2006 8:49:59
Showing data 26-4-2006 8:49:59
'temp.exe': Loaded 'c:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
Done showing data 26-4-2006 8:51:12
9321 records fetched

as u can see the query didnt became any faster. and the showing data neither.

I think the problem isnt at the query but at the data fetching. anyone know C# and can enlight me about this fuzzy stuff :P