Обсуждение: select/update performance?
I need to maintain a manually counter for an id-field, but I can do this two ways. Either make a counter table (which means one select and one update) or just selecting the largest id from existing table and increment by one (just one select + one table lock). Which one is fastest? Regards, BTJ
Bjørn T Johansen wrote: > I need to maintain a manually counter for an id-field, but I can do this > two ways. Either make a counter table (which means one select and one > update) or just selecting the largest id from existing table and > increment by one (just one select + one table lock). Which one is > fastest? Is would be better to create a SEQUENCE and simply call nextval on it. Then you are assured that you'll get a unique sequence when working in a concurrent environment. It would also be guaranteed faster than interrogating tables. Hope this helps, -- Rob Fielding Development Designer Servers Ltd
Yes, but the table in question have 3 PK and only one that needs this "sequence" so I just thought instead of getting holes in the IDs I just manually handle this counter somehow.. Not a big deal but... :) BTJ On Wed, 2003-11-05 at 10:42, Rob Fielding wrote: > Bjørn T Johansen wrote: > > I need to maintain a manually counter for an id-field, but I can do this > > two ways. Either make a counter table (which means one select and one > > update) or just selecting the largest id from existing table and > > increment by one (just one select + one table lock). Which one is > > fastest? > > Is would be better to create a SEQUENCE and simply call nextval on it. > Then you are assured that you'll get a unique sequence when working in a > concurrent environment. > > It would also be guaranteed faster than interrogating tables. > > Hope this helps,
You are buying yourself trouble. The holes in the IDs are the least problem, and a generated sequence should not have any business meaning anyway. BTW, what happens when you delete a record ? That would surely leave you a hole in the IDs... Just a quick thought: if you are going to handle manually the ID creation, and lock rows/tables to do that, you will effectively serialize all transactions which create ids, because the locks are held until the transactions finish. Not to mention increased deadlock probability. Sequences were designed to overcome all these problems, so why not just use them ? Cheers, Csaba. On Wed, 2003-11-05 at 10:49, Bjørn T Johansen wrote: > Yes, but the table in question have 3 PK and only one that needs this > "sequence" so I just thought instead of getting holes in the IDs I just > manually handle this counter somehow.. Not a big deal but... :) > > > BTJ > > On Wed, 2003-11-05 at 10:42, Rob Fielding wrote: > > Bjørn T Johansen wrote: > > > I need to maintain a manually counter for an id-field, but I can do this > > > two ways. Either make a counter table (which means one select and one > > > update) or just selecting the largest id from existing table and > > > increment by one (just one select + one table lock). Which one is > > > fastest? > > > > Is would be better to create a SEQUENCE and simply call nextval on it. > > Then you are assured that you'll get a unique sequence when working in a > > concurrent environment. > > > > It would also be guaranteed faster than interrogating tables. > > > > Hope this helps, > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wednesday 05 November 2003 09:49, Bjørn T Johansen wrote: > Yes, but the table in question have 3 PK and only one that needs this > "sequence" so I just thought instead of getting holes in the IDs I just > manually handle this counter somehow.. Not a big deal but... :) Do you mean a 3-column primary key? By definition you can't have more than one primary key. If you absolutely need to have no holes in your sequence numbers (e.g. for invoices) then you will have to handle it yourself. If you can live with them, it is much easier and quicker to use a sequence. If you don't want holes, the simplest way is probably to have a "next_id" column in a system-settings table. You'll need to lock it before reading/updating and this will be a bottleneck when inserting new rows. -- Richard Huxton Archonet Ltd
Bjørn T Johansen wrote: > Yes, but the table in question have 3 PK and only one that needs this > "sequence" so I just thought instead of getting holes in the IDs I just > manually handle this counter somehow.. Not a big deal but... :) You'd only get holes if you keep making nextval requests without using the value - say by issuing rollback. The problem with holes is actually the feature of uniqueness SEQUENCES provides. Perhaps you judge that there is too high a chance of rollback to create a sufficient number of holes to warrant not using a SEQUENCE. It's all down to your application and specific situation I guess however your counter table idea sounds exactly like what SEQUENCE provides, without any of the guarantees. I think I'd still recommend using a SEQUENCE for anything but the most profound reason :) -- Rob Fielding rob@dsvr.net Development Designer Servers Ltd
Ok you all, I cave... I will use sequences.... :) BTJ On Wed, 2003-11-05 at 11:46, Rob Fielding wrote: > Bjørn T Johansen wrote: > > Yes, but the table in question have 3 PK and only one that needs this > > "sequence" so I just thought instead of getting holes in the IDs I just > > manually handle this counter somehow.. Not a big deal but... :) > > You'd only get holes if you keep making nextval requests without using > the value - say by issuing rollback. The problem with holes is actually > the feature of uniqueness SEQUENCES provides. Perhaps you judge that > there is too high a chance of rollback to create a sufficient number of > holes to warrant not using a SEQUENCE. > > It's all down to your application and specific situation I guess however > your counter table idea sounds exactly like what SEQUENCE provides, > without any of the guarantees. > > I think I'd still recommend using a SEQUENCE for anything but the most > profound reason :)