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
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
Don't forget the WHERE clause....this is a count for specific criteria...not a total record count for the table!
I would wager you're onto something though: I bet COUNT FOR locks the header and SELECT COUNT(*) does NOT.
?????
On 10/22/2020 3:27 PM, Richard Kaye wrote:
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]
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
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/MWHPR1001MB2144FA9E548C9FCCC6A8B4CFD21D0@MW... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. Report [OT] Abuse: http://leafe.com/reportAbuse/MWHPR1001MB2144FA9E548C9FCCC6A8B4CFD21D0@MWHPR1...
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]
Other than the differences in the internals between an xBase function versus the SQL engine I'm out of guesses. I bet Christof (or Chen) could explain it... 😊
I still don't understand why it's attempting to get a row lock. What about SET EXCLUSIVE?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of MB Software Solutions, LLC Sent: Friday, October 23, 2020 3:24 PM To: profoxtech@leafe.com Subject: Re: COUNT FOR hangs on record locking, but SQL - SELECT COUNT(*) works with no issue. Why?
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]
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/fb34194e-1588-b0d3-6f56-039788741f4e@mbsoft... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. Report [OT] Abuse: http://leafe.com/reportAbuse/fb34194e-1588-b0d3-6f56-039788741f4e@mbsoftware...
Definitely SET EXCL OFF since it's a multi-user app.
My money is on wOOdy. I honestly think I had asked something like this years ago but forgot the details.
On 10/23/2020 3:33 PM, Richard Kaye wrote:
Other than the differences in the internals between an xBase function versus the SQL engine I'm out of guesses. I bet Christof (or Chen) could explain it... 😊
I still don't understand why it's attempting to get a row lock. What about SET EXCLUSIVE?
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of MB Software Solutions, LLC Sent: Friday, October 23, 2020 3:24 PM To: profoxtech@leafe.com Subject: Re: COUNT FOR hangs on record locking, but SQL - SELECT COUNT(*) works with no issue. Why?
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]
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]
SQL server works completely differently from FoxPro. For Sql Server the performance even on the same system would heavily depend on the isolation level you use, concurrent access and memory usage. I've consulted on SQL server databases projects in the close to a TB database size range where developers were thinking that performance optimization means creating the right indexes, but totally ignored how SQL server locking works, how transactions work, how memory can be utilized, etc.
It does make a big difference, if the database engine knows which parts of a file have changed (SQL Server) vs. it can only tell if a file has changed, if at all (VFP).
BTW, what exactly is "glTable600"?
glTable600 was replacing the true table name because we don't state true identities. It is our gl transaction table and it is a beast in size.
On Sat, Oct 24, 2020 at 10:43 AM Christof Wollenhaupt < christof@wollenhaupt.org> wrote:
SQL server works completely differently from FoxPro. For Sql Server the performance even on the same system would heavily depend on the isolation level you use, concurrent access and memory usage. I've consulted on SQL server databases projects in the close to a TB database size range where developers were thinking that performance optimization means creating the right indexes, but totally ignored how SQL server locking works, how transactions work, how memory can be utilized, etc.
It does make a big difference, if the database engine knows which parts of a file have changed (SQL Server) vs. it can only tell if a file has changed, if at all (VFP).
BTW, what exactly is "glTable600"?
-- Christof
On 24. Oct 2020, at 17:25, Stephen Russell srussell705@gmail.com
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.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Thanks, it makes sense not to reveal true identities. gl is "general ledger"?
On 24. Oct 2020, at 18:04, Stephen Russell srussell705@gmail.com wrote:
glTable600 was replacing the true table name because we don't state true identities. It is our gl transaction table and it is a beast in size.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
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]
The example was to do a count(*) in VFP. You don't have these system tables.
SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) -- WHERE [Tables].name = N'name of the table' GROUP BY SCHEMA_NAME(schema_id), [Tables].name;
You never have to count the table rows itself. I wasn't going to show this thinking that it wasn't viable in VFP.
On Sat, Oct 24, 2020 at 6:34 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
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]
You never have to count the table rows itself
But.... but... wouldn't this only be relevant if you just want the raw RecordCount?
As soon as you would do a COUNT FOR (or COUNT WHERE for you SQL guys) the metadata or systemtables don't help you a dime. And VFP's queryEngine is so intelligent that it can optimze a COUNT FOR by asking the indices, as long as the FOR condition is equal to an indexkey. BTW: since SQLServer got VFP's Rushmore logics, it should be the same there too.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Stephen Russell Gesendet: Montag, 26. Oktober 2020 02:01 An: ProFox Email List profox@leafe.com Betreff: Re: COUNT FOR hangs on record locking, but SQL - SELECT COUNT(*) works with no issue. Why?
The example was to do a count(*) in VFP. You don't have these system tables.
SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) -- WHERE [Tables].name = N'name of the table' GROUP BY SCHEMA_NAME(schema_id), [Tables].name;
You never have to count the table rows itself. I wasn't going to show this thinking that it wasn't viable in VFP.
those lawyers who are too stupid to see the obvious.
In uber complicated queries or counts, in this case, I'll add various counts in a query that uses UNION to allow index seeks per param with an index hint for each that I am interested in.
Granted raw VFP will pull the entire cdx into ram and probably the entire table because we have so much ram, and vfp table size is tiny. Local ram always kicks ass. My current db file is 1.8TB. My SQL server in Prod has 60 gig of ram for the instance that holds my ERP db. It jams for doing the traditional functionality all day, any day. Nightly we pull relevant business data into our DW. Against the DW I can do any processing I want and never stress the ERP system.
Our business rule is to never touch the live ERP system unless you are after just in time data. We only have 2 usage cases that need to do that so far. Customer portal updates on daily shipments in one and the Certificate of Analysis for every truckload leaving.
We are very proud of holding that line. We do 150-250 trucks a day depending on the day in the week.
On Mon, Oct 26, 2020 at 3:05 AM Jürgen Wondzinski juergen@wondzinski.de wrote:
You never have to count the table rows itself
But.... but... wouldn't this only be relevant if you just want the raw RecordCount?
As soon as you would do a COUNT FOR (or COUNT WHERE for you SQL guys) the metadata or systemtables don't help you a dime. And VFP's queryEngine is so intelligent that it can optimze a COUNT FOR by asking the indices, as long as the FOR condition is equal to an indexkey. BTW: since SQLServer got VFP's Rushmore logics, it should be the same there too.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Stephen Russell Gesendet: Montag, 26. Oktober 2020 02:01 An: ProFox Email List profox@leafe.com Betreff: Re: COUNT FOR hangs on record locking, but SQL - SELECT COUNT(*) works with no issue. Why?
The example was to do a count(*) in VFP. You don't have these system tables.
SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) -- WHERE [Tables].name = N'name of the table' GROUP BY SCHEMA_NAME(schema_id), [Tables].name;
You never have to count the table rows itself. I wasn't going to show this thinking that it wasn't viable in VFP.
those lawyers who are too stupid to see the obvious.
[excessive quoting removed by server]
Thanks, wOOdy....I failed to make that clear. ;-)
On 10/26/2020 4:05 AM, Jürgen Wondzinski wrote:
You never have to count the table rows itself
But.... but... wouldn't this only be relevant if you just want the raw RecordCount?
As soon as you would do a COUNT FOR (or COUNT WHERE for you SQL guys) the metadata or systemtables don't help you a dime. And VFP's queryEngine is so intelligent that it can optimze a COUNT FOR by asking the indices, as long as the FOR condition is equal to an indexkey. BTW: since SQLServer got VFP's Rushmore logics, it should be the same there too.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Stephen Russell Gesendet: Montag, 26. Oktober 2020 02:01 An: ProFox Email List profox@leafe.com Betreff: Re: COUNT FOR hangs on record locking, but SQL - SELECT COUNT(*) works with no issue. Why?
The example was to do a count(*) in VFP. You don't have these system tables.
SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) -- WHERE [Tables].name = N'name of the table' GROUP BY SCHEMA_NAME(schema_id), [Tables].name;
You never have to count the table rows itself. I wasn't going to show this thinking that it wasn't viable in VFP.
those lawyers who are too stupid to see the obvious.
[excessive quoting removed by server]
No, the exercise was to count something of a specific criteria....not a general RECCOUNT of the resulting dataset.
On 10/25/2020 9:00 PM, Stephen Russell wrote:
The example was to do a count(*) in VFP. You don't have these system tables.
SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) -- WHERE [Tables].name = N'name of the table' GROUP BY SCHEMA_NAME(schema_id), [Tables].name;
You never have to count the table rows itself. I wasn't going to show this thinking that it wasn't viable in VFP.
On Sat, Oct 24, 2020 at 6:34 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
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]
COUNT does indeed a Filelock, which has the effect that the result doesn't get changed in between the time it needs to traverse the table.
Thus if someone is just editing a record and altering your FOR field: what should COUNT (or CALC or SUM etc) take into account? That's why it's waiting until he's finished. Of course, for a perfect waiting experience, you should SET REPROCESS correctly.
A SQL COUNT just takes a snapshot of the buffered data before the edit.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von MB Software Solutions, LLC Gesendet: Donnerstag, 22. Oktober 2020 21:24 An: ProFox Email List profox@leafe.com Betreff: 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
-- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Thanks, wOOdy!
On 10/23/2020 4:08 PM, Jürgen Wondzinski wrote:
COUNT does indeed a Filelock, which has the effect that the result doesn't get changed in between the time it needs to traverse the table.
Thus if someone is just editing a record and altering your FOR field: what should COUNT (or CALC or SUM etc) take into account? That's why it's waiting until he's finished. Of course, for a perfect waiting experience, you should SET REPROCESS correctly.
A SQL COUNT just takes a snapshot of the buffered data before the edit.
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von MB Software Solutions, LLC Gesendet: Donnerstag, 22. Oktober 2020 21:24 An: ProFox Email List profox@leafe.com Betreff: 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
-- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
-
Christof Wollenhaupt -
Jürgen Wondzinski -
MB Software Solutions, LLC -
Richard Kaye -
Stephen Russell