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


Top 5 Contributors of the Month
Imran Ghani
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.

DECLARE CURSOR in Transact-SQL

  
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.

Questions:

- 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


Categories: 
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