Hi,

In a DBISAM3 application we are seeing that a table containing about 70.000
records take about 250msec for the following to execute:

SELECT MAX(ID) FROM OrderItemTable

This pussles me a bit. The column ID is the primary index(*), and even if i
optimize the table to this column it still takes about 250msec...? How come?

When the column is indexed it should be (blazing!) fast to find the maximum
value....or not?

Any ideas/suggestions/pointers will be greatly appreciated!

Thanks!

/Thomas



*) Complete table description:

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "OrderItemTable"
(
  "ID" AUTOINC NOT NULL,
  "ItemNo" VARCHAR(250),
  "ItemText" MEMO,
  "EAN" VARCHAR(250),
  "Amount" FLOAT DEFAULT 1,
  "SalesPrice" FLOAT DEFAULT 0,
  "Extra" FLOAT DEFAULT 0,
  "DiscountPct" FLOAT DEFAULT 0,
  "PurchasePrice" FLOAT DEFAULT 0,
  "OrderID" INTEGER DEFAULT 0,
  "StatusID" INTEGER DEFAULT 0,
  "LineNo" INTEGER DEFAULT 0,
  "Imported" BOOLEAN DEFAULT FALSE,
  "VAT" FLOAT DEFAULT 0,
PRIMARY KEY ("ID") COMPRESS NONE
LANGUAGE "ANSI Standard" SORT "Default Order"
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "IDX_OrderItemTable_1" ON "OrderItemTable"
("OrderID");
CREATE INDEX IF NOT EXISTS "IDX_OrderItemTable_2" ON "OrderItemTable"
("StatusID");

Thomas,

<< In a DBISAM3 application we are seeing that a table containing about
70.000 records take about 250msec for the following to execute:

SELECT MAX(ID) FROM OrderItemTable

This pussles me a bit. The column ID is the primary index(*), and even if i
optimize the table to this column it still takes about 250msec...? How come?
>>

DBISAM 3.x does not optimize the MIN()/MAX() aggregate functions.   DBISAM
4.x and ElevateDB both do, however.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i en
meddelelse news:349B4E78-7047-4FA2-BE70-F559536B5E62@news.elevatesoft.com...
> << In a DBISAM3 application we are seeing that a table containing about
> 70.000 records take about 250msec for the following to execute:
>
> SELECT MAX(ID) FROM OrderItemTable
>
> This pussles me a bit. The column ID is the primary index(*), and even if
> i optimize the table to this column it still takes about 250msec...? How
> come?
> >>
>
> DBISAM 3.x does not optimize the MIN()/MAX() aggregate functions.   DBISAM
> 4.x and ElevateDB both do, however.
>

This makes "inserting and retrieving last autoinc"-values very slow using
queries...are there anything i can do to get around this?

....and no...upgrading this particular application to a newer database isn't
an option

/Thomas

"Thomas Eg Jørgensen" <thomas@killspam.notaplan.com> wrote in message
news:3E0ED7F3-6326-4010-8903-CC76CC7D7674@news.elevatesoft.com...
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i en
> meddelelse
> news:349B4E78-7047-4FA2-BE70-F559536B5E62@news.elevatesoft.com...
>> << In a DBISAM3 application we are seeing that a table containing about
>> 70.000 records take about 250msec for the following to execute:
>>
>> SELECT MAX(ID) FROM OrderItemTable
>>
>> This pussles me a bit. The column ID is the primary index(*), and even if
>> i optimize the table to this column it still takes about 250msec...? How
>> come?
>> >>
>>
>> DBISAM 3.x does not optimize the MIN()/MAX() aggregate functions.
>> DBISAM 4.x and ElevateDB both do, however.
>>
>
> This makes "inserting and retrieving last autoinc"-values very slow using
> queries...are there anything i can do to get around this?
>

Why?doesn't the query use lastautoinc? What exactly are you doing?

Robert

Thomas,

<< This makes "inserting and retrieving last autoinc"-values very slow using
queries...are there anything i can do to get around this? >>

Robert makes a good suggestion - you can use the LASTAUTOINC() function for
this also.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i en
meddelelse news:CD148A0B-CBC0-43B1-82CE-67B6DD0B9A3F@news.elevatesoft.com...
> << This makes "inserting and retrieving last autoinc"-values very slow
> using queries...are there anything i can do to get around this? >>
>
> Robert makes a good suggestion - you can use the LASTAUTOINC() function
> for this also.
>

Hi Tim and Robert, Thanks for your answers...


I have a range of dataclasses and they all implement a save-method. In this
method i have to to either execute an INSERT-statement or an
UPDATE-statement, depending on if the dataobject is a new object(never saved
to database before) or an old object which just have to be updated in the
database.

In the save-method i do something like this during the INSERT-statement:

INSERT INTO MyTable(blah)
VALUES (blah);
SELECT MAX(ID) FROM MyTable

to insert a new item and return the new items autoinc ID...The next time the
save-method is called the object has an ID-value and the UPDATE-statement
will be used instead.

This works fine as long as i only have to insert one item. The problem
occures when i then create a list of my dataclasses and try to import(and
save/insert) multiple items at once...This i quite slow since the
executiontime is linear with the amount of objects to insert(each MAX()
always takes the same amount of time)...even though I use
"prepared-queries", but this does not seem to help on MAX()...

I was not able to find the TDBISAMQuery.LastAutoInc...? I was also under the
impression that this was only implemented in the TDBISAMTable?

Thanks again!

/Thomas

"Thomas Eg Jørgensen" <thomas@killspam.notaplan.com> wrote in message
news:92BDB4E6-E24F-4760-A8E9-13622D74F3A2@news.elevatesoft.com...
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i en
> meddelelse
> news:CD148A0B-CBC0-43B1-82CE-67B6DD0B9A3F@news.elevatesoft.com...
>> << This makes "inserting and retrieving last autoinc"-values very slow
>> using queries...are there anything i can do to get around this? >>
>>
>> Robert makes a good suggestion - you can use the LASTAUTOINC() function
>> for this also.
>>
>
> Hi Tim and Robert, Thanks for your answers...
>
>
> I have a range of dataclasses and they all implement a save-method. In
> this method i have to to either execute an INSERT-statement or an
> UPDATE-statement, depending on if the dataobject is a new object(never
> saved to database before) or an old object which just have to be updated
> in the database.
>
> In the save-method i do something like this during the INSERT-statement:
>
> INSERT INTO MyTable(blah)
> VALUES (blah);
> SELECT MAX(ID) FROM MyTable
>

Well then just don't use MAX for this purpose.

Also there is no guarantee that between the time you completed an insert and
the time you get max or lastautoinc, somebody else did not do another
insert. Worse for MAX, of course, since it takes longer.

Can't you use a tTable instead of a query?  It is really the only way, at
least as far as I know, of making sure you get the right autoinc number.

Robert

Thomas

> In a DBISAM3 application we are seeing that a table containing about
70.000
> records take about 250msec for the following to execute:
>
> SELECT MAX(ID) FROM OrderItemTable
>
> This pussles me a bit. The column ID is the primary index(*), and even if
i
> optimize the table to this column it still takes about 250msec...? How
come?

Where ID is an autoinc field one possibility would be to use the lastautoinc
function.
Create a dummy table with 1 record (eg dummy) then use

SELECT LASTAUTOINC("OrderItemTable") FROM dummy

If this a multi user app this must be done within a transaction to ensure
the correct value is returned.

Alternaively if you have the source code you can optimize the MAX function


John

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i en meddelelse
news:5B8F467A->>> << This makes "inserting and retrieving last
autoinc"-values very slow
>>> using queries...are there anything i can do to get around this? >>
>>>
>>> Robert makes a good suggestion - you can use the LASTAUTOINC() function
>>> for this also.
>>>
>>
>> In the save-method i do something like this during the INSERT-statement:
>>
>> INSERT INTO MyTable(blah)
>> VALUES (blah);
>> SELECT MAX(ID) FROM MyTable
>>
>
> Can't you use a tTable instead of a query?  It is really the only way, at
> least as far as I know, of making sure you get the right autoinc number.
>

I will try that...

Thank you...

/Thomas

"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> skrev i en
meddelelse news:05AB4F47-BDAB-4B5A-A38C-51591D664C22@news.elevatesoft.com...
>> In a DBISAM3 application we are seeing that a table containing about
> 70.000
>> records take about 250msec for the following to execute:
>>
>> SELECT MAX(ID) FROM OrderItemTable
>>
>> This pussles me a bit. The column ID is the primary index(*), and even if
> i
>> optimize the table to this column it still takes about 250msec...? How
> come?
>
> Where ID is an autoinc field one possibility would be to use the
> lastautoinc
> function.
> Create a dummy table with 1 record (eg dummy) then use
>
> SELECT LASTAUTOINC("OrderItemTable") FROM dummy

Interesting! Thanks, i will try that out...

> Alternaively if you have the source code you can optimize the MAX function
>
>

ha! I think you overestimate me


/Thomas