What is the fastest way to select an entire table on SQL Server?

Question

I am writing an application that reads an entire table, does some processing, and then writes the received data to another table. I use the SqlBulkCopy class (.net version of "bcp in"), which inserts very quickly. But I can not find an effective way to select data in the first place. there is no .net equivilent "bcp out", which seems strange to me.

I am currently using select * from table_name . It takes 2.5 seconds for a presentation to select 6,000 lines ... and only 600 ms for loading insert the same number of lines.

I would expect data selection to always be faster than insertion. What is the fastest way to select all rows and columns from a table?

---

Answers to qeustions:

• I counted my choice for 2.5 seconds. First, when starting my application and starting sql trace. the second made the same request in SSMS. Both retreated about the same result.
• I am reading data using SqlDataReader.
• No other applications use this database.
• My current processing takes less than 1 second, so the reading time of 2+ seconds is relatively long. But mostly I'm interested in performance when scaling up to 100,000 rows and millions of rows.
• Sql Server 08r2 and my application are running on my dev machine.
• Some data processing is set based on, so I need to have the entire table in memory (to support much larger datasets, I know that this step will probably need to be moved to SQL, so I only need to work on each row in memory)

Here is my code:

 DataTable staging = new DataTable(); using (SqlConnection dwConn = (SqlConnection)SqlConnectionManager.Instance.GetDefaultConnection()) { dwConn.Open(); SqlCommand cmd = dwConn.CreateCommand(); cmd.CommandText = "select * from staging_table"; SqlDataReader reader = cmd.ExecuteReader(); staging.Load(reader); } 

Answer 1

select * from table_name is the easiest, easiest and fastest way to read an entire table.

Let me explain why your results lead to the wrong conclusions.

• Copying the entire table is an optimized operation that requires cloning old binary data into a new one (in most cases, you can perform a file copy operation in accordance with the storage mechanism).
• Record is buffered . The DBMS says that the record was written, but in fact it has not yet been completed if you are not working with transactions. Disk operations are usually delayed.
• A table query also requires (as opposed to cloning) adaptation of data from a binary-stored layout / format to a driver-specific format that is ultimately read by your client. It takes time.

Answer 2

It all depends on your hardware, but most likely your network is a bottleneck.

Aside from the fact that your query just reads the columns that you are actually using, making the selection is as fast as it will be. Caching is involved here when you execute it twice in a row, the second time it is much faster because the data is cached in memory. run dbcc dropcleanbuffers to check the effect of caching.

If you want to do this as quickly as possible, try implementing code that performs processing in T-SQL, so it can directly work with data directly on the server.

Another good tip for speed tuning is a table that reads on one disk (see file groups) and a table written on another disk. Thus, one disc can read continuously, and the other can write continuously. If both operations occur on the same disk, the disk heads continue to move back and forth, which seriously reduces performance.

If the logic of your record cannot be used for T-SQL, you can also take a look at the SQL CLR.

One more tip: when you select * from the table, use datareader if possible. Thus, you do not materialize all this primarily in memory.

GJ

Answer 3

It is a good idea to usually include column names in the selection list, but with today's RDBMS this will not make much difference. You will only see the difference in this question if you restrict the selected columns. Generally speaking, including column names is a good practice. But for the answer, it seems that the selection is really slower than the insert in the script you are describing and yes select * from table_name really the fastest way to read all the rows and columns from the table