Greetings Sql Server 2005 SSIS gurus!



In migrating data into Sql Server from flat file sources I've found SSIS to
be incredibly fast. i.e. 300,000 records + in less than a minute. However,
now I need to extract some complex data out of Sql Server ...



In an attempt to make things a little easier, I created a view from the Sql
Query below.



All of the tables are indexed on wr_id and the license table is indexed on
license_nbr.

Select * from wl_view_gwwi_well_log ORDER BY wl_id takes about 49 seconds to
complete with SMS.



However, when I try to load this view into SSIS using an OldDb Data Source
it takes 4+ MINUTES to load the 1st 4800 records. Arrrrrrggggg. That
means it'll take about 5.5 HOURS to extract all 380,000 records into a flat
file. Not good.



Can anyone tell me what's causing the tremendous slow down? Looking at
taskmgr shows the CPU's basically idle. I can't figure what's going on to
make this thing so incredibly SLOW.



Any help / guidance would be greatly appreciated. I have to do this extract
every night along with others to keep the old legacy system running while we
finish our migration to Sql Server.



Thanks in advance!



Barry

in Oregon





The view is based on the following Sql Query.



SELECT

wl_well_log.wl_id,

wl_well_log.wl_county_code,

wl_well_log.wl_nbr,

wl_well_log.wl_version,

wl_well_log.name_last,

wl_well_log.name_first,

wl_well_log.name_company,

wl_well_log.street1,

wl_well_log.city,

wl_well_log.state,

wl_well_log.zip,

wl_well_log.owner_well_nbr,

wl_well_location.qtr40,

wl_well_location.qtr160,

wl_well_location.sctn,

wl_well_location.township,

wl_well_location.township_char,

wl_well_location.range,

wl_well_location.range_char,

wl_well_location.tax_lot,

wl_well_log.startcard_nbr,

wl_well_log.complete_date,

wl_well_log.post_static_water_level,

wl_well_log.post_static_water_level_date,

wl_well_log.depth_drilled,

wl_well_h2o_zone.psi,

wl_well_log.depth_first_water,

wl_well_log.completed_depth,

wl_well_log.special_standards,

wl_well_log.first_entry_date,

wl_well_log.first_entry_userid,

wl_well_log.start_date,

wl_well_log.received_date,

wl_well_log.bonded_license_nbr,

wl_license.name_last,

wl_license.name_first,

wl_license.name_company,

wl_well_log.temperature,

wl_well_log.lab_analysis,

wl_well_log.temperature_unit,

wl_well_h2o_zone.to_depth,

wl_well_h2o_zone.h2o_zone_date,

wl_well_location.lot,

wl_well_location.block,

wl_well_location.subdivision,

wl_well_location.street_of_well,

wl_well_log.well_tag_nbr,

wl_well_log.geologist_engineer,

wl_well_location.latitude_dec,

wl_well_location.longitude_dec,

wl_well_log.use_of_hole,

wl_well_log.enforcement_flag,

wl_well_log.work_abandonment,

wl_well_log.work_alteration,

wl_well_log.work_conversion,

wl_well_log.work_deepening,

wl_well_log.work_new,

wl_well_log.type_of_log,

wl_well_log.abandon_start_date,

wl_well_log.abandon_complete_date,

wl_well_log.abandon_start_date_est,

wl_well_log.abandon_complete_date_est,

wl_well_log.drill_rotary_air,

wl_well_log.drill_rotary_mud,

wl_well_log.drill_cable,

wl_well_log.drill_auger,

wl_well_log.drill_push_probe,

wl_well_log.drill_hand_auger,

wl_well_log.drill_hollow_stem,

wl_well_log.drill_other,

wl_well_log.use_community,

wl_well_log.use_domestic,

wl_well_log.use_industrial,

wl_well_log.use_injection,

wl_well_log.use_irrigation,

wl_well_log.use_monitoring,

wl_well_log.use_thermal,

wl_well_log.use_livestock,

wl_well_log.type_of_hole,

wl_well_log.type_of_hole_other,

wl_well_log.affiliation_name,

wl_well_log.bonded_date_signed,

wl_well_log.unbonded_date_signed,

wl_well_log.geologist_date_signed,

wl_well_log.other_license,

wl_well_log.company_job_nbr,

wl_well_casing_liner.diameter,

wl_well_log.other_first_name,

wl_well_log.other_last_name,

wl_well_log.work_other,

wl_well_location.location_county,

wl_well_log.sc_id,

wl_well_log.second_entry_userid,

wl_well_log.last_updt_userid,

wl_well_log.second_entry_date,

wl_well_log.last_updt_date

FROM wl_well_log

LEFT OUTER JOIN wl_well_location ON wl_well_location.wl_id =
wl_well_log.wl_id

LEFT OUTER JOIN wl_well_casing_liner ON wl_well_casing_liner.wl_id =
wl_well_log.wl_id

LEFT OUTER JOIN wl_well_h2o_zone ON wl_well_h2o_zone.wl_id =
wl_well_log.wl_id

LEFT OUTER JOIN wl_license ON wl_license.license_nbr =
wl_well_log.bonded_license_nbr

Hi Barry,

Are tables "wl_well_log" and "wl_well_location" also Text File on your
disk or remote server? If they were Text File, can you post some sample
records so that we could build a test to see what will happen?

Luke

Luke,

Problem resolved!

Had a "brain storm" and decided to try the SMS Export Wizard on the view. I
normally avoid wizards and prefer to 'roll my own' scripts. May have to
rethink that tendancy when it comes to Sql Server 2005.

The Wizard's resulting SSIS package worked Great! Simply amazing. Built the
flat file quickly and efficiently.

The difference between my SSIS package and the Export Wizard's package was
that the wizard chose an "Sql command" Data Access Method (and simply said
SELECT * FROM view) where I chose the "Table / View" Data Access Method.

The type of Data Access Method selected apparently makes a HUGE difference
in response time.

Thanks for your time and patience. Its been very much appreciated!!

Barry
in Oregon


"Luke Zhang [MSFT]" <lukezhan (AT) online (DOT) microsoft.com> wrote

Thank you for the information. Have a good day!

Luke