delimiting table size

[Evertjan.]

I would like to delimit a table to the newest 2000 entries
[for site hit logging]

I use ADODB and Microsoft.Jet.OLEDB.4.0

==========

Any sense in this PSEUDO! sql code [ID is autoincrement]:

Insert into table1 (field1,field2) values (1,2)
.... execute
Delete first(ID) from table1 order by ID where count(ID)>2000
.... execute

===========

Can I reduce a much longer table to the latest 2000 entries?

Repeat Delete first(ID) from table1 order by ID WHILE count(ID)>2000

[Yes I know repeat..while is not sql]

===========

Please hint me in the right direction.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> I would like to delimit a table to the newest 2000 entries
> [for site hit logging]
>
> I use ADODB and Microsoft.Jet.OLEDB.4.0
>
> ==========
>
> Any sense in this PSEUDO! sql code [ID is autoincrement]:
>
> Insert into table1 (field1,field2) values (1,2)
> ... execute
> Delete first(ID) from table1 order by ID where count(ID)>2000
> ... execute
>

No

> ===========
>
> Can I reduce a much longer table to the latest 2000 entries?
>
> Repeat Delete first(ID) from table1 order by ID WHILE count(ID)>2000
>
> [Yes I know repeat..while is not sql]
>
> ===========
>
> Please hint me in the right direction.

Create a new table with the same structure. Then,

INSERT into newtable
SELECT TOP 2000 ... from table1 ORDER BY ...

DELETE * FROM table1
INSERT into table1
SELECT ... from newtable
DELETE * FROM newtable

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Evertjan.]

Bob Barrows [MVP] wrote on 06 mei 2004 in
microsoft.public.inetserver.asp.db:

> Evertjan. wrote:

>> Please hint me in the right direction.

>
> Create a new table with the same structure. Then,
>
> INSERT into newtable
> SELECT TOP 2000 ... from table1 ORDER BY ...
>
> DELETE * FROM table1

> INSERT into table1
> SELECT ... from newtable
> DELETE * FROM newtable

Tnx, Bob,

Won't the last solution have the [theoretical?] possibility of deleting
a fresh simultaneous insert from another session ???

I will experiment with your solution.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> Bob Barrows [MVP] wrote on 06 mei 2004 in
> microsoft.public.inetserver.asp.db:

>> Evertjan. wrote:

>>> Please hint me in the right direction.

>>
>> Create a new table with the same structure. Then,
>>
>> INSERT into newtable
>> SELECT TOP 2000 ... from table1 ORDER BY ...
>>
>> DELETE * FROM table1

>

>> INSERT into table1
>> SELECT ... from newtable
>> DELETE * FROM newtable

>
> Tnx, Bob,
>
> Won't the last solution have the [theoretical?] possibility of

"last solution"? There's a single solution here consisting of 4 steps:

1. insert the top 2000 records into the newtable
2. delete all records from table1
3. insert the 2000 records from newtable into tsble1
4. delecte all the records from newtable


Or did you mean "step" when you said "solution"?

> deleting a fresh simultaneous insert from another session ???

Wrapping these steps in a transaction will prevent this type of problem. You
should use a transaction anyways so that it can be rolled back if any of the
steps fails.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

Bob Barrows [MVP] wrote on 06 mei 2004 in
microsoft.public.inetserver.asp.db:

> Evertjan. wrote:

>> I would like to delimit a table to the newest 2000 entries
>> [for site hit logging]
>>
>> I use ADODB and Microsoft.Jet.OLEDB.4.0
>>
>> ==========
>>
>> Any sense in this PSEUDO! sql code [ID is autoincrement]:
>>
>> Insert into table1 (field1,field2) values (1,2)
>> ... execute
>> Delete first(ID) from table1 order by ID where count(ID)>2000
>> ... execute
>>

>
> No
>

I made this as a [working!] solution:

randomize()
inserts(int(rnd()*33)) ' inserts random integer 0...32
maximizes(9) ' deleting the oldest records maximizes to 9
writes()

function inserts(x) ' inserts x value in new record
SQL = "Insert into Tabel1 (naam) VALUES ("&x&")"
'response.write "<hr>" & SQL & "<hr>"
set mDATA=CONNECT.Execute(SQL)
end function

function maximizes(x) ' deletse oldest records till x are left
SQL = "SELECT count(*) as counting FROM Tabel1"
'response.write "<hr>" & SQL & "<hr>"
set mDATA=CONNECT.Execute(SQL)
counting = mDATA("counting")

while counting>x
SQL = "SELECT min(ID) as minid FROM Tabel1"
'response.write "<hr>" & SQL & "<hr>"
set mDATA=CONNECT.Execute(SQL)
minid = mDATA("minid")

SQL = "DELETE * FROM Tabel1 WHERE ID = " & minid
'response.write "<hr>" & SQL & "<hr>"
set mDATA=CONNECT.Execute(SQL)

SQL = "SELECT count(*) as counting FROM Tabel1"
'response.write "<hr>" & SQL & "<hr>"
set mDATA=CONNECT.Execute(SQL)
counting = mDATA("counting")
wend
end function

function writes() ' writes the table contents
SQL = "Select ID,naam from Tabel1"
'response.write "<hr>" & SQL & "<hr>"
set mDATA=CONNECT.Execute(SQL)
n = 1
Do Until mData.Eof
Response.Write n & ": "
Response.Write mData("ID") & " - "
Response.Write mData("naam") & "<br>"
n = n + 1
mData.MoveNext
Loop
end function

Can this be done simpler ?


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> I made this as a [working!] solution:
>
> randomize()
> inserts(int(rnd()*33)) ' inserts random integer 0...32
> maximizes(9) ' deleting the oldest records maximizes to 9
> writes()
>
> function inserts(x) ' inserts x value in new record
> SQL = "Insert into Tabel1 (naam) VALUES ("&x&")"
> 'response.write "<hr>" & SQL & "<hr>"
> set mDATA=CONNECT.Execute(SQL)
> end function
>
> function maximizes(x) ' deletse oldest records till x are left
> SQL = "SELECT count(*) as counting FROM Tabel1"
> 'response.write "<hr>" & SQL & "<hr>"
> set mDATA=CONNECT.Execute(SQL)
> counting = mDATA("counting")
>

Maybe something like this:
if x < counting then
SQL = "DELETE * FROM Tabel1 WHERE ID IN " & _
"(SELECT TOP " & counting - x & " ID FROM " & _
"table1 ORDER BY ID asc)"
end if

At least that would get rid of the loop.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

Bob Barrows [MVP] wrote on 06 mei 2004 in
microsoft.public.inetserver.asp.db:

> Maybe something like this:
> if x < counting then
> SQL = "DELETE * FROM Tabel1 WHERE ID IN " & _
> "(SELECT TOP " & counting - x & " ID FROM " & _
> "table1 ORDER BY ID asc)"
> end if
>
> At least that would get rid of the loop.

This works very nice.
[took me half an hour to see
the difference beween tabel1 and table1 ;-)]

I will start to overhaul the logging in textfiles from 3 years ago.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)