By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,274 Members | 2,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,274 IT Pros & Developers. It's quick & easy.

How to speed up database scripts

P: n/a
We currently have all of our schema and testdata laid out in a large
set of sql scripts.

It currently takes about 15 minutes to run the scripts on a Dual
1.7ghz box with 1gb of ram.

Does anyone know of a way to speed this up? Since our database is
currently changing all the time, we are constantly having to re-create
our database from these scripts.

We recently migrated from using Sybase ASA to DB2 8.0.4 Workgroup
Server Unlimited. The same scripts take about 5 minutes in Sybase ASA.

Thanks,
Mike.
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
What do the scripts do? Can you take one or two examples of scripts that
are taking 15 minutes and tell us what they do?

Larry Edelstein

Mike Nau wrote:
We currently have all of our schema and testdata laid out in a large
set of sql scripts.

It currently takes about 15 minutes to run the scripts on a Dual
1.7ghz box with 1gb of ram.

Does anyone know of a way to speed this up? Since our database is
currently changing all the time, we are constantly having to re-create
our database from these scripts.

We recently migrated from using Sybase ASA to DB2 8.0.4 Workgroup
Server Unlimited. The same scripts take about 5 minutes in Sybase ASA.

Thanks,
Mike.


Nov 12 '05 #2

P: n/a
Mike,

You could add some timer outputs to your SQL scripts to get an idea where
the majority of time is being spent.

e.g !date or SELECT CURRENT TIME FROM sysibm.sysdummy1

I would guess it would come down to the way the data is being loaded.
e.g Many insert statements?

Paul.

mi******@yahoo.com (Mike Nau) wrote in message news:<b8**************************@posting.google. com>...
We currently have all of our schema and testdata laid out in a large
set of sql scripts.

It currently takes about 15 minutes to run the scripts on a Dual
1.7ghz box with 1gb of ram.

Does anyone know of a way to speed this up? Since our database is
currently changing all the time, we are constantly having to re-create
our database from these scripts.

We recently migrated from using Sybase ASA to DB2 8.0.4 Workgroup
Server Unlimited. The same scripts take about 5 minutes in Sybase ASA.

Thanks,
Mike.

Nov 12 '05 #3

P: n/a
AK
Mike,
it could be much faster to use LOAD utility than to run INSERT staetements.
Also you might want to create indexes after all the data has been loaded
Nov 12 '05 #4

P: n/a
The scripts do the following:

1. Delete the database.
2. Create the database.
3. Load the tables
4. Load the table constraints
5. Load the views
6. Loads the data via insert statements

A lot of the time is consumed during the insert statements (~17000) of
them.

Were loading it via

db2 -v -s -t -f %file name% -l %log file name%

Since the identical scripts run 1/3 faster in Sybase ASA, I was
curious if I was missing something obvious.

Mike.
pa**@abacus.co.uk (Paul Reddin) wrote in message news:<1f**************************@posting.google. com>...
Mike,

You could add some timer outputs to your SQL scripts to get an idea where
the majority of time is being spent.

e.g !date or SELECT CURRENT TIME FROM sysibm.sysdummy1

I would guess it would come down to the way the data is being loaded.
e.g Many insert statements?

Paul.

mi******@yahoo.com (Mike Nau) wrote in message news:<b8**************************@posting.google. com>...
We currently have all of our schema and testdata laid out in a large
set of sql scripts.

It currently takes about 15 minutes to run the scripts on a Dual
1.7ghz box with 1gb of ram.

Does anyone know of a way to speed this up? Since our database is
currently changing all the time, we are constantly having to re-create
our database from these scripts.

We recently migrated from using Sybase ASA to DB2 8.0.4 Workgroup
Server Unlimited. The same scripts take about 5 minutes in Sybase ASA.

Thanks,
Mike.

Nov 12 '05 #5

P: n/a
Are you using:
INSERT INTO .. VALUES ( ...);
INSERT INTO .. VALUES ( ...);
.....?

DB2 supports multi row VALUES clauses.
INSERT INTO ... VALUES (....), (.....), ....;
Thsi can cut teh time down significantly.

DB2 V8 takes its time to create a database (it bind all kinds of
packages etc). AFAIK Sybase does more of a "restore" kind of db creation.

Naturally this usually doesn't matter, but in your extreme case it
likely does.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #6

P: n/a
Mike,

Sounds like you are in a similar development mode to what we are here.

Just a word of caution before you start changing all your inserts
to import or load. Those you could move to using these utilities (and
speed things up), it will likely take a little while to convert/set
up.

IMHO,If you are creating and inserting 17,000 rows in 15 minutes I
would be
tempted to stay with it and take the hit. Even if you improved it by
say 10 minutes would it be really worth the work/potential problems?

Are you/could you run the scripts local on the server?
The overhead of running across a network for 17,000
insert statements could be significant.

Call me lazy, but going KISS can somtimes save in the long run.

Paul.
Nov 12 '05 #7

P: n/a
AK
> 1. Delete the database.
2. Create the database.
3. Load the tables
4. Load the table constraints
5. Load the views
6. Loads the data via insert statements


It could be faster to

Load the tables
Loads the data via insert statements
Load the table constraints (if you mean RI)
Nov 12 '05 #8

P: n/a
Ian
AK wrote:
1. Delete the database.
2. Create the database.
3. Load the tables
4. Load the table constraints
5. Load the views
6. Loads the data via insert statements

It could be faster to

Load the tables
Loads the data via insert statements
Load the table constraints (if you mean RI)


Another enhancement:

Load (create) the tables
Load data via insert statements
Create indexes
Create constraints (RI)

Moving to the multi-value insert statements will help a lot, if you
can't move to using LOAD. Keep in mind that you could move to load
by populating the database with the current (insert) method, and
then just export all of your tables into delimited files.

Also, if you're creating LOTS (many hundred) of tables, consider using
DMS tablespaces, if you aren't already, as the overhead of allocating
new file(s) for each table in an SMS tablespace will add overhead.

Good luck,
Good luck,


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.