Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 10 total |
DBISAM3: MAX() very slow on primary index? |
Fri, Feb 27 2009 8:30 AM | Permanent Link |
"Thomas Eg Jørgensen" | 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"); |
Mon, Mar 2 2009 7:14 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 2 2009 9:53 AM | Permanent Link |
"Thomas Eg Jørgensen" | "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 |
Mon, Mar 2 2009 11:53 AM | Permanent Link |
"Robert" | "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 |
Thu, Mar 5 2009 5:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Mar 13 2009 4:31 AM | Permanent Link |
"Thomas Eg Jørgensen" | "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 |
Fri, Mar 13 2009 8:56 AM | Permanent Link |
"Robert" | "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 |
Sat, Mar 14 2009 7:48 AM | Permanent Link |
"John Hay" | 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 |
Thu, Mar 19 2009 4:27 AM | Permanent Link |
"Thomas Eg Jørgensen" | "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 |
Thu, Mar 19 2009 4:56 AM | Permanent Link |
"Thomas Eg Jørgensen" | "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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |