On Sat, 6 Mar 2004 23:41:26 +0000 (UTC), ggroper <ggroper@...> wrote:

> I backed up the structure of my data base and restored it with no data.
> I used the IBPump.exe utility that I downloaded. It has a date of
> 11/1/2002. Maybe this is a problem and is not compatible with FB 1.5 ???
> I pumped a small amount of data - about 300,000 records, rebult a
> summary table and updated fields in the large table. This seemed to
> work OK. I then did a database check and a sweep with Database
> Workbench. All ok. I then used a DOS batch file to do a complete
> backup and restore using gbak, this went OK and pretty fast. I then
> deletede the rows in my two test files, the big table and a small
> summary table created by a sql procedure. I re ran the back up and
> restore for the empty data base all OK ready to go!!

Deleting (all) the rows leaves (a lot of) garbage - which eventually will
be cleaned out (GC).
Doing backup (with or without GC) plus restore will accomplish this nicely

> The IBPump program ran the load of the full data set, 3.5 mill
> records, in about 2 hours. This seemed faster than the MS SQL Server
> utility. I next ran and update stored procedure to update two fields
> in the large table, took about an hour and a half. I next ran a
> summary procedure to create a new file, again slow but it finished.

Did you run the process on the server (is there network times included in
the timings)?
I haven't used IBpump, but I once transferred data from one database to
another (all running on the same server) on a 2x800MHz Xeon server doing
about 2000 records (select+insert) per second. Declared record size about
512B (probably about 50% filled with data). This was a Delphi program
using IBO without any special optimization for those databases. No indexes
defined, just the primary key.
Updates also leaves some garbage, the system will eventually clean up.
When I do massive updates or deletes I usually try to get rid of the
garbage there and then (this used to be easy, but it seems we are now
slaves of the GC thread).
GC can be particularly painful with low-selectivity indexes on large tables.

> I looked at the data, ran a few queries, slow but data seemed to be OK.
>
> I next ran the DOS bactch file to back up the data base. It started
> out and then stalled, I looked at the file as it was being created -
> very very slow almost a hang-maybe 1 kb a sec. After two hours I
> killed the job, rebooted and tried a back up from Database Workbench,
> again it started off OK, but then slowed down at about 260,000 records
> and seemed to hang there.

Maybe the system was also taking out the garbage while doing the backup
(any low-selectivity indexes?)
Since it started off fine, maybe som garbaga was already cleaned out.

> I looked at the data with a Delphi test program and Jason Wharton's
> controls and all the data seemed to be there. The first query with
> three parameters on indexed fields took over a minute to search
> through the 3.5 million records, but changing the paremeters, closing
> and reopening the query was sub second??? (Same query with ADO to MS
> SQL Server was initially 4 secs and subsequent queriesw sub second
> slightly faster than FB.)

Take a look at the plan for the query - this may give a hint.

> So, I have data that looks good, executes slow, and may be corrupted.
> It seems I can not do a sweep, or a backup????

Check the consistency of the data with the command line tools (gfix).

> How long do you estimate it should take to sweep? and a back up a
> database with 3.5 million new records, and I guess 3.5 million new
> updates??? Does 4 or 5 hours seem right or more like 30 minutes???

This depends. With a lot of garbage (and low-selectivity indexes) it may
really take a loooong time. If I will delete all records and there isn't
may dependencies, I consider dropping the table and recreating it.
I do a sweep every night (at work), but haven't timed it. My guess is
about an hour or so - maybe some more. The database has about 4 mill.
records (1.3GB), not much garbage (800MHz proc., 512MB RAM and 15krpm
disk. Win2k/SP2).

Hmmmmm - I just ran a sweep on a local database (at home). 900MB database
(no garbage), 1.7GHz cpu, 7.2krpm IDE disk: less than a couple of minutes.
I did:
gfix -sweep -user sysdba -password masterkey D:\DB\db.gdb
Ran it once more: 35 seconds - amazing. This is FB1.5/RC6.

> I assume that FB 1.5 is compatable with Database Workbench, and the
> IBPump. May be not and there is some weird corruption.
> Again, the WIN XP retore is turned off, the database extension is fdb.
> I have a 3ghz PC with 1 gig memory, and two disk drives with over 10
> gig each available on both drives. The FB database is about 2 gig
> before a back up and restore.
> Any ideas?? Maybe try again and pump from Database Workbench??

I wouldn't suspect DBW or IBPump. However, if you find corruptions you
should contact the authors.
Touch wood - I haven't seen a corrupted IB or Fb database since I swept an
IB/5.0 database (where we had deleted just over 6 mill. records from a
single table).


--
Aage J.