Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #13461 > unrolled thread

SQLServer VO2.5 x VO2.8

Started byAntonio Falcao Neto <netofalcao.bezerra@gmail.com>
First post2021-11-11 11:52 -0800
Last post2021-11-14 13:52 +0100
Articles 3 — 3 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  SQLServer VO2.5 x VO2.8 Antonio Falcao Neto <netofalcao.bezerra@gmail.com> - 2021-11-11 11:52 -0800
    Re: SQLServer VO2.5 x VO2.8 Jamal <nospam@example.com> - 2021-11-13 01:51 -0500
    Re: SQLServer VO2.5 x VO2.8 "Wolfgang Riedmann" <wriedmann@gmail.com> - 2021-11-14 13:52 +0100

#13461 — SQLServer VO2.5 x VO2.8

FromAntonio Falcao Neto <netofalcao.bezerra@gmail.com>
Date2021-11-11 11:52 -0800
SubjectSQLServer VO2.5 x VO2.8
Message-ID<db88e132-c7e6-4614-975b-17a0a41e0f2fn@googlegroups.com>
Hello,
I have a very old system on VO2.5 on SQLServer.
I am switching to VO2.8. But performance on 2.8 is MUCH SLOWER.
Does anyone have any idea how to improve?
The code below takes 1s on VO25 and 27s on VO28. There are 2500 records

local oConn		as SQLConnection
local cCon		as string
local cSQL		as string
local oSele		as SQLSelect 
local aVal		as array 
local cStart	as string
	
cCon:="DRIVER=SQL Server;SERVER=IP;UID=XaXaXaXa;PWD=PoPoPoPo;DATABASE=base"	
oConn := SQLConnection{}
oConn:DriverConnect(,SQL_DRIVER_NOPROMPT,cCon)
oConn:SetConnectOption(SQL_AUTOCOMMIT,1)
	
cSQL:="select obr_uni_numero "+;
		"from obra_unidades "+;
		"where obr_numero=203 "+;
		"order by obr_uni_numero "+;
	
cStart:=Time()
oSele:=SQLSelect{cSQL,oConn}
if oSele:Execute()
		
	aVal:={}
	do while !oSele:EoF
		AAdd(aVal,oSele:FIELDGET(#Obr_Uni_Numero))
		oSele:Skip()
	endd
	
	TextBox{,"",ElapTime(cInicio,Time())+"-"+Str1(Len(aVal))}:Show()
		
endif
	
oSele:Close()

[toc] | [next] | [standalone]


#13462

FromJamal <nospam@example.com>
Date2021-11-13 01:51 -0500
Message-ID<smnn91$2lp$1@gioia.aioe.org>
In reply to#13461
Antonio,

What part is slow? Is it in the DO WHILE loop?

You need to profile the FieldGET() and Skip() method and isolate which 
is slow.

One option is to speed up the Skip() is by surrounding the DO WHILE loop 
with oSele:SuspendNotification() and oSele:ResetNotification() block; 
however, I'm not sure about this when dealing with SQL.


Good Luck!
Jamal



On 11/11/2021 2:52 PM, Antonio Falcao Neto wrote:
> Hello,
> I have a very old system on VO2.5 on SQLServer.
> I am switching to VO2.8. But performance on 2.8 is MUCH SLOWER.
> Does anyone have any idea how to improve?
> The code below takes 1s on VO25 and 27s on VO28. There are 2500 records
> 
> local oConn		as SQLConnection
> local cCon		as string
> local cSQL		as string
> local oSele		as SQLSelect
> local aVal		as array
> local cStart	as string
> 	
> cCon:="DRIVER=SQL Server;SERVER=IP;UID=XaXaXaXa;PWD=PoPoPoPo;DATABASE=base"	
> oConn := SQLConnection{}
> oConn:DriverConnect(,SQL_DRIVER_NOPROMPT,cCon)
> oConn:SetConnectOption(SQL_AUTOCOMMIT,1)
> 	
> cSQL:="select obr_uni_numero "+;
> 		"from obra_unidades "+;
> 		"where obr_numero=203 "+;
> 		"order by obr_uni_numero "+;
> 	
> cStart:=Time()
> oSele:=SQLSelect{cSQL,oConn} > if oSele:Execute()
> 		
> 	aVal:={}
> 	do while !oSele:EoF
> 		AAdd(aVal,oSele:FIELDGET(#Obr_Uni_Numero))
> 		oSele:Skip()
> 	endd
> 	
> 	TextBox{,"",ElapTime(cInicio,Time())+"-"+Str1(Len(aVal))}:Show()
> 		
> endif
> 	
> oSele:Close()
> 

[toc] | [prev] | [next] | [standalone]


#13463

From"Wolfgang Riedmann" <wriedmann@gmail.com>
Date2021-11-14 13:52 +0100
Message-ID<ivcf4pF1s12U1@mid.individual.net>
In reply to#13461
Hi Antonio,

> I have a very old system on VO2.5 on SQLServer.
> I am switching to VO2.8. But performance on 2.8 is MUCH SLOWER.
> Does anyone have any idea how to improve?

try to set 

SqlSetStmtCursorType( SQL_CURSOR_DYNAMIC )

at the start of your program.

Wolfgang


-- 

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web