At 01:32 AM 24/11/2007, you wrote:

>Hi Helen,
>
>> Well, reading this and your other posts, I observe that confusion
>reigns supreme. You have introduced an "idcount" - so - did you
>really want to COUNT the number of hits on the searchitem????
>>
>> If so, you're a bit lost...so answer that question and you will be
>overwhelmed with correcto solutions.
>>
>> ./heLen
>>
>
>hehe, I´m not lost :)
>
>You think just writing the requests in a table and do a "select count"
>would be faster?

I'm not suggesting you do a select count...the logic doesn't fit what you apparently want to do. I just watched your successive posts where you introduced this idcount variable. If you want to COUNT the hits then your select statement needs to be in a FOR loop, one iteration for each hit and setting or incrementing your counter at each turn of the loop.

> If so, what´s the limitation? Will it get slower and
>slower when the table grows up to 5 million records and above?

Whatever you do, it's not size that matters, it's how good your indexing is. A search term of 400 UTF-8 characters doesn't look too hot unless you have a page_size of 8k or more, though.

>Yes, I want to log the hits on a searchitem.

CREATE PROCEDURE SP_TBLONE_INS_UPD(
SEARCHTERM VARCHAR(400) CHARACTER SET UTF8 /* DEFAULT NULL this is silly - you'll never match a searchterm of null */)
RETURNS(
SEARCHEXISTS SMALLINT)
AS
DECLARE VARIABLE getid INTEGER;
DECLARE VARIABLE idcount INTEGER;
declare variable newcount integer = 0;
declare variable nowtime timestamp;
declare variable searchstarttime timestamp=CURRENT_TIMESTAMP;
declare variable lastsearch timestamp;
BEGIN
getid = NULL;
idcount = null;
lastsearch = null;
for
SELECT ID, SEARCHCOUNT, lastsearch
FROM TBLONE
WHERE SEARCHTERM = :SEARCHTERM

INTO :getid, :idcount, :lastsearch DO
/* loop begins here */
BEGIN
nowtime = cast('now' as timestamp);
/* ignore if we have inserted the record during this loop */
if (lastsearch not between searchstarttime and nowtime) then
BEGIN
IF ( getid IS NULL or idcount is null or lastsearch is null ) THEN
BEGIN
INSERT INTO TBLONE
( SEARCHTERM, LASTSEARCH, SEARCHCOUNT)
VALUES
(:SEARCHTERM,nowtime, 1 );
END
else /* if (getid IS NOT NULL) then */
begin
if (idcount is null) then
idcount = 1;
else
idcount = idcount + 1;
update TBLONE
SET
SEARCHCOUNT = :idcount,
lastsearch = :nowtime
where ID = :getid;
END
END /* of the ignore trap */
/* re-intialise the variable for the next iteration */
getid = NULL;
lastsearch = null;

END /* of the loop */

SUSPEND;
END;

./hb