Question..... 1

[cjulio]

Why does my aplications is so slow opening and whorking with tables with 20.000 records...

I dont no if this is Ok or is there anithing i can do to make it faster...

 

[Mike Gagnon]

Why does my aplications is so slow opening and whorking with tables with 20.000 records...

I dont no if this is Ok or is there anithing i can do to make it faster...

It depends on a lot of things. Filters can slow down a lot, even worst if you have filters on indexes. LAN can slow down (specially if you are still using 10 base T cards).
What is your setup?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[daleoclarke]

Whats the configuration of your system.

- Is the application running locally or networked
- Whats the load(# of user) like on the network
give me some one info. Question is too vague.

Visual is generally speedy at opening and performed actions tables.

Send some more info

 

[cjulio]

My Network is a 100/10 MB Network
For Example I am tring to do this and this code in some computer is very slow (this table is only for +/- 3000):

aux1 = thisform.numcursors + "51"
aux2 = thisform.numcursors + "52"

cod = upper(alltrim(thisform.cod.value))
fm1 = upper(alltrim(thisform.fm1.value))
fm2 = upper(alltrim(thisform.fm2.value))
mor = upper(alltrim(thisform.mor.value))
nom = UPPER(alltrim(thisform.nom.value))
mor = upper(alltrim(thisform.mor.value))
loc = upper(alltrim(thisform.loc.value))
cdp = upper(alltrim(thisform.cdp.value))
con = upper(alltrim(thisform.con.value))
tel = upper(alltrim(thisform.tel.value))
W = 1

do case
case thisform.opgordem.value=1
ordem = "codigo"
case thisform.opgordem.value=2
ordem = "enome"
case thisform.opgordem.value=3
ordem = "famn1,famn2"
endcase

do case
*** Fornecedores ***
case thisform.Tipo="F"
thisform.caption="Consulta de Fornecedores (entcon1)"
campo1 = "fcodigo"
campo2 = "UPPER(ffamn1)"
campo3 = "UPPER(ffamn2)"
comando = " fnumseq = enumseq "

*** Clientes ***
case thisform.Tipo="C"
thisform.caption="Consulta de Clientes (entcon1)"
campo1 = "ccodigo"
campo2 = "UPPER(cfamn1)"
campo3 = "UPPER(cfamn2)"
comando = " cnumseq = enumseq "

*** Vendedores ***
case thisform.Tipo="V"
thisform.caption="Consulta de Vendedores (entcon1)"
campo1 = "vcodigo"
campo2 = "UPPER(Vfamn1)"
campo3 = "UPPER(Vfamn2)"
comando = " vnumseq = enumseq "

otherwise
thisform.grdfic1.recordsource=""
endcase

select &campo1 as codigo, &campo2 as famn1, &campo3 as famn2 ;
, UPPER(ENOME) AS ENOME, UPPER(emorada1) as emorada1 ;
, upper(ecodp) as ecodp, upper(elocalid) as elocalid, upper(encontr) as encontr ;
, upper(etelef1) as etelef1, DELETED() AS EAPAGADO;
from thisform.ficheiros(1,1), thisform.ficheiros(3,1) ;
where &comando ;
into cursor &aux1

TABARR = thisform.nometabela
CREATE TABLE (TABARR) FREE (TACODIGO N(8))

SELECT &AUX1
GO TOP

SCAN
errado = .F.
codigoent = CODIGO
coden = UPPER(alltrim(str(codigo)))

if cod != "" and errado = .F.
IF ATCC(cod, coden) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if fm1 != "" and errado = .F.
IF ATCC(fm1,famn1) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if fm2 != "" and errado = .F.
IF ATCC(fm2,famn2) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if nom != "" and errado = .F.
IF ATCC(NOM,enome) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if mor != "" and errado = .F.
IF ATCC(mor,Emorada1) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if loc != "" and errado = .F.
IF ATCC(loc,elocalid) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if cdp != "" and errado = .F.
IF ATCC(cdp,ecodp) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if CON != "" and errado = .F.
IF ATCC(CON,encontr) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if tel != "" and errado = .F.
IF ATCC(tel,etelef1) > 0
errado = .F.
else
errado = .T.
endif
ENDIF

if errado = .F.
insert into (tabarr) values (codigoent)
W = W + 1
ENDIF

ENDSCAN

set deleted on
cur1 = " select codigo, enome, emorada1, ecodp, elocalid, encontr, etelef1, famn1, famn2, EAPAGADO ;
from &aux1 into cursor &aux2 order by &ordem ;
where CODIGO IN (SELECT * FROM &TABARR) "

thisform.grdfic1.recordsource = cur1

set deleted off

select TABARRENT
use

DELETE FILE &tabarr

thisform.Alterou = .F.

 

[gchandrujs]

I Think that SQL query Statement takes long time and ATCC too. If you want to know the timings of each line execution try using SET COVERAGE TO filename command before executing the above codes and after finishing codes give SET COVERAGE TO. Now you can view the filename for time taken for execution of lines.


Suggestions Invited...


gchandrujs

 

[Mike Gagnon]

select &campo1 as codigo, &campo2 as famn1, &campo3 as famn2 ;
, UPPER(ENOME) AS ENOME, UPPER(emorada1) as emorada1 ;
, upper(ecodp) as ecodp, upper(elocalid) as elocalid, upper(encontr) as encontr ;
, upper(etelef1) as etelef1, DELETED() AS EAPAGADO;
from thisform.ficheiros(1,1), thisform.ficheiros(3,1) ;
where &comando ;
into cursor &aux1


As gchandrujs suggests I have a feeling that your SQL statement is most likely not optimized. Try removing your :

Where &comando

And see if that is the culprit. This link might help optimize your SQL:

http://www.craigberntson.com/Articles/kb004.htm

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[gchandrujs]

HI Mike,

Where there is need, we have to use somethings. What cjulio done with Where &comando is not wrong. He may in need of that.

I can't understand And see if that is the culprit


Suggestions Invited...

gchandrujs

 

[Mike Gagnon]

gchandrujs

What cjulio done with Where &comando is not wrong. He may in need of that.

I was merely suggesting, in order to narrow down the bottleneck, to remove the filter and see if there is a difference in speed, and if there is then maybe there is a way the "fine tune" the WHERE clause. I wasn't suggesting to remove permanently. Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[cjulio]

People my problem is the atcc statement the sql is good but i dont now in this case how to the atcc statement faster.... if there is any ideas.... Please Say....

 

[cjulio]

How yes and both the variables in "&comando" are index

 

[daleoclarke]

Try the following.

in the SQL statement try using brackets () instead of macro substitution &.

eg
instead of
select &campo1 as codigo, &campo2 as famn1, &campo3 as famn2 ....

try
select (campo1) as codigo, (campo2) as famn1, (campo3) as famn2....

if the above is not working (usually it works), try building the statement by concatenating the parts

eg. "SELECT "+campo1+ " AS codigo,"+campo2+ " as famn1,".....

Microsoft has always said that as best as possible avoid using mcro substitution.

Another thing, instead of DELETE FILE &tabarr. try using the sys(3) function to assign a temp file. Fox will remove the file automatically once use close it.

You can also omitt the GO TOP. the SCAN funtion will automatically start at the top of the file

Let me know how this worked out.