Re: Advice - emptying a table while other users connected

#1

Bill

Hmmmm.. I am only using raw btrieve, not any of the fancy SQL stuff.
However, I can see that your stored procedure would be fast. Is there
a way to call a SQL stored procedure but only using the btrieve API
from 'C'? I don't really want to add a new API at this stage, but if I
can do it with my normal routines that would be great.

Thanks

Andy Rigby

Bill Bach <bbach (AT) cncdsl (DOT) com> wrote

Quote:

I've had very good luck with stored procedures to do this on PSQL2000 &
PSQLV8. An example is:
CREATE PROCEDURE DeleteIt() WITH DEFAULT HANDLER AS
BEGIN
DECLARE :M1 INT;
DECLARE C1 CURSOR C1 FOR
SELECT Field1 FROM DataFile
FOR UPDATE;
OPEN C1;
FETCH NEXT FROM C1 INTO :M1
WHILE SQLSTATE = '00000' DO
DELETE WHERE CURRENT OF C1;
FETCH NEXT FROM C1 INTO :M1;
END WHILE;
END

Performance is VERY good, especially under Pervasive.SQL V8, and can get
over 2000-2500 deletes per second on a 300MB data file.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: September 16-18: See our web site for details!

Andy Rigby wrote:

Hi

I have a C and Btrieve app that has some huge datafiles.

I have a situtation where I need to empty a table sometimes, even
though there may be other clients who are still viewing it.

I can do it by issuing repeated 'delete records', but with potentially
several million records, this will take a long long time.

I tried re-creating the table, which is fast, but of course the create
fails with 'access denied' type errors if other clients are still
using it.

I was wondering if there is some clever btrieve 'truncate table' type
command I could use. A bit like in SQL server where you can say
"truncate table customers"

Any thoughts?

Andy

#2

Hi Andy,

There is no reason why the Btrieve API calls would be slower than SQL, so I
don't think you need SQL for that (which require you to create ddf files
too). I would suggest to re-create the file, and if it fails, loop through
the file and delete the records. Try yo use B_GET_NEXT + 50 (Get Key) to
prevent sending too much data over the wire.

Serge

"Andy Rigby" <ecol1111 (AT) yahoo (DOT) co.uk> wrote

Quote:

Bill

Hmmmm.. I am only using raw btrieve, not any of the fancy SQL stuff.
However, I can see that your stored procedure would be fast. Is there
a way to call a SQL stored procedure but only using the btrieve API
from 'C'? I don't really want to add a new API at this stage, but if I
can do it with my normal routines that would be great.

Thanks

Andy Rigby

Bill Bach <bbach (AT) cncdsl (DOT) com> wrote

I've had very good luck with stored procedures to do this on PSQL2000 &
PSQLV8. An example is:
CREATE PROCEDURE DeleteIt() WITH DEFAULT HANDLER AS
BEGIN
DECLARE :M1 INT;
DECLARE C1 CURSOR C1 FOR
SELECT Field1 FROM DataFile
FOR UPDATE;
OPEN C1;
FETCH NEXT FROM C1 INTO :M1
WHILE SQLSTATE = '00000' DO
DELETE WHERE CURRENT OF C1;
FETCH NEXT FROM C1 INTO :M1;
END WHILE;
END

Performance is VERY good, especially under Pervasive.SQL V8, and can get
over 2000-2500 deletes per second on a 300MB data file.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: September 16-18: See our web site for details!

Andy Rigby wrote:

Hi

I have a C and Btrieve app that has some huge datafiles.

I have a situtation where I need to empty a table sometimes, even
though there may be other clients who are still viewing it.

I can do it by issuing repeated 'delete records', but with potentially
several million records, this will take a long long time.

I tried re-creating the table, which is fast, but of course the create
fails with 'access denied' type errors if other clients are still
using it.

I was wondering if there is some clever btrieve 'truncate table' type
command I could use. A bit like in SQL server where you can say
"truncate table customers"

Any thoughts?

Andy

#3

The advantage of the SP over the Btrieve app is that the SP runs ON the server
-- no network traffic. The Btrieve method MUST drag at least some data across
the network, resulting in two round-trip communications for each delete.
Serge's suggestion minimizes the amount of data transferred, but every trip over
the network wire will slow it down anyway.

Unfortunately, though, the Btrieve API cannot call a SQL SP. You need to use
ODBC (and all the corresponding overhead). Stick with Serge's suggestion for
the easiest coding.
BB

S Nelissen wrote:

Quote:

Hi Andy,

There is no reason why the Btrieve API calls would be slower than SQL, so I
don't think you need SQL for that (which require you to create ddf files
too). I would suggest to re-create the file, and if it fails, loop through
the file and delete the records. Try yo use B_GET_NEXT + 50 (Get Key) to
prevent sending too much data over the wire.

Serge

"Andy Rigby" <ecol1111 (AT) yahoo (DOT) co.uk> wrote in message
news:d7d8dd25.0308110813.43a54a5 (AT) posting (DOT) google.com...
Bill

Hmmmm.. I am only using raw btrieve, not any of the fancy SQL stuff.
However, I can see that your stored procedure would be fast. Is there
a way to call a SQL stored procedure but only using the btrieve API
from 'C'? I don't really want to add a new API at this stage, but if I
can do it with my normal routines that would be great.

Thanks

Andy Rigby

Bill Bach <bbach (AT) cncdsl (DOT) com> wrote in message
news:<3F28A7B4.3A332915 (AT) cncdsl (DOT) com>...
I've had very good luck with stored procedures to do this on PSQL2000 &
PSQLV8. An example is:
CREATE PROCEDURE DeleteIt() WITH DEFAULT HANDLER AS
BEGIN
DECLARE :M1 INT;
DECLARE C1 CURSOR C1 FOR
SELECT Field1 FROM DataFile
FOR UPDATE;
OPEN C1;
FETCH NEXT FROM C1 INTO :M1
WHILE SQLSTATE = '00000' DO
DELETE WHERE CURRENT OF C1;
FETCH NEXT FROM C1 INTO :M1;
END WHILE;
END

Performance is VERY good, especially under Pervasive.SQL V8, and can get
over 2000-2500 deletes per second on a 300MB data file.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: September 16-18: See our web site for details!

Andy Rigby wrote:

Hi

I have a C and Btrieve app that has some huge datafiles.

I have a situtation where I need to empty a table sometimes, even
though there may be other clients who are still viewing it.

I can do it by issuing repeated 'delete records', but with potentially
several million records, this will take a long long time.

I tried re-creating the table, which is fast, but of course the create
fails with 'access denied' type errors if other clients are still
using it.

I was wondering if there is some clever btrieve 'truncate table' type
command I could use. A bit like in SQL server where you can say
"truncate table customers"

Any thoughts?

Andy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Advice - emptying a table while other users connected

comp.databases.btrieve comp.databases.btrieve

Re: Advice - emptying a table while other users connected - 08-11-2003 , 11:13 AM

Re: Advice - emptying a table while other users connected - 08-11-2003 , 11:42 AM

Re: Advice - emptying a table while other users connected - 08-11-2003 , 05:32 PM