SQL Toolkit Vs Native ODBC Speed

[holly7787]

Hello everyone,

I'm always using SQL toolkit for my application, usually to save data doing Select -> bind -> create and put record and never complain about speed. Maybe because the database is on the same machine that run my application.

Now I have an application that have remote database and must fetch table with few record, some with 10k and one with 400k.

Database is stored in old .mdb access file and when i do some query the activate function took 2s to exexute, fetch is fast.

I've tryed copying the file locally and it's a lot faster but this project must work on many pc with shared database.

I've tryed to migrate to Microsoft SQL server and now activate is super fast, but when I fetch 10k record takes about 2.5s.

I've done a lot of test, assisted with AI too, and seems that .mbd load all data locally and execute query on client pc, that's why activate is slow and fetch is fast.

On the other way with Microsoft SQL query execute on server and activate is fast but fetch is slow.

AI suggested to test with native odbc driver and bypass NI toolkit and fetch on Microsoft SQL goes down to 25ms, thats 100 times faster.

Did someone get this problem too? How did you solve it? I prefer to not convert every DB instruction and keep using toolkit so everyone in my office knows the instruction.

Thanls

[holly7787 · Accepted solution]

turns out that the solution is this.

	hstmt = DBNewSQLStatement(hdbc, sQuery);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CURSOR_TYPE, DB_CURSOR_TYPE_FORWARD_ONLY);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CURSOR_TYPE, DB_CURSOR_TYPE_KEYSET);
	resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CURSOR_TYPE, DB_CURSOR_TYPE_DYNAMIC);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CURSOR_TYPE, DB_CURSOR_TYPE_STATIC);
	resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_LOCK_TYPE, DB_LOCK_READ_ONLY);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_LOCK_TYPE, DB_LOCK_PESSIMISTIC);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_LOCK_TYPE, DB_LOCK_OPTIMISTIC);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_LOCK_TYPE, DB_LOCK_BATCH_OPTIMISTIC);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CACHE_SIZE, 100);
	//resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CURSOR_LOCATION, DB_CURSOR_LOC_CLIENT); // se devo recuperare tanti record
	resCode = DBSetStatementAttribute(hstmt, ATTR_DB_STMT_CURSOR_LOCATION, DB_CURSOR_LOC_SERVER); // se devo recuperare pochi record
	resCode = DBOpenSQLStatement(hstmt);

instead of 

hstmt = DBActivateSQL(hdbc, sQuery);

got a 20x speed increase.

also database indexing gives a lot of help

[holly7787]

Now I've got another problem.

When I call multiple DBNewSQLStatement() it return always the same hstmt.

I've tryed to update SQL Toolkit to latest version and change connection string to try ODBC, OLEDB or other but still have the same issue

[holly7787]

ok the solution seems to be this.

not using this to close the statement:

DBCloseSQLStatement(hstmt);
DBDiscardSQLStatement(hstmt);

but use this:
DBDeactivateSQL(hstmt);

[carlox]

---

@holly7787 wrote:

got a 20x speed increase.

also database indexing gives a lot of help

---

Just out of curiosity, have you ever tried using stored procedures?
Naturally, it depends on the complexity of your queries.
Without stored procedures, the query is executed on the local PC, which has to fetch the records from the server—a situation where indexes only provide partial relief.
With stored procedures, everything is executed on the server, and only the final result is transferred.

I used SQL Server years ago, though never from within LabWindows, so I can’t provide any further details.

[holly7787]

Thanks Carlo, that could be another good idea to improve further if it was on a DBMS like Microsoft SQL but this is on an access file, so only a DB

Now I've reached my goal also optimising some query.

For example instead of selecting the table with id order desc only to fetch the highest id value I do select max(id).

14 years doing developer and still a lot of feature to learn!

[carlox]

---

@holly7787 wrote:

14 years doing developer and still a lot of feature to learn!

---

I have for sure more than double (35+) and I'm still learning. So be prepared 😎

[holly7787]

Just have a call with NI and they confirm that I've found a bug and the workaround is to use DBDeactivateSQL instead of DBCloseSQLStatement and DBDiscardSQLStatement.

Bad news is that this bug probably will never be fixed because LabWindows developement is dead in National Instruments

[holly7787]

Just a little update, NI created BUG request #4156878 in order to solve this issue.