CODEWITHSUNDEEP
sql-server-2005ado.net
Bala
Bala

Reputation: 331

Select 100K+ records quickly

I need to select some 100k+ records from a SQL table and do some processing and then do a bulk insert to another table. I am using SQLBulkCopy to to do the bulk insert which runs quickly. For getting the 100k+ records, I am currently using DataReader.

Problem: Sometimes I am getting a timeout error in DataReader. I have increased the timeout to some managable number.

Is there anything like SQLBulkCopy for selecting records in a bulk batch?

Thanks!
Bala

Upvotes: 2

Views: 261

Answers (2)

Blorgbeard
Blorgbeard

Reputation: 103467

A quote from this msdn page:

Note

No special optimization techniques exist for bulk-export operations. These operations simply select the data from the source table by using a SELECT statement.

However, on that same page, it mentions the bcp utlity can "bulk export" data from SQL Server to a file.

I suggest you try your query with bcp, and see if it's significantly faster. If it's not, I'd give up and try fiddling with your batch sizes, or look harder at moving the processing into SQL Server.

Upvotes: 0

gor
gor

Reputation: 11658

It sound like you should do all your processing inside sql server. Or split data into chunks.

Upvotes: 4

Related Questions