Thanks Paul, I'll give it a try and test it out.

Paul,

I always had this question in mind, isn't there a performance penalty in using this approach on the DB side. Imagine if there are several thousand records, SQL Server still...

And with SQL Server 2008 it is even better. It's getting there, just have to wait a little more i think before it is claimed as the best ever ETL...

Brilliant article and the timing is so right. I have had code being sent by App Developer today to develop a paged recordset using stored procedures. Just began to work...

It's doing all Clustered index seek. Tried all possible optimization. Also tried replacing the @table variable to a #temp table..didn't help.

My question is there a way to tell SSIS to...

The time to run the SP definitely is longer than it should have taken, almost double the time (~6 minutes).

Thanks,

Tried both, thru BIDS and Job, same results.

Is there an option to turn off pre-validation? How to do that?

Thanks,

Yes, i tuned the proc execution down to 3 minutes from 20 minutes, yet the package takes longer to execute.

How about this then... Load all the records to a staging table with IDENTITY column. Using T-SQL Loop thru the records using IDENTITY range. This will be more efficient and...

But why do you need to use a For each loop? As mentioned by stewartc mentioned use batch size and commit size of 10,000 with TABLOCK option in the OLEDB...

Very intelligent question. I got this wrong though :(. The second part of the question tricked me into thinking that the SQL Server will have an efficient way to retrieve...

Agreed da-zero. But table variables are meant to hold smaller datasets and having an index doesn't really benefit here and as regards truncation of the table variable, it's not required...

Thanks for the response Lynn. I agree with you, i am aware of partitioning although i wanted to see if i could effectively use partitioned views in this scenario.

I guess...

The table is a name-value pair table has only 3 columns:

[ID] -- NOT an identity, but a FK to another table in the db.

[Name]

[Value]

Primary key is [Object_ID] and [Name].

We are...