Creating huge amounts of test data sql

Question

I've wrote a script that does work, but it's VERY slow (6 hours). I've been tasked with creating 10000 records, each with anywhere (randomly) up to 50 enditems, each with anywhere (randomly) up to another 50 relevant foreign key items, and then again, and again.

I think it's so very slow because of the iterative way I'm doing it.

Loop 10000
 insert into tble1()
 Loop 50
  insert into tble2 (select top(1) from tble1 desc)
  Loop 50
   insert into tble3 (select top(1) from tble2 desc)
    Loop 50
     insert into tble4 (select top(1) from tble3 desc)
   end
  end
 end 
end

Is kind of how it looks. I know sql works best with sets instead of iteration, but is there a way to do this with relationships without this loop setup I have?

That's 25 million rows I'd let it run for 6 hours overnight & use it the next day. — Toby Allen
– Toby Allen, Commented Dec 6, 2014 at 21:17
Yeah, I know I'm dealing with a lot of data, but is the method ive done this in way too slow? I'm really not sure how else I could do it but this. I'm pretty new to SQL, and developing in a set mindset instead of the traditional iterative process. — Adola
– Adola, Commented Dec 6, 2014 at 21:19

Neil McGuigan · Accepted Answer · 2014-12-06 21:28:36Z

1

Which database brand?

If your db supports it, you should use Bulk Copy.

If you can't do Bulk Copy, try

insert into some_table (columns...) values
(some values...),
(some values...),
...
(some values...);

You can also disable auto-commit and only commit the transaction at the very end.

It will also be faster if you can drop/disable indexes, triggers, constraints, keys before the insert and re-enable them after.

You would want to insert into the foreign (referred to) tables first.

MS SQL Server: http://msdn.microsoft.com/en-us/library/ms188365.aspx

PostgreSQL: http://www.postgresql.org/docs/9.3/static/sql-copy.html

Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_concepts.htm

edited Dec 6, 2014 at 21:28

answered Dec 6, 2014 at 21:21

Neil McGuigan

48.5k12 gold badges131 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adola Over a year ago

I'm looking at bulk insert right now. And unless I'm reading the documentation wrong, I have to already have data created right? I have to randomly generate all this data. And as for your example, that's how I'm currently doing it. But dropping indexs might be an idea, I haven't tried that yet.

Neil McGuigan Over a year ago

What lang is your script in? In JDBC+Postgres for example you can stream to database using CopyManager (which uses COPY internally). Or you could write to temp csv files

Toby Allen · Accepted Answer · 2014-12-06 21:26:09Z

0

We'll notwithstanding that I think 6 hours is quite a short time to insert 1.25 billion rows you could try the following

Loop 10000
 Insert into table

   Insert into table (select 50 from temp table where Id like x)
   Loop 50
     Insert into table (select 50 from temptable2 where id > something else)

answered Dec 6, 2014 at 21:26

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

Creating huge amounts of test data sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related