Thread View: borland.public.jdatastore
5 messages
5 total messages
Started by John T. Dow
Tue, 20 Dec 2005 18:09
MAX(RECORDNO) performance bad - solution found?
Author: John T. Dow
Date: Tue, 20 Dec 2005 18:09
Date: Tue, 20 Dec 2005 18:09
17 lines
1672 bytes
1672 bytes
I have a table with 660,000 rows and SELECT MAX(RECORDNO) FROM TABLE takes 15 or so seconds, which is totally unacceptable as this needs to be done whenever a new row is added. RECORDNO is a 6 character string with values like 660123. It is indexed of course (primary key) but JDatastore doesn't seem to realize that. I just discovered that a little trick seems to make the optimizer use the index. SELECT MAX(RECORDNO) FROM TABLE WHERE RECORDNO >= '000000' I first tried WHERE RECORDNO <= '999999' but find no difference. It seems that anything to get the optimizer to use the index for a test of RECORDNO's value then makes the index available to find the max value. Anybody else see this behavior?
Re: MAX(RECORDNO) performance bad - solution found?
Author: Lori M Olson [TeamB]
Date: Tue, 20 Dec 2005 20:02
Date: Tue, 20 Dec 2005 20:02
40 lines
2249 bytes
2249 bytes
John T. Dow wrote:
> I have a table with 660,000 rows and SELECT MAX(RECORDNO) FROM TABLE
> takes 15 or so seconds, which is totally unacceptable as this needs to
> be done whenever a new row is added.
>
> RECORDNO is a 6 character string with values like 660123. It is indexed
> of course (primary key) but JDatastore doesn't seem to realize that.
>
> I just discovered that a little trick seems to make the optimizer use
> the index.
>
> SELECT MAX(RECORDNO) FROM TABLE WHERE RECORDNO >= '000000'
>
> I first tried WHERE RECORDNO <= '999999' but find no difference. It
> seems that anything to get the optimizer to use the index for a test of
> RECORDNO's value then makes the index available to find the max value.
>
> Anybody else see this behavior?
>
Which version of JDataStore are you using?
--
Regards,
Lori Olson [TeamB]
------------
Save yourself, and everyone else, some time and search the
newsgroups and the FAQ-O-Matic before posting your next
question.
Google Advanced Newsgroup Search
http://www.google.ca/advanced_group_search
Other Newsgroup Searches:
http://www.borland.com/newsgroups/ngsearch.html
Joi Ellis's FAQ-O-Matic:
http://www.visi.com/~gyles19/fom-serve/cache/1.html
Re: MAX(RECORDNO) performance bad - solution found?
Author: John T. Dow
Date: Tue, 20 Dec 2005 21:35
Date: Tue, 20 Dec 2005 21:35
27 lines
1951 bytes
1951 bytes
JDataStore 7 Lori M Olson [TeamB] wrote: > John T. Dow wrote: > >> I have a table with 660,000 rows and SELECT MAX(RECORDNO) FROM TABLE >> takes 15 or so seconds, which is totally unacceptable as this needs to >> be done whenever a new row is added. >> >> RECORDNO is a 6 character string with values like 660123. It is >> indexed of course (primary key) but JDatastore doesn't seem to realize >> that. >> >> I just discovered that a little trick seems to make the optimizer use >> the index. >> >> SELECT MAX(RECORDNO) FROM TABLE WHERE RECORDNO >= '000000' >> >> I first tried WHERE RECORDNO <= '999999' but find no difference. It >> seems that anything to get the optimizer to use the index for a test >> of RECORDNO's value then makes the index available to find the max value. >> >> Anybody else see this behavior? >> > > Which version of JDataStore are you using? >
Re: MAX(RECORDNO) performance bad - solution found?
Author: Quinn Wildman
Date: Wed, 21 Dec 2005 18:18
Date: Wed, 21 Dec 2005 18:18
23 lines
1969 bytes
1969 bytes
Have you tried a descending index to see if it makes a difference? My hunch is no, but it is worth a try. John T. Dow wrote: > I have a table with 660,000 rows and SELECT MAX(RECORDNO) FROM TABLE > takes 15 or so seconds, which is totally unacceptable as this needs to > be done whenever a new row is added. > > RECORDNO is a 6 character string with values like 660123. It is indexed > of course (primary key) but JDatastore doesn't seem to realize that. > > I just discovered that a little trick seems to make the optimizer use > the index. > > SELECT MAX(RECORDNO) FROM TABLE WHERE RECORDNO >= '000000' > > I first tried WHERE RECORDNO <= '999999' but find no difference. It > seems that anything to get the optimizer to use the index for a test of > RECORDNO's value then makes the index available to find the max value. > > Anybody else see this behavior? >
Re: MAX(RECORDNO) performance bad - solution found?
Author: John T. Dow
Date: Wed, 21 Dec 2005 19:13
Date: Wed, 21 Dec 2005 19:13
33 lines
2312 bytes
2312 bytes
Yup. I've tried everything I could think of, including specifying sort order (up and down). I had been hard coding a largish value, saying WHERE RECORDNO >= '600000', and that was fast, but not a good solution obviously. Then I tried < '999999' and found it worked quite well (maybe not quite as fast as specifying 660000, not sure). Finally, >= '000000' seems to be as fast. Quinn Wildman wrote: > Have you tried a descending index to see if it makes a difference? My > hunch is no, but it is worth a try. > > John T. Dow wrote: > >> I have a table with 660,000 rows and SELECT MAX(RECORDNO) FROM TABLE >> takes 15 or so seconds, which is totally unacceptable as this needs to >> be done whenever a new row is added. >> >> RECORDNO is a 6 character string with values like 660123. It is >> indexed of course (primary key) but JDatastore doesn't seem to realize >> that. >> >> I just discovered that a little trick seems to make the optimizer use >> the index. >> >> SELECT MAX(RECORDNO) FROM TABLE WHERE RECORDNO >= '000000' >> >> I first tried WHERE RECORDNO <= '999999' but find no difference. It >> seems that anything to get the optimizer to use the index for a test >> of RECORDNO's value then makes the index available to find the max value. >> >> Anybody else see this behavior? >>
Thread Navigation
This is a paginated view of messages in the thread with full content displayed inline.
Messages are displayed in chronological order, with the original post highlighted in green.
Use pagination controls to navigate through all messages in large threads.
Back to All Threads