troubleshooting Question

Insert Performance Issue #10000 ^^

Avatar of T-Virus
T-VirusFlag for Netherlands asked on
Microsoft SQL Server
12 Comments1 Solution362 ViewsLast Modified:
Hi,

please enlighten my mind...

I have SQL Server 2008 RC2 64bit  installed on my Win7 64bit desktop machine.
So i am just f*** around with FlatFile imports, Views and inserting them into Tables.

A Bulk insert from Flat.txt file(250MB 2mil Rows) takes about 3sec rather then the 20min with the usual Insert into. Which is somewhat clear to me, but really 3sec vs. 20min ?

So after the import of the Flatfile into a table, i need to seperate the Columns - so i got this nice little function here... http://pastebin.com/FGFxwPGc -  that does the trick.
-- no there is no other way, the file has different column seperators !

Next - i created a view - so i get myself a nice littel table to work with.
As u might all know it is not the most performant thing to work with view... so i try to copy the stuff into a table.
Which gives me headaches, my god it is taking so incredibly looooooooong - like 30min for 2mil rows.

I thought well maybe the query is the problem, so i quickly ran a test by exporting the content into a csv flat file - which took 5sec. So no it is not!

Why on earth is the Insert so slow?  

I tried SSIS Packages, did performance testing, turned logging off and what not.
Performance wise i am at 8% cpu usage, 30% ram, and database i/o is at around 0,3MB/s.
The HDD is not even used in the slightest way, i can copy movies with 300MB/s while the query is running whit no performance decrease what so ever.

Any ideas ?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros