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 <
mbsoftwaresoluti...@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 <
> > mbsoftwaresoluti...@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-boun...@leafe.com> On Behalf Of Richard
> >> Kaye
> >>> Sent: Thursday, October 22, 2020 3:28 PM
> >>> To: profoxt...@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-boun...@leafe.com> On Behalf Of MB
> >> Software Solutions, LLC
> >>> Sent: Thursday, October 22, 2020 3:24 PM
> >>> To: profoxt...@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/CAJidMY+rgm+DyMzvwWKSEuJvRaXKnC-fk9=j_r=n7fgnug7...@mail.gmail.com
** 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.
