Re: Slow File Load Through ODBC Driver
From: David L. Jacobson (tornadogroup_at_hotmail.com)
Date: 09/29/04
- Next message: sherry: "large cursor"
- Previous message: Andrew Howell: "CANCEL in SCAN..ENDSCAN changes selected work area to the one being SCANned"
- In reply to: Lee Mitchell: "RE: Slow File Load Through ODBC Driver"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Sep 2004 09:00:23 -0400
Good morning, Lee
Thank you very much for answering my post. I have spent the last several
days diagnosing the problem and wanted to give you complete feedback on your
recommendations.
I tried the sys(3050,1) recommendation to change the foreground buffer. By
watching the performance tab in task manager, I could see that reducing the
buffer cache from 772 Megs (amount determined by the system) to 132Megs
(optimal amount I found by stepping up from 50) did release real (vice
virtual) ram immediately to fox when fox generated a large select statement.
Now here's where things get screwy. I am running the same program on two
machines: the first P4/2.0Ghz with Windows 2000 OS; the second P4/3.0Ghz HTT
with Windows XP. On the smaller NT box, this worked like a champ. Transfer
of query from MySQL server to Fox cache completed in about 51 seconds
repeatably. On the larger XP box, the changes had no effect on query return
speed--same exact operation took 10 minutes, 28 seconds (tried it 20 times
with different caches, different XP performance priorities, etc.) all
resulted in the exact same slow performance time (10 times slower than the
smaller 2000 box). The sys3050 made the ram instantly available to fox, but
the records still loaded with a completely steady (no stopping of the record
counter like in the knowledgebase note). I tried upgrading the larger box to
XP professional (suspecting kernel memory handling) -- no effect; removed
one of the dimms (brought memory down to single-channel 512MB, like in the
smaller box) -- no effect. The only clue I have to the problem is that on
XP, the csrss.exe process gets equal clock cycles to fox on the query load,
and on the 2000 box csrss.exe gets no cycles.
At this point, my only guess is that we had two problems--your note fixed
the first. The second problem seems to revolve around how the XP kernal
allocates memory for a cache to cache transaction. I looks like XP is giving
it up in only 64kB chunks and I have no idea how to change this. At this
point, my only options appears to be rebuilding the larger machine on the
older 2000 OS. Do you have any other ideas before I wipe slick and reload?
Thank you again for all of your help.
David
"Lee Mitchell" <Leemi@online.microsoft.com> wrote in message
news:a0E5w1loEHA.404@cpmsftngxa06.phx.gbl...
> Hi David:
>
> Have you tried optimizing VFP's internal memory usage with the SYS(3050)
> function? I think VFP is paging out to the hard disk as it uses virtual
> memory. If you limit VFP to use RAM only, you may see a performance
> improvement. See this article for more info on the SYS(3050) function:
>
> 176483 PRB: Large Amounts of RAM Seem to Process Data Slowly
> http://kb/article.asp?id=Q176483
>
> I would start with 50000000 as the third parameter and test the app. If
it
> is stil slow, increase the number by 10000000 and test again.
>
> I hope this helps.
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Sincerely,
> Microsoft FoxPro Technical Support
> Lee Mitchell
>
> *-- VFP9 Public Beta Now Available!! --*
> Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/
>
> *-- VFP8 HAS ARRIVED!! --*
> Read about all the new features of VFP8 here:
> http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
> Purchase VFP8 here:
> http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518
>
> Keep an eye on the product lifecycle for Visual FoxPro here:
> http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
> - VFP5 Mainstream Support retired June 30th, 2003
> - VFP6 Mainstream Support retired Sept. 30th, 2003
>
> >Hi gang,
>
> >I am running VFP 8.0, Windows, on several different Pentium Machines
> >(2.0/3.0 HTT). I am using Fox as the client/presentation layer to a
MySQL
> >4.0 server, via a local view (actually a local view of a remote view of
the
> >MySQL table) using the MySQL 3.51 ODBC connector. Server table is
300,000
> >rows/39 Megs and I need to return the entire table as a result set.
>
> >Here's the problem: when the view is first activated (local or remote or
> >both) by Fox, I can see MySQL return the result with 5 seconds (also
tested
> >sql statement directly in MySQL), but Fox takes 10 minutes to load the
view
> >through ODBC. I can see Fox grabbing RAM and throwing it to a local Fox
> >table cache. Once the view is in the data session, it is lightning fast,
> >but if I close the Fox database (with the connection) we are back to the
10
> >minute load. In any case, the initial load is much slower than I would
> >expect, since best case this is a RAM to RAM transaction and worst case a
> >DISK to RAM.
>
> >It feels like there is something unoptimized in the way Fox is putting
the
> >file into cache, but I can't figure out where to tune it.
>
> >I have ruled out:
> >Tuning the ODBC data source parameters (no effect)
> >RAM (1 gig dual-channel; only 400 Meg of physical in use)
> >CPU (does the transaction at 20%-30% CPU util)
> >PROCESSOR/OS (same result in Win2000 and WinXP/same result on P4/2.0 and
> >P4/3.0)
> >VFP 8.0 (loaded server file into straight Fox table--opens and loads in
> >seconds)
> >remote vs. local view (remote view provides first view in seconds, but
> still
> >loads file to RAM in the background-more slowly in fact-and the table is
> not
> >fully accessible)
>
> >WHAT AM I MISSING?
>
> >Thanks in advance for your help!
>
>
- Next message: sherry: "large cursor"
- Previous message: Andrew Howell: "CANCEL in SCAN..ENDSCAN changes selected work area to the one being SCANned"
- In reply to: Lee Mitchell: "RE: Slow File Load Through ODBC Driver"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
