.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Advanced T-SQL - want to get rid of a large Cursor

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi all, 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? Thanks- Jason

View Complete Post

More Related Resource Links

Advanced Paging GridView with ASP.NET 2.0/3.5

This is an article about advanced paging GridView with ASP.NET 2.0/3.5. All the HTML, CSS, JavaScript it needs have been embedded into the WizardGridView.dll.

Cursor Functions

A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.


Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Persist large dataset across ASP.NET pages. No database


Can anyone give me suggestions on how to implement the following requirement?

1. A online wizard (step-by step operation) processes user requests across several ASP.NET pages.  The session data is stored in memory until the user submits the request.  Once the request is submitted, a XML file is created and the data are passed to XML.

# Case Study: The parent page has a "select" button.  Click "select" will pop up a child page.  The users can select 1000+ items from a datagrid in the child page.  Once the user clicks "confirm", the selected items in the child page will be populated to the selected item gridveiw in the parent page.  The user can keep selecting more in the child page and the selected items will be appended to the gridview in the parent page.


- What's the best way to extract strings from a text file and convert to a dataset in memory and then present it in a datagrid in UI?

- What's the best way to persist and manipulate a large dataset across pages?  Session? or ViewState? Can it be accomplished by LINQ?

The app doesn't interact with a DB.  Once the request is submitted, it will convert the in memory data to a XML file.  What technology can be used to accomplished this?&nb

A Challenging Question for Advanced user....


Using  dot net How will you release .exe applicattion for 1000 clients.this question was asked by manager but i could not answer him.tell me if you can!!!

How to generate large pdf's?


Hi all,

I am using rdlc reports in my appln. Am trying to generate a huge pdf file which almost consists of 1500 - 2000 pages.
I have used ReportViewer and am adding datasource (Generic collection) to report. But am getting system out of Memory exception as it is a huge file.
Can any one suggest me how to generate large pdf files in asp.net c#

Lessons Learned: Optimizing A Large Scale Software + Services Application


Udi Dahan explains how his team identified and overcame unforeseen problems while developing a large-scale software + services trading application.

Udi Dahan

MSDN Magazine April 2009

Advanced Basics: The ObservableCollection Class


Ken Getz shows how the CollectionChanged event lets you reflect changes to your underlying data source in your bound data controls.

Ken Getz

MSDN Magazine December 2008

Advanced WPF: Understanding Routed Events And Commands In WPF


See how routed events and routed commands in Windows Presentation Foundation form the basis for communication between the parts of your UI.

Brian Noyes

MSDN Magazine September 2008

Advanced Basics: The LINQ Enumerable Class, Part 2


This month Ken digs deeper into the System.Linq.Enumerable class and shows readers how to perform some magic data operations using System.Linq.Enumerable.

Ken Getz

MSDN Magazine September 2008

Advanced Basics: The LINQ Enumerable Class, Part 1.


Presented here is the LINQ Enumerable class, which allows you to manipulate data in any class that implements IEnumerable(Of T).

Ken Getz

MSDN Magazine July 2008

CLR Inside Out: Large Object Heap Uncovered


This month CLR Inside Out reveals the secrets behind the Large Object Heap-what's there, when it's released, how the garbage collector handles the objects there.

Maoni Stephens

MSDN Magazine June 2008

Silverlight: Building Advanced 3D Animations with Silverlight 2.0


Animating with Silverlight is easier than you think. Here we create a 3D app that folds a polyhedron using XAML, C#, and by emulating the DirectX math libraries.

Declan Brennan

MSDN Magazine April 2008

Advanced Basics: Office 2007 Files and LINQ


LINQ to XML and the Microsoft SDK for Open XML Formats simplify access to the parts of a 2007 Office system Open XML document when retrieving or modifying data, resulting in shorter, less complex code.

Ken Getz

MSDN Magazine March 2008

Editor's Note: A Large Release Wave is Headed your Way


This month Howard Dierking discusses the coverage of Windows Server 2008, SQL Server 2008, and Visual Studio 2008.

Howard Dierking

MSDN Magazine Launch 2008

Toolbox: Advanced Unit Testing, Object Mocking, Profiling Your Apps and More


This month we look at advanced unit testing, object mocking, profiling, and lots more.

James Avery

MSDN Magazine January 2008

Advanced Basics: Reflection and Generics


This month Advanced Basics flaunts the power of generics and reflection and shows how you get more flexible and efficient development by combining the two.

Ken Getz

MSDN Magazine January 2008

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend