SQL Server Import and Export Wizard - export slow after upg to SQL

[LisaM]

We are using SQL Server Import and Export Wizard to export from a view to a
flat file. This export worked in under 20 seconds in SQL Server 2005 SP3 but
after we upgraded to SQL Server 2008 SP1 the same export will take up to 2
hours or more. Also it appears to dominate one CPU out of 8 on that server
for the duration of the export.

This particular view is rather complex with a right outer join, a where
clause and order by clause. The view has 40560 records.

A similar export of a simple view processes quickly (a view created by a
simple select statement). Tables export quickly too. Furthermore, if you
copy the select statement (including the joins, the where and order by
clauses) and paste it into the query section of the SQL Server Import and
Export Wizard, the export processes in under 20 seconds!! Finally, if you
copy the database to another SQL Server 2008 SP1 server and run the same
combination of tests, the results are the same.

All of this suggests to me that the problem lies with SQL Server Import and
Export Wizard (in SQL Server 2008) - in the way that it is parsing this
particular view when left to it's own devices.

Microsoft, can you please explain why the SQL Server Import and Export
Wizard in SQL Server 2008 is behaving this way?? Do you have any suggestions
as to how I can overcome this problem?

[warrena...@gmail.com]

I have been looking for an answer to this same question in various forums with no luck. I have encountered the same issue. Exporting 2.5 million records from a view took 3+ hours. Take that same view and SELECT INTO another table took 8 seconds then to export that data to a flat file took 15 seconds. Also took the query from the view and use the import/export wizard with this query and it took 20 seconds to export to a flat file. Why does the view cause this incredible bog down? In my case it exported about 1.9 million records in 3 minutes then started slowing down to a crawl for the next 600K records.

Anybody? Anybody? Bueller? Bueller? Bueller?