I'm using a CTD3.1 application to connect to a SQLServer 2008 database. I do it with SQL Server ODBC drivers.
While i'm on the local network it works fine. When i connect from ouside my clients office, over the internet, even though i have a very good line, the performance drops to a point where it is useluss.
An instruction that retrieves 8000 rows from a table takes 5 seconds using a .net application, 5 seconds using the SQLServer console and (the very same instruction) over 3 MINUTES from the application in CTD3.1. It takes 9 seconds just to do the SqlPrepareAndExecute with the select statement
I believe it is related with the drivers i'm using but I really don't know what to do. Can someone point me to the correct drivers/versions? Is there some native driver from CTD3.1 to SQLServer?
* MS SS driver is via ODBC only, I am sure, like it now for TD v5.2
* "9 seconds just to do the SqlPrepareAndExecute" -- that might not be too bad, can't tell without seeing the query.
* "3 MINUTES" -- have result set turned on (It is by default)? That can be a performance hit. Try the same query in SQLTalk and see what you get. What's you SQL.INI file look like? cache= size?
* The query is a very simple one. It returns about 8000 rows. The array is pre-dimensioned.
Code: Select all
Call SqlPrepareAndExecute( shSQL,DICDBConvertSqlString( '
select KMsgId, AMsgTxt
into :nCount, :stMessageStrAux
where KLang = :stRegisterLang
order by KMsgId' ) )
While SqlFetchNext( shSQL, nRet )
Set astRegisterStr[nCount] = stMessageStrAux
* Yes, we have SqlResultSet to true. We use SqlGetResultSetCount in our code, we need that. Is it such a performance hitter?
* The SQL.INI is like this:
Code: Select all
That can be a performance problem, Set result Set Mode (RS) to FALSE. Issue is that with RS = TRUE the router is creating a client-side file of all the rows fetches, because RS mode requests backward scrolling in the result set. If you don't need SqlFetchPrevious and SqlFetchRow then you do not need RS = TRUE.Yes, we have SqlResultSet to true.
The SQL statement is not going to show whether performance is bad or good, except...
What is this?? DICDBConvertSqlString()
I had also asked you to:
as a way to get a metric with another app/utility in the same environment as your app. We need a test case, even if you have to create a simple MS SS DB test table with 8000 records.Try the same query in SQLTalk and see what you get.
THANK YOU. The trick was to turn SqlResultSet off. Backwards, it's so logical that it hurts not having thought of it
We'll have to change login around the program to avoid the usage of SqlGetResultSetCount, SqlFetchPrevious, SqlFetchRow, etc but it is now behaving at great speed.
You saved the project and gave the last push for us to upgrade to CTD5.2
Who is online
Users browsing this forum: [Ccbot] and 1 guest