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 ?