Re: C/VB dll for "browsing" large datasets? by ImagineCorp
ImagineCorp
Wed Jan 17 20:29:52 CST 2007
Hello CJ;
Marc is absolutely right.
We did a massive project where the total database size is over 80GB with
tables containing 24+ million records each. ALL VFP. & all under 2GB. Why in
Gods name are you allowing users to retrieve such large sets of data. Not
only is it slow its silly. For users wanting to view "larger" clients
invoices, narrow the criteria down by dates etc to reduce the size of the
query. We did it and the users love it. Nobody & Nobody has the time or the
desire to view that many records (based on experience). You could also
automate some of the tasks the users perform on these large datasets. This
involves work... Bring in data, cutting it up into various temp tables,
further refining then presenting to the user... Your boss may make you a
partner{G}
--
Mohammed
www.imaginecorp.com/whatwedo.htm
"Marc Miller" <ctefsd_sys@hotmail.com> wrote in message
news:OPoOwxnOHHA.3944@TK2MSFTNGP06.phx.gbl...
> CJ<
>
> I think you have hit on the right idea. Although FoxPro is an amazing
> database,
> the size of your data probably requires a database server product and
> hardware
> that can better accomodate it. SQL Server is a great choice and
> controlling the
> fetching from FoxPro will improve the thru-put.
>
> However, I still also would question, as a former accountant, what on
> earth your users
> would be doing with over 2GB of data in their hands! I would think that
> its
> the users machines that may be doing some of the 'spewing'. I once took
> some
> time from my schedule to give SQL classes for some of my users. After
> that
> they were able to narrow down their criteria and build much more
> effficient queries
> with result sets that were more meaningful. I would even suggest that
> they are
> pullling down huge amounts of data and then summarizing it using Excel or
> MS Access
> when they could actually be doing this up front.
>
> Also, have you normalized the data? Would it be possible to archive some
> of the
> data into tables representing fiscal or calendar years? There probably
> many things
> you can do with the data on the back-end to improve performance and
> prevent
> mad accountants from grabbing all the data the company has ever generated.
>
> Good luck,
> Marc Miller
>
>
>
>
>
>
> "Curious Joe" <joebob.johnson@gmail.com> wrote in message
> news:1169066401.579423.195450@s34g2000cwa.googlegroups.com...
>>
>> Yes.
>>
>> The users have to option to run any query against the SQL 2005 server
>> they want. Most of the queries are simply pulling a vendor to work.
>> Maybe they will pull all of the EDI Invoice detail for vendor 123 so
>> they can analyze all invoices from that vendor. In many cases (on our
>> larger clients), these single vendors are >2GB. Once they have the
>> vendor "pulled" they do many things to the file. Add fields, sort,
>> index, run reports, do summaries, whatever their heart desires.
>>
>> Maybe I need to investigate keeping all of the data stored on the SQL
>> server and use VFP to retrieve 10,000 rows at a time. I have never
>> done that so there would be a learning curve, but it may be the best
>> answer if it is even possible the way I am imagining it.
>>
>> Any suggestions are appreciated. I hope I explained my situation well
>> enough to get good feedback. If any more information is needed, please
>> let me know and I will try to explain.
>>
>> Thanks,
>>
>> CJ
>>
>>
>> On Jan 17, 1:25 pm, "Marc Miller" <ctefsd_...@hotmail.com> wrote:
>>> CJ,
>>>
>>> I assume you are referring to tables and views outside of FoxPro since
>>> the
>>> table size limite in FoxPro is 2GB.
>>>
>>> You say 'browse'. Does your application not control the amount of
>>> records
>>> they can fetch at a time?
>>>
>>> I guess I would need a better understanding of how they are viewing the
>>> data
>>> and why they would need to view so much data at a given time.
>>> Otherwise, it should be entirely manageable within a FoxPro app. to
>>> control
>>> the 'spewing'.
>>> FoxPro has full capabilities and methods for retrieving and displaying
>>> data.
>>>
>>> Can you give us a little more background?
>>>
>>> Marc Miller
>>>
>>> "Curious Joe" <joebob.john...@gmail.com> wrote in
>>> messagenews:1169064535.718258.77430@q2g2000cwa.googlegroups.com...
>>>
>>> >I am facing a problem that has become a major issue of late with my
>>> > users. They are frequently trying to "browse" tables and views that
>>> > are larger than 2GB. This is causing the application to spew and then
>>> > I get complained to.
>>>
>>> > I am now considering (and looking for comments from the best user
>>> > community for the best application ever) creating a C++ or VB
>>> > "external" program that will use alternate methods to retrieve and
>>> > display the data.
>>>
>>> > If anyone has encountered this problem and come up with a better
>>> > solution or has any thoughts on a workable solution, please let me
>>> > know
>>> > as I am at a loss right now. The only thing I know for sure is that
>>> > our clients are getting bigger so this problem will arise more and
>>> > more
>>> > as time goes on.
>>>
>>> > Thanks,
>>>
>>> > CJ
>>
>
>