Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-31 : 09:08:42
|
| j writes "Hi,I have a question about how to poplate various tables in a SQL server DB. The application when up and running will grow quickly and some of the tables will hold millions of rows. We need to stress test the DB to see how our current hardware and set up will perform. So, we need to get large amounts of data into our DB FAST!!!Note:The inserts we're trying involve a single insert into a "parent" table and N inserts into a "child" table.Attempts:We've tried calling insert stored procedures in a loop (which exec() parameter strings) - 1000 inserts into parent table and 3000 inserts into child table takes 4.5 minutes.We've tried generating large SQL strings and exec-ing them. (using VBscript to generate the string an passing to a stored proc for execution) - same insert package as above but > 20 minutes!!!We've tried running a cursor which selects everyting from the tables and reinserts them back into the tables - same insert package as above but ~ 3 minutes.Are we missing something??? Are there quicker ways for us to flood the DB with useful data.Would using DTS be useful? Are there bulk copying facilites for such a task as this?Specification:W2K/SQL server 2000" |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-05-31 : 09:12:59
|
| Check Books On Line for info about the Replicate() function. Should be relatively quick to generate data.Justin |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-31 : 11:05:49
|
| is your purpose to stress test your app with a full database or stress test the filling of the database?if its the former, why does it matter how fast your database populates. I figure with you first method it'll take about 18 hours to get to 1 mill rows . . . start it now and test it tomorrow.if its the latter, shouldn't you use the same method that your app will be populating the tables (my guess, the stored procs with the insert dml)? it seems to me your first attempt (inserts in a loop) was your stress test. you could run this script from as many simultantious connections as you can to get more realistic (read: slower) results....<O> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-31 : 11:13:32
|
| I agree w/ Page, it's unclear from your post what you are trying to accomplish here. Are you a) trying to see how fast your system accepts inserts, or b) trying to fill it with data because you anticipate significant performance degradation as a result?if a), there are plenty of disk subsystem metrics utilities available out there. MSFT even has a free one dedicated specifically to SQL Server.if b), this should be a red flag that your system design (and, in particular, indexing and app row fetching) is not optimal, especially if you anticipate that read performance will suffer.setBasedIsTheTruepath<O> |
|
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2002-05-31 : 11:49:26
|
| There is pretty syntax in native sql that you can use to generate table and lots of insert quickly.try this : select * into newtable from existingtableYou can use this for any query as long as your new table does'nt exists |
|
|
|
jeremym
Starting Member
1 Post |
Posted - 2002-06-06 : 08:50:42
|
| Sorry for not being clearer in my original post - I was a bit stressed!My aim was to try to fill the database as quickly as possible as I wanted to test the performance of various complicated queries. The database design is NOT optimal and was designed 2-3 years ago. I wanted to flood the database to its estimated capacity in 6 months time, and then to test the performance of the SQL. This would allow me to give early warning to my boss about the needs for a possible redesign of the DB.As Page47 pointed out it would take about 18 hours - I just wanted to show my boss the outcome of my tests ASAP i.e. in a few hours.The issue is resolved now anyway - I ended up using DTS which seems to be one of the fastest ways to flood a table(s) - 60000 rows in 4.5 mins.Thanks for you time guys,JIf anyone has any other "flood-a-DB-PDQ" suggestions I'd love to hear them. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-06 : 09:32:11
|
| What's your disk subsystem? 60,000 rows in 4.5 minutes is very slow. I would say 60,000 rows in 4.5 seconds is slow.In the DTS data pump task, did you uncheck the 'check constraints' checkbox? Did you check the 'table lock' checkbox? Are there any insert triggers on the table?setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
Populating tables rapidly!