Efficient way to get 10 million records from SQL

Question

I have a table in SQL Server and I need to export its data into a .CSV file.

Here are the table details:

ProductId: PK, bigint
SupId: varchar(100)
CatalogId: FK, bigint
Price: float

No indexes.

CSV part is done, meaning I have tried out a couple of libraries that can write the resultset to CSV.

Place where I am stuck is that the SELECT * takes a long time to execute.

Till now I don't have any success in running

SELECT * FROM

Now, I know that it's a very bad idea to run SELECT *, I have read quite a few posts.

Is there a way to stream or get the data in parts? This is C# person asking so please excuse the lingo.

So, what should I be reading / learning?

How do I get that kind of data from SQL?

Regards.

Provide your table structure and the current indexes created for it. — Joël Salamin, Sep 16 '14 at 11:00
You should look into the `bcp` utility: http://msdn.microsoft.com/en-us/library/ms162802.aspx. — Gordon Linoff, Sep 16 '14 at 11:07

sumit · Answer 1 · 2014-09-16T11:10:13.660

The bulk copy (bcp) command of Microsoft SQL Server provides you with the ability to insert large numbers of records directly from the command line

 bcp mydb.dbo.mytable out "C:\mytable.csv" -c -T

more : http://msdn.microsoft.com/en-us/library/ms162802.aspx

EDIT

you can use bcp on sql execution as well

bcp "SELECT * FROM mydb.dbo.mytable t1 join mydb.dbo.anothertbl t2 on (t1.id=t2.mytable_id)" QUERYOUT C:\mytable.csv  -c -T

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

That highly depends on

(a) what your table(s) look like,
(b) what kind of indexes you have,
(c) what your query is like, and
(d) what your hardware (servers) supports...

this question is way too broad to answer.

How many rows and what kind of columns does this table have? And do you really need ALL columns (SELECT *) and ALL rows (no WHERE clause) from your table? If you really need everything from that table - there's really no "magic" way to speed up the clustered index scan

but do try using LOCKS . When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.

select * from sys_letter_intid WITH (NOLOCK)

SQL Server - when should you use “with (nolock)”

There's a few factors that would go into this. A quick list of things to look at:

The speed of your server. CPU, memory, network connection would all be factors
If you are doing a SELECT statement with conditions (ie. using a WHERE) or one with JOINS, having indexes will improve your performance, especially on a table with millions of rows. Hash tables will do a huge net positive on a large table.
Writing clean queries. For example, if you have a large list of items you need to exclude from a query, perform a LEFT JOIN instead of using a NOT IN condition.

This is really just the tip of the iceberg, but some of the easiest things to implement will also provide you with some of the biggest performance boosts.

Efficient way to get 10 million records from SQL

2 Answers2