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]