VFP 7 Not Responding on SQL Select statement

[ahaws]

Hi all-
For some reason, when my program gets to the code below, VFP 7 says Not Responding when I look in the task list of windows. I can run in a separate .prg and it works, but when run from a form, it hangs. In the code below, I have a 1 field table that contains primary keys of another table. It is supposed to skip through this key table and select all from the other tablesthat have that 'key' as their primary key.


Select altcodes
Do While !Eof() && going through key table here
Wait Window "Building Report..." Nowait
tempno = altcodes.selcode
rptmsg1 = "CURRENT LICENSES BY CLASS CODE"
rptmsg2 = ""
Select Distinct olmast.busname1, olmast.busname2, olmast.busstnum, ;
olmast.busstreet, olmast.busdir, olmast.busunit, ;
olmast.buscity, ;
olmast.buszip, olmast.no_solicit, olmast.busphone, ;
olmast.establishd, olmast.mailname1, olmast.mailname2, ;
olmast.mailstnum, olmast.mailstreet, olmast.maildir, ;
olmast.mailunit, ;
olmast.mailcity, olmast.mailstate, olmast.mailzip, ;
olaccts.Desc, olowner.ownfirst, olowner.ownmi, ;
olowner.ownlast, olowner.owntitle ;
FROM olmast, olaccts, olowner, olcontrl ;
WHERE (olmast.lyear_pd = olcontrl.curr_yr And ;
olaccts.account = olmast.account And olaccts.current = .t. and ;
olowner.account = olmast.account And ;
olmast.purge <> .T. And ;
!Empty(tempno) And olaccts.code = tempno);
ORDER By olmast.busname1 ;
INTO Table selcodes.Dbf && table made on the fly....
Select 0
Use who + "temp.Dbf" Again
Append From selcodes && add records for this key to final table who + "temp.dbf"
Select altcodes
Skip
Enddo


Can anyone see why VFP 7 would hang on this select statement?
Please let me know if you do- thanks
angie

 

[EzLogic]

After this command, I would close the selcodes.dbf
INTO Table selcodes.Dbf
use

Select 0
..
..

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[rgbean]

angie,
You don't indicate how large these tables are or where the data resides or how fast (busy?) the network migh be. What settings do you use for EXACT, ANSI and DELETED?

Any reason you don't explicily close and remove selcodes.dbf in between Selects? Would be a good idea to check _TALLY or reccount("selcodes") before doing the APPEND? Why do you always open temp.dbf AGAIN and never close it? When you check this after a couple loops don't you have Temp opened multiple times all with different aliases?

Rick

 

[ahaws]

EXACT is on
ANSI is off
DELETED is on

I tried closing the selcodes, still hangs.
The reason I call temp 'again', is because higher up in the code, I must give a bogus tempno of 0 - since no keys have that number, so I can create a 'base' table to append selcodes to. So - I must first do that, then the form can see temp.dbf( users pc id.txt called "who" - to determine workstations - and to make sure no one else is using that table).

I can't figure this out. It use to work.
The tables that I am selecting from contain about 40,000 records each on average. Don't know why it hangs now and didn't before.
It could be that the network is a little slower today, it seems to be because it normally builds my .exe faster...but today _ SLOW.

 

[craigber]

That's common during long SQL SELECT statements. What it means is that when Windows polls the application asking it "Are you there", VFP doesn't respond. VFP is busy resolving the query. MS could put code in to respond to Windows, but it will then slow down the query.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[ahaws]

craigber-
so maybe I should wait a little bit to see if it will definitely finish with the query?

I think I had waited like 5 minutes before and had to endtask.

thanks
angie

 

[samuqeeth]

It must be the 'where' clause, you join same table twice.

Test your query adding one 'where' clause at a time.

 

[craigber]

I suggest you do some optimization work on the query, although it will never be fully optimized.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports&quot;

 

[ahaws]

hi-
Am changing the way I do my query-
instead of skipping through the key table and SQL each time, I skip though and throw those values into a string called codestr - separated by commas, then in the query I say:

AND inlist(olaccts.code,codestr);
into table temp.dbf

Will see if that does =the trick - but its 5 oclock somewhere...
Will check on Monday!
Have a good weekend
Angie

 

[DSummZZZ]

ahaws,

Don't forget about Performance Monitor. (Usually in Control Panel).
It's a good resource to see what's going on in your computer.
Add counters for the network, CPU, and disk. I find Histogram mode the most informative, especially after a truckload of counters have been added.
It will show you whether Fox is just busier than a one-legged Riverdancer or truly hung (Not Responding).

Also, which form method do you have your routine in? If it's by chance in say an activate or show or something that gets called by those, which may lose focus to a progress bar or something, when that bar goes away the activate could fire again.
I don't know if I explained it well enough, I hope you're with me here.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[ahaws]

Hi Dave-
the form method is in the click event of a navigational vcr button.
I will take a look into Performance Monitor and see what I can find from there.
thanks