William Sanders / EFG <> wrote:
> Point by point, but not in the original order as presented:
>
> 1. "The problem is that they have screens that pull back all the
> data from certain key tables that are pretty large."
> ALL the data? so the sql query code looks like:
> select * from tablename.
> Always bad form , imo/ime, in any client/server environment. Even if
> it's a KEY table, it sounds like there needs to be some other
> normalization of the data, or at least shift off to doing a
> parameterized query that includes a 'where' clause, and the item in
> the where clause is a hit on a column that is indexed.
>
> 2. "but they say when they had the FoxPro database, the app would
> simply open a pipe to the data and they could bring back the first
> screen full of data in a couple of seconds, and then Foxpor
> [sic]would leave the pipe open so it could smootly scroll
> forward/backward throught the results without having to issue
> another data request."
> Yup - thats the beauty of the speed of data access with native vfp
> tables.
> Doesn't exist in SQL SERVER, sorry.
>
> 3. "Therefore, the application is not built with any form of paging
> capability"
> Ah - but it could be! With the same remote views into sql server,
> you can set up the connection properties from vfp to not pull
> everything, and limit each pull to 100 to 200 records. Then, as the
> pseudo-cursor is moved (ie- go bottom) , VFP will force the odbc
> connection to get to the end. There is a time lag, of course, but NOT
> for the initial VFP form/screen load.
>
> 4. "The proplem [sic] they've run into now that SQL Server is the
> database is that in order to simulate the scrolling capability of
> the FoxPro based tables, the middle layer has to pull all the data
> into a cache area first."
> OK - so now comes 'the rub' right ? This is where the design change
> comes into play.
>
> Going forward.
>
> The set of tables that need to be 'replicated' - How often does this
> information change ? How is each record uniquely identified? Have you
> done any matching with primary ID's in the SQL table with primary
> ID's in the VFP table?
>
> IFIW - I'd take a 3 shot approach to your 'replication' process,
> could be done in stored procedure, then migrated over to something
> else.
>
> 1. from sql server to VFP table via oledb
> - get a sql cursor (call it diff_a) of the primary id's in the vfp
> table 2. in sql server
> - do a diff on the diff_a cursor with the sql table you wish to push
> out, getting only the primary id's, creating a new sql cursor (again
> storing only the primary id's), call it diff_b
> - create a sql cursor with everything in it that matches the primary
> id's stored in diff_b 3. from sql server to vfp table via oledb
> - simply append to the vfp table, the contents of the diff_b sql
> cursor.
>
> This will negate the error you run across with the vfpoledb driver
> having the parameterized problem.
I have presented most of the same topics you have to the poor consultant who
needed some help. I also recommended that they do a go bott after they
start the pull from SQL.
Company had merged with a another and another, so new ownership is doing a
rewrite in .NET but they still need to operate today.
Owners won't "fix" FP either said consultant. Where have I heard something
like that before.
Stephen Russell
DBA / .Net Developer
Memphis TN 38115
901.246-0159
"A good way to judge people is by observing how they treat those who
can do them absolutely no good." ---Unknown
http://spaces.msn.com/members/srussell/
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.441 / Virus Database: 268.17.39/686 - Release Date: 2/14/2007
7:54 AM
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** 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.
