Re: C/VB dll for "browsing" large datasets?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxx> wrote in message
news:OPoOwxnOHHA.3944@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx> wrote in message
news:1169066401.579423.195450@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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_...@xxxxxxxxxxx> 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...@xxxxxxxxx> wrote in
messagenews:1169064535.718258.77430@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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





.

Relevant Pages

  • Re: VFPOLEDB
    ... table first to find out what data to retrieve, retrieves the data, and then ... Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP ... > if the database is larger it is slow because the entire ... > a subsequent query is fast - because the data is loaded - but after some ...
    (microsoft.public.data.oledb)
  • query to seperate a last name from a name field containing LastNam
    ... I retrieved a database created in FoxPro. ... in seperate fields. ... retrieve the LastName only? ...
    (microsoft.public.access.queries)
  • Re: Help - Timing Logic
    ... from the database ... ... now you either need to incorporate a locking procedure ... ... opposed to building the transaction in the code. ... implement as above locking only the records you retrieve / update - need ...
    (microsoft.public.dotnet.languages.vb)
  • Re: OT: MySQL versus PostgreSQL and database design
    ... to retrieve all the events at once. ... can make that a primary key in the database. ... likely to want to retrieve all at once, without any sorting. ... different the event types are -- if they've all got exactly the same ...
    (rec.crafts.metalworking)
  • Re: DOCX FILE CORRUPTED WHEN RETRIEVED FROM SQL2005
    ... I am facing the same problem and would appreciate your reply on the same as it has become a show stopper for me now. ... My Code is given below to retrieve the docx content from SQL 2005 whose data type is image. ... Here is the code where i insert the file into database. ... when I store these files to the server, using the code below and similar ...
    (microsoft.public.office.developer.vba)