Hi Stephen,
Your reply doesn't help the matter in question but I'll play along. ;-)
On "real DBMS" systems like your example, shouldn't it use the header or meta-data to know the record count rather than load the whole thing into memory? That doesn't seem right.
On 10/24/2020 11:25 AM, Stephen Russell wrote:
I just did a count(*) from one of our biggest tables. It took 2 min to load it into memory the first time. SELECT count(*) cnt FROM [erplndb].[dbo].[glTable600]
cnt 138,371,855
The second time I run this it only takes .03 seconds. The size of the table and index is a mere 364 gigs today.
On Fri, Oct 23, 2020 at 2:24 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
The WHERE clause was something to count but I don't think it was optimizable because no index based on it. (Vague recollection; not 100% sure.)
SET MULTILOCKS is ON.
On 10/22/2020 3:36 PM, Richard Kaye wrote:
True. My next guess is it's using the index because your WHERE clause
is Rushmore optimized. So it still doesn't need to move the record pointer in the source table. And I'll return to how your environment is setup. I'd have to go read the fine docs to understand why moving the record pointer is also trying to lock the row. Do you have SET MULTILOCKS ON?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Richard
Kaye
Sent: Thursday, October 22, 2020 3:28 PM To: profoxtech@leafe.com Subject: RE: COUNT FOR hangs on record locking, but SQL - SELECT
COUNT(*) works with no issue. Why?
Leaving aside the environment stuff like SET EXCLUSIVE and SET
MULTILOCKS, my first guess is COUNT FOR actually moves the record pointer through every row in the table, Whereas SELECT COUNT() is reading the header.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of MB
Software Solutions, LLC
Sent: Thursday, October 22, 2020 3:24 PM To: profoxtech@leafe.com Subject: COUNT FOR hangs on record locking, but SQL - SELECT COUNT(*)
works with no issue. Why?
VFP9SP3
Why would a COUNT FOR hang ("Attempting to lock") whereas my easy
workaround is SELECT COUNT(*) FROM SomeCursor WHERE <<yada yada yada>> ??
tia, --Mike
[excessive quoting removed by server]