I am importing & processing large amounts of data (100MB+ xml files), and the biggest bottleneck is a step involving one huge cursor.
In this step, I have say 300,000 rows, that I need to split space-delimited values into their own individual rows (10 Million rows total after the 300k are fn_split).
Presently I am loading them all into a Cursor, fetch next into a group of local vars, then combine fn_split with insert. It takes 10-15 minutes to do.
Meanwhile, my server has 24 processors (cores + hyperthreading), and only ONE is in use due to this cursor. Talk about a waste of resources!
I have tried many alternatives - @id=1 WHILE @id<max BEGIN select @values=cols fn_split Insert @id=@id+1 END, and they all seem to take 2-3 times as long to execute as the Cursor. (the While loop also uses less processing %age than the cursor!)
There has to be a way to optimize this thing... I would love to find a way to engage all 24 processors at 40-60% to crank through this thing, it'd be done in 2-3 minutes.
Anybody have ideas?
View Complete Post