Drastic performance degradation in Data Flow from SQLS => DBF's -- IDEAS PLEASE
-
Source is a custom SELECT (5 tidy columns) from SQL Server. Destination is a DBF file living on the same server's hard drive.
When I first launch the flow, it appears to be flowing 10K rows every 1 second which *could* suffice for production, but immediately starts to falter. By the time it gets to 100K rows, it's taking 5-8 seconds every 10K rows, and by the time we get to 300K records, it's taking 20 seconds for 10K rows.
There are no other significant processes happening on the box. I've also included the resource monitor's view of the disk load which seems light to me. CPU and memory are both un-taxed.
I've already tried using "Fastload" on the destination objects, but I don't think that setting is defined for the VFP OLEDB driver.
Screenshot shows 3 parallel flows, but I've also tried with just 1, no WHERE, no ORDER BY, various drives... all have the same issue.
I ultimately need to get 260MM rows into umpteen DBF files (Foxpro 2G/file limitation), so this is a major road block right now.
Does anyone have experience with exporting a massive set of data to DBF's with reasonable performance? How'd you do it?!?! Any insight as to what's happening here will help me, don't hesitate to respond.
Thanks SSC!
--------------------------
PS --> just added the perfmon screeny... looks like there are quite a few page faults happening. Does this help? Other counters you'd like to see?
-
Greg J (1/5/2012)
just added the perfmon screeny... looks like there are quite a few page faults happening. Does this help? Other counters you'd like to see?Yep, page faults = using the swapfile for memory. Your memory IS taxed. If this is all on your local drive because you need to play in the sandbox, then your drive is being used for both read from SQL, write to DBF, and memory swapfile. You're not getting a true test here if that's the case.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Stick with me Kraig...
I'm attaching a screeny of the resource monitor's take on memory which is why I originally said it's not taxed.
Also, I saw on the "output" tab, this message:
"Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console."
How do I rectify the situation you outlined? "Not a true test"... ?? How to I en-true this?
-
Greg J (1/5/2012)
Stick with me Kraig...I'm attaching a screeny of the resource monitor's take on memory which is why I originally said it's not taxed.
Also, I saw on the "output" tab, this message:
"Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console."
How do I rectify the situation you outlined? "Not a true test"... ?? How to I en-true this?
Okay, I should have looked at the images too, sorry.
Your SQL Server and the DBFs, if I'm reading that right, are on different Physical identifiers for the disks, even though they might be the same past the drivers. This almost looks like you're running it on a server not your desktop, so I may decide to foot-in-mouth my 'real test' comment. Everything you described sounded like a desktop sandbox, not a dev server environment.
That is a LOT of page faulting. Resource monitor is lying to you, something's spilling to disk and it's spilling HARD. That could simply be a bad application allocation too, especially if it's a 32bit app engine and it filled up its expected 4GB in RAM. Um. Hrm. Something's up...
I need to think a bit on it, I'm not sure what's your next approach from this side of the forum. I'm missing a lot of the normal things I'd use to base my decision on and I don't want to hand you a flock of wild geese to chase.
To deal with that memory error do an RDC over to the box in question and review perfmon there.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
All screenshots were taken from an RDC on the server.
Machine is 64bit, and this package has "Run64BitRuntime" set to False (because I've never connected to a DBF successfully in 64bit).
I just tried a barrage of tweaks, all to no avail: logging mode, isolation, transaction, optimized mode, single dest.
Dead in the water here. Taking a step back... can I do an OPENROWSET to insert to the DBF table (file) or some other similar magickry?
-
I just thought of something. It's possible that your FoxPro driver is 32bit and is holding the transaction information, so it's choking, instead of anything that you can do with SQL Server. Have you looked into the DBF drivers?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Greg J (1/5/2012)
All screenshots were taken from an RDC on the server.Then I'm not sure why that error is popping, that's annoying though.
Machine is 64bit, and this package has "Run64BitRuntime" set to False (because I've never connected to a DBF successfully in 64bit).
Ah hah! So, you've got a 4 gig limit on the entire works before it spills. Ayup, this could definately be your problem.
Now, out of curiousity, what's your destination type? OLEDB driver? Can you set your commit size to 10000 rows or so?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
The maximum commit size and row per batch settings are only available on OLE DB destination objects with "Fast Load" checked, no? This doesn't seem to be an option for a DBF destination. Is there some other way to effect that setting?
I've included a screenshot of all the properties available to me from the DBF connection object. Do any of these look like they'd be worth screwing with?
Thanks, this is a tough one for me. I'm spitting distance from the finish line here, and quite frustrated on this issue.
----------------------------
ALSO: The DBF files can't exceed 2G each (inherent limitation of FoxPro?), and that wouldn't happen until the 12 million row mark or so, and the slow down begins as soon as the package is launched, so I STILL don't think there's a memory issue.
-
I've never seen a DBF destination object, these are the standard ones:
As to the properties, even with the OLEDB object they're not all visible in that properties window. You'll usually have to rt-click/properties to get at the information. You're correct that it's only available for Fast Load with the OLE DB object though. It's rare you'd use it otherwise however, so I had made an assumption.
Where did you download the DBF object from?
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Sorry for not clarifying that... The DBF I'm trying to populate is a FoxPro "free table".
I've been trying the Microsoft VFPOLEDB.1 driver which seems to be the most recommended out there. Today, I tried using a Microsoft ODBC driver for FoxPro to no avail (MDAC 2.8 won't install on 64 bit OS). I also tried Advantage ODBC drivers as well as Advantage OLEDB drivers for FoxPro. (http://devzone.advantagedatabase.com/dz/content.aspx?key=20&Release=16). I got the Advantage ODBC to show up in SSIS (must use User DSN, not System?), but it gave an error when used. The OLEDB driver from Advantage never even showed up in SSIS.
In all cases, I tried a TON of permutations in property setting with the driver, connection object, and destination object. I tried moving the target .dbf to a 32 bit machine for the load. I tried voodoo, chanting, levitation, and kimchee. NOTHING got rows/second performance to improve.
At this point, I've thrown in the towel. I'm starting to re-architect this portion of the solution as a Data Flow to a tab delimited flat file. From what I'm told, FoxPro has a native "APPEND" command that can make use of this format.
It took over an hour to populate the DBF with 1 million records. I'm getting all 260 million records into the flat file in < 12 minutes.
Sincere thanks for your help Craig. I'll watch this thread for future posts.
-
Greg J (1/6/2012)
Sincere thanks for your help Craig. I'll watch this thread for future posts.Sorry I can't be more help. I don't have anything Foxpro'ish locally to even attempt to screw around with helping you find a workaround. I'm glad you're able to locate one. Good luck!
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply