Very bad performance from ctd3.1 to sqlserver over web

General discussion forum about all databases other than SqlBase.
CarlosMora
Portugal
Posts: 20
Joined: 13 May 2020, 12:34
Location: Portugal

Very bad performance from ctd3.1 to sqlserver over web

Post by CarlosMora » 05 Nov 2010, 00:23

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)

Jeff Luther

Re: Very bad performance from ctd3.1 to sqlserver over web

Post by Jeff Luther » 05 Nov 2010, 00:50

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?

CarlosMora
Portugal
Posts: 20
Joined: 13 May 2020, 12:34
Location: Portugal

Re: Very bad performance from ctd3.1 to sqlserver over web

Post by CarlosMora » 06 Nov 2010, 13:40

Hi Jeff,

* 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
* 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:

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
Thx in advance, I'm really desperate.

(Carlos Mora)

Jeff Luther

Re: Very bad performance from ctd3.1 to sqlserver over web

Post by Jeff Luther » 06 Nov 2010, 17:37

Yes, we have SqlResultSet to true.
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.

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:
Try the same query in SQLTalk and see what you get.
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.

CarlosMora
Portugal
Posts: 20
Joined: 13 May 2020, 12:34
Location: Portugal

Re: Very bad performance from ctd3.1 to sqlserver over web

Post by CarlosMora » 07 Nov 2010, 16:03

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)

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 1 guest