ricardo_chicas (8/20/2010)
Ok lets do this, forget about the original problemwhat I really want to know is for a suggestion on what is the best way to retrieve large amount of data, without blocking tables, without using no log, using a select, when I use oracle I was able to do something like that using a bulk select ( I set the select to retrieve chunks of 10000 rows), I am not sure if that kind of stuff works in sql server, but i want something like that
Thank you
Well if you ask the question like that the only possible answer is that you dont need to do anything special. The application just needs to issue the select query and then consume the returned data as fast as it can.
There will be blocking - another application will not be able to insert data into the table until the application has finished reading all the data.
To make blocking shorter you have to minimize the time used to consume the returned data. You application should just read the data as quicklu as possible without waiting for anything else.
To avoid blocking completely you would have to use snapshot isolation (check BOL) or use the NOLOCK hint in the select.
Your real problem is probably that your application is not reading data fast enough from the SQL server connection.
I have no idea what an Oracle BULK SELECT does.