Hi all,
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?
Thx
(Carlos Mora)
Very bad performance from ctd3.1 to sqlserver over web
Re: Very bad performance from ctd3.1 to sqlserver over web
Well, let's see if we can unscramble this a bit...
* 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?
* 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?
Re: Very bad performance from ctd3.1 to sqlserver over web
Hi Jeff,
* The query is a very simple one. It returns about 8000 rows. The array is pre-dimensioned.
* I've tried with a Trial verison of CTD5.2 (because i'm considering the upgrade) but we have the same problem.
* 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:
Thx in advance, I'm really desperate.
(Carlos Mora)
* The query is a very simple one. It returns about 8000 rows. The array is pre-dimensioned.
Code: Select all
!!CB!!
Call SqlPrepareAndExecute( shSQL,DICDBConvertSqlString( '
select KMsgId, AMsgTxt
into :nCount, :stMessageStrAux
from SYSADM.WUserMsg
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
[odbcrtr]
buffrow=10
odbctrace=off
odbctracefile=sql.log
longbuffer=62536
enablemultipleconnections=on
;fetchrow=1
REMOTEDBNAME=DARWIN5,Driver=Sql Server;Server=ODBCTFSQL02;Database=DARWIN5;AnsiNPW=No;QuotedId=No;AutoTranslate=No;Language=English
(Carlos Mora)
Re: Very bad performance from ctd3.1 to sqlserver over web
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.
Re: Very bad performance from ctd3.1 to sqlserver over web
Jeff,
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
Thx
(Carlos Mora)
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
Thx
(Carlos Mora)
Who is online
Users browsing this forum: [Ahrefs], [Ccbot] and 0 guests