Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 19 of 19 total |
Best way to merge several tables into one? |
Wed, Oct 3 2007 10:24 AM | Permanent 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 AM | Permanent 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 PM | Permanent 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 Dave |
Wed, Oct 3 2007 8:01 PM | Permanent 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 AM | Permanent Link |
"Jose Eduardo Helminsky" | Dave
It failed in the prepare step or during the SQL ? Eduardo |
Thu, Oct 4 2007 10:35 AM | Permanent 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent 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 Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |