Most efficient way to get max row id?

[Amir Michail]

Hi,

I was wondering what is the most efficient way to get the max
automatically generated row id.

select max(...) is slow.

Amir

--
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail

[Peter Ondruška]

You want to use identity: Defining an identity column

On Sun, Oct 19, 2008 at 10:52 PM, Amir Michail <amichail@...> wrote:

Hi,

I was wondering what is the most efficient way to get the max
automatically generated row id.

select max(...) is slow.

Amir

--
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail

[Amir Michail]

On Sun, Oct 19, 2008 at 5:00 PM, Peter Ondruška
<peter.ondruska@...> wrote:
> You want to use identity: Defining an identity column

Yes, but how do you get the max row id efficiently without adding another row?

Amir

>
> On Sun, Oct 19, 2008 at 10:52 PM, Amir Michail <amichail@...> wrote:
>>
>> Hi,
>>
>> I was wondering what is the most efficient way to get the max
>> automatically generated row id.
>>
>> select max(...) is slow.
>>
>> Amir
>>
>> --
>> http://chatbotgame.com
>> http://numbrosia.com
>> http://twitter.com/amichail
>
>

--
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail

[PerfectReign]

On Sunday 19 October 2008 02:04:29 pm Amir Michail wrote:
> On Sun, Oct 19, 2008 at 5:00 PM, Peter Ondruška
>
> <peter.ondruska@...> wrote:
> > You want to use identity: Defining an identity column
>
> Yes, but how do you get the max row id efficiently without adding another
> row?

In the particular table, how many rows are you referring to? A few thousand? A
few million?  A hundred million?

Do you have an index on the identity column?  What's your datatype?

--
kai
www.filesite.org || www.perfectreign.com

 
Clean out a corner of your mind and creativity will instantly fill it. - Dee
Hock

[Amir Michail]

On Sun, Oct 19, 2008 at 11:25 PM, Kai Ponte <kai@...> wrote:

> On Sunday 19 October 2008 02:04:29 pm Amir Michail wrote:
>> On Sun, Oct 19, 2008 at 5:00 PM, Peter Ondruška
>>
>> <peter.ondruska@...> wrote:
>> > You want to use identity: Defining an identity column
>>
>> Yes, but how do you get the max row id efficiently without adding another
>> row?
>
> In the particular table, how many rows are you referring to? A few thousand? A
> few million?  A hundred million?
>
> Do you have an index on the identity column?  What's your datatype?

~60,000 rows

So if I put an index on the identity column, max will be fast?

Amir

>
> --
> kai
> www.filesite.org || www.perfectreign.com
>
>
> Clean out a corner of your mind and creativity will instantly fill it. - Dee
> Hock
>



--
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail

[Amir Michail]

On Mon, Oct 20, 2008 at 12:15 AM, Amir Michail <amichail@...> wrote:

> On Sun, Oct 19, 2008 at 11:25 PM, Kai Ponte <kai@...> wrote:
>> On Sunday 19 October 2008 02:04:29 pm Amir Michail wrote:
>>> On Sun, Oct 19, 2008 at 5:00 PM, Peter Ondruška
>>>
>>> <peter.ondruska@...> wrote:
>>> > You want to use identity: Defining an identity column
>>>
>>> Yes, but how do you get the max row id efficiently without adding another
>>> row?
>>
>> In the particular table, how many rows are you referring to? A few thousand? A
>> few million?  A hundred million?
>>
>> Do you have an index on the identity column?  What's your datatype?
>
> ~60,000 rows

But there's also a where in the query, so the number of rows
considered by max will vary.  A slow case involves ~ 16000 rows.

Amir

>
> So if I put an index on the identity column, max will be fast?
>
> Amir
>
>>
>> --
>> kai
>> www.filesite.org || www.perfectreign.com
>>
>>
>> Clean out a corner of your mind and creativity will instantly fill it. - Dee
>> Hock
>>
>
>
>
> --
> http://chatbotgame.com
> http://numbrosia.com
> http://twitter.com/amichail
>

--
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail

[Suavi Ali Demir]

You should have an index.


--- On Sun, 10/19/08, Amir Michail <amichail@...> wrote:

> From: Amir Michail <amichail@...>
> Subject: Re: Most efficient way to get max row id?
> To: "Derby Discussion" <derby-user@...>
> Date: Sunday, October 19, 2008, 9:18 PM
> On Mon, Oct 20, 2008 at 12:15 AM, Amir Michail
> <amichail@...> wrote:
> > On Sun, Oct 19, 2008 at 11:25 PM, Kai Ponte
> <kai@...> wrote:
> >> On Sunday 19 October 2008 02:04:29 pm Amir Michail
> wrote:
> >>> On Sun, Oct 19, 2008 at 5:00 PM, Peter
> Ondruška
> >>>
> >>> <peter.ondruska@...> wrote:
> >>> > You want to use identity: Defining an
> identity column
> >>>
> >>> Yes, but how do you get the max row id
> efficiently without adding another
> >>> row?
> >>
> >> In the particular table, how many rows are you
> referring to? A few thousand? A
> >> few million?  A hundred million?
> >>
> >> Do you have an index on the identity column?
> What's your datatype?
> >
> > ~60,000 rows
>
> But there's also a where in the query, so the number of
> rows
> considered by max will vary.  A slow case involves ~ 16000
> rows.
>
> Amir
>
> >
> > So if I put an index on the identity column, max will
> be fast?
> >
> > Amir
> >
> >>
> >> --
> >> kai
> >> www.filesite.org || www.perfectreign.com
> >>
> >>
> >> Clean out a corner of your mind and creativity
> will instantly fill it. - Dee
> >> Hock
> >>
> >
> >
> >
> > --
> > http://chatbotgame.com
> > http://numbrosia.com
> > http://twitter.com/amichail
> >
>
>
>
> --
> http://chatbotgame.com
> http://numbrosia.com
> http://twitter.com/amichail

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[PerfectReign]

On Sunday 19 October 2008 09:15:55 pm Amir Michail wrote:
> > Do you have an index on the identity column?  What's your datatype?
>
> ~60,000 rows
>
> So if I put an index on the identity column, max will be fast?

It will certainly be faster than without.

--
kai
www.filesite.org || www.perfectreign.com

 
Clean out a corner of your mind and creativity will instantly fill it. - Dee
Hock

[Rick Hillegas-2]

Hi Amir,

1) If what you need to know is the max value on disk right now, then you
will need to issue a "select max(...)" statement.

2) Maybe, however, all you need to know is the max value that was ever
on disk. Of course, this can be a different number than (1) because you
may have deleted rows at the upper end.

If all you need to know is (2), then you can get the answer from the
system catalogs. Here's a little script which creates a table with an
identity column, inserts and deletes some rows, and then selects the max
value that was ever in the identity column. The last query gives you the
answer to (2):

drop table s;
create table s
(
   a int generated always as identity (start with 3, increment by 3),
   b int
);

insert into s( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 );
delete from s where b = 4;
insert into s( b ) values ( 11 ), ( 12 ), ( 13 ), ( 14 );

select * from s order by b;

-- now find the max value that was ever put in the identity column
select c.autoincrementvalue - c.autoincrementinc
from sys.syscolumns c, sys.systables t
where t.tablename = 'S'
and t.tableid = c.referenceid
and c.columnname = 'A';

For more information, please see the Reference Guide section describing
the SYSCOLUMNS system catalog.

Hope this helps,
-Rick

Amir Michail wrote:

> Hi,
>
> I was wondering what is the most efficient way to get the max
> automatically generated row id.
>
> select max(...) is slow.
>
> Amir
>
>  

[Derby-3]

I think the question that hasn't been asked is why do you want to know the
max id of a row?

If you're using an identity column then you shouldn't have to worry about
the id of the row since the identity column will create and maintain the id
for you.

If you want to, you have another option.
You can create a id table and a couple of stored procedures to access this
table.

The table would consist of two columns. A label column and a counter column.
Your stored procedure has two options. lastVal() and getNextVal(),
getNextVal(n). (The last one is optional or could be incrementValTo(n) )

The table will be very small. A couple of rows and the lable,counter would
be unique.

This works well if you have a distributed system and you want to guarantee
unique ids across the databases. (There's more to the design, but the basic
concept is enough to answer this question.)

You can then use the id in your table, and you can always index the column
where the id is being used.

I think there should be a pattern of this somewhere...

HTH

-Mike

> -----Original Message-----
> From: Kai Ponte [mailto:kai@...]
> Sent: Monday, October 20, 2008 12:28 AM
> To: Derby Discussion
> Subject: Re: Most efficient way to get max row id?
>
> On Sunday 19 October 2008 09:15:55 pm Amir Michail wrote:
> > > Do you have an index on the identity column?  What's your datatype?
> >
> > ~60,000 rows
> >
> > So if I put an index on the identity column, max will be fast?
>
> It will certainly be faster than without.
>
> --
> kai
> www.filesite.org || www.perfectreign.com
>
>
> Clean out a corner of your mind and creativity will instantly fill it. -
> Dee
> Hock

[PerfectReign]

On Monday 20 October 2008 08:52:34 am derby@... wrote:
> The table would consist of two columns. A label column and a counter
> column. Your stored procedure has two options. lastVal() and getNextVal(),
> getNextVal(n). (The last one is optional or could be incrementValTo(n) )

I've done this also in the past. It simply keeps track of the last value of an
inserted item. That way a web page - disconnected recordset - can insert a
new item then another page can grab the information from that item.



--
kai
www.filesite.org || www.perfectreign.com

 
Clean out a corner of your mind and creativity will instantly fill it. - Dee
Hock

[Derby-3]

> -----Original Message-----
> From: Kai Ponte [mailto:kai@...]
> Sent: Monday, October 20, 2008 4:48 PM
> To: Derby Discussion
> Subject: Re: Most efficient way to get max row id?
>
> On Monday 20 October 2008 08:52:34 am derby@... wrote:
> > The table would consist of two columns. A label column and a counter
> > column. Your stored procedure has two options. lastVal() and
> getNextVal(),
> > getNextVal(n). (The last one is optional or could be incrementValTo(n) )
>
> I've done this also in the past. It simply keeps track of the last value
> of an
> inserted item. That way a web page - disconnected recordset - can insert a
> new item then another page can grab the information from that item.
>
>
>

Yes.

This kind of mimics how Informix's Serial column works or Sybase/Oracle's
sequence numbers work. (There's more to it on their part but you can always
make this simple thing more advanced.)

It's an "older technique" that still has its uses.

 

[Craig L Russell]

Echoing what Mike Segal said earlier,

Why do you want to know? You might be using the wrong tool for the job.

Craig

On Oct 19, 2008, at 1:52 PM, Amir Michail wrote:

> Hi,
>
> I was wondering what is the most efficient way to get the max
> automatically generated row id.
>
> select max(...) is slow.
>
> Amir
>
> --
> http://chatbotgame.com
> http://numbrosia.com
> http://twitter.com/amichail

Craig L Russell
Architect, Sun Java Enterprise System http://db.apache.org/jdo
408 276-5638 mailto:Craig.Russell@...
P.S. A good JDO? O, Gasp!

[Andrew McIntyre-2]

On Sun, Oct 19, 2008 at 1:52 PM, Amir Michail <amichail@...> wrote:
> Hi,
>
> I was wondering what is the most efficient way to get the max
> automatically generated row id.
>
> select max(...) is slow.

If this is an identity column, perhaps IDENTITY_VAL_LOCAL() suits your needs?

http://db.apache.org/derby/docs/10.4/ref/rrefidentityvallocal.html

andrew

[Amir Michail]

On Tue, Oct 21, 2008 at 2:32 PM, Andrew McIntyre <mcintyre.a@...> wrote:

> On Sun, Oct 19, 2008 at 1:52 PM, Amir Michail <amichail@...> wrote:
>> Hi,
>>
>> I was wondering what is the most efficient way to get the max
>> automatically generated row id.
>>
>> select max(...) is slow.
>
> If this is an identity column, perhaps IDENTITY_VAL_LOCAL() suits your needs?
>
> http://db.apache.org/derby/docs/10.4/ref/rrefidentityvallocal.html
>
> andrew
>

I ended up storing the max id in another table (which I get
efficiently via IDENTITY_VAL_LOCAL() ).

Amir


--
http://chatbotgame.com
http://numbrosia.com
http://twitter.com/amichail

[vadali]

Thank you Rick!

That was really helpful!

I need to use option 2, since i cannot retrieve automatically generated identity numbers on a multi-columns insert (https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel).

that select you posted is exactly what I need in order to maintain the identities myself for the time being, starting with the last one (+ inc size) every time the program restarts (or the connection was lost).

Rick Hillegas-2 wrote:

Hi Amir,

1) If what you need to know is the max value on disk right now, then you
will need to issue a "select max(...)" statement.

2) Maybe, however, all you need to know is the max value that was ever
on disk. Of course, this can be a different number than (1) because you
may have deleted rows at the upper end.

If all you need to know is (2), then you can get the answer from the
system catalogs. Here's a little script which creates a table with an
identity column, inserts and deletes some rows, and then selects the max
value that was ever in the identity column. The last query gives you the
answer to (2):

drop table s;
create table s
(
   a int generated always as identity (start with 3, increment by 3),
   b int
);

insert into s( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 );
delete from s where b = 4;
insert into s( b ) values ( 11 ), ( 12 ), ( 13 ), ( 14 );

select * from s order by b;

-- now find the max value that was ever put in the identity column
select c.autoincrementvalue - c.autoincrementinc
from sys.syscolumns c, sys.systables t
where t.tablename = 'S'
and t.tableid = c.referenceid
and c.columnname = 'A';

For more information, please see the Reference Guide section describing
the SYSCOLUMNS system catalog.

Hope this helps,
-Rick

Amir Michail wrote:
> Hi,
>
> I was wondering what is the most efficient way to get the max
> automatically generated row id.
>
> select max(...) is slow.
>
> Amir
>
>