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 <juer...@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-boun...@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] _______________________________________________ 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/cajidmyjm-2za6qmjrqzgqo9n7csfhpyhwsvoxbwbezga_bb...@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.
