Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 19 of 19 total
Thread Best way to merge several tables into one?
Wed, Oct 3 2007 10:24 AMPermanent Link

Dave Harrison
Roy Lambert wrote:

> Dave
>
>
> Isn't it just a matter of setting the COMMIT interval?
>
> Roy Lambert
>

Roy,
   If you're using a loop, then sure. But how do you set a Commit
interval for something like:

insert into table2 select * from table1;

it will fail if there are too many rows in table1. Am I missing something?

Dave
Wed, Oct 3 2007 10:38 AMPermanent Link

"Jose Eduardo Helminsky"
Dave

> insert into table2 select * from table1;
>
> it will fail if there are too many rows in table1. Am I missing something?

Just add "commit interval 500" for commit for each 500 records

insert into table2 select * from table1 commit interval 500

Eduardo

Wed, Oct 3 2007 5:49 PMPermanent Link

Dave Harrison
Jose Eduardo Helminsky wrote:
> Dave
>
>
>>insert into table2 select * from table1;
>>
>>it will fail if there are too many rows in table1. Am I missing something?
>
>
> Just add "commit interval 500" for commit for each 500 records
>
> insert into table2 select * from table1 commit interval 500
>
> Eduardo
>
>
Eduardo,
    I didn't realize the Insert statement had a "Commit Interval"
syntax. I'll give it a try. Thanks Smile

Dave
Wed, Oct 3 2007 8:01 PMPermanent Link

Dave Harrison
Jose Eduardo Helminsky wrote:
> Dave
>
>
>>insert into table2 select * from table1;
>>
>>it will fail if there are too many rows in table1. Am I missing something?
>
>
> Just add "commit interval 500" for commit for each 500 records
>
> insert into table2 select * from table1 commit interval 500
>
> Eduardo
>
>
Eudardo,
    Well I used "commit interval 1000" and it still failed with "DBISAM
Engine error # 10258 Cannot lock record in the table sp_all". It failed
halfway through like it did before without the "commit interval".

Here is the SQL:

delete from sp_all;
insert into sp_all
select * from sp0_to union all
select * from sp1_to union all
select * from sp2_to union all
select * from sp3_to union all
select * from sp4_to union all
select * from sp5_to union all
select * from sp6_to union all
select * from sp7_to union all
select * from sp8_to union all
select * from sp9_to union all
select * from sp10_to union all
select * from sp11_to union all
select * from sp12_to union all
select * from sp13_to union all
select * from sp14_to union all
select * from sp15_to union all
select * from sp16_to union all
select * from sp17_to union all
select * from sp18_to union all
select * from sp19_to union all
select * from sp20_to union all
select * from sp21_to union all
select * from sp22_to union all
select * from sp23_to union all
select * from sp24_to union all
select * from sp25_to union all
select * from sp26_to union all
select * from sp27_to
order by date1 commit interval 1000;

Now it has failed in the past using just a simple "Insert into table1
from table2" so it's not the "union all" syntax. I guess it is just too
many records to move at one time.

Dave
Thu, Oct 4 2007 6:13 AMPermanent Link

"Jose Eduardo Helminsky"
Dave

It failed in the prepare step or during the SQL ?

Eduardo

Thu, Oct 4 2007 10:35 AMPermanent Link

Dave Harrison
Jose Eduardo Helminsky wrote:
> Dave
>
> It failed in the prepare step or during the SQL ?
>
> Eduardo
>
>

Eduardo,
    During the execution of the SQL. It ran for about an hour then I
got the error. It failed at approx the same time it did before when I
wasn't using "commit interval". I'll see if I can create a test file
that I can submit.

Dave
Thu, Oct 4 2007 11:09 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Dave,

<< Why is the OS doing row locks? >>

DBISAM uses the OS to do the actual low-level locking, with a different lock
range per row.  See here:

http://www.elevatesoft.com/dbisam4d7_locking_concurrency.htm

under "How DBISAM Performs Locking".

<< I thought DBISAM would have locked the table until the query completed so
there would be only 1 lock. >>

No, but you can do that also to get around this issue.  Just use a
TDBISAMTable to issue a LockTable call on the desired table prior to running
the query, and that should prevent multiple row locks from being acquired.

<< Has ElevateDb solved this problem? >>

Yes, EDB does not use a strict transaction internally with an INSERT..SELECT
statement, and only acquires (approximately) a few hundred row locks at a
given time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Oct 4 2007 11:10 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< Isn't it just a matter of setting the COMMIT interval? >>

No, since the COMMIT interval doesn't affect how/when DBISAM releases the
row locks it acquires during the execution of the statement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Oct 4 2007 11:10 AMPermanent Link

"Jose Eduardo Helminsky"
Dave

I think it is a bug in DBISAM because recently I´ve been updating a huge
table (8M records) and get the same problem. I´ve reported it to Tim and
have checked with the
last DBSys and after a while I´ve received #10258 error. Even after a repair
(took me another hours) the error appear again.

Eduardo

« Previous PagePage 2 of 2
Jump to Page:  1 2
Image