Database level lock: oltp

Showing posts with label oltp. Show all posts

Saturday, February 25, 2012

database design question

Hi. I have a new OLTP database requirement, and was wondering what columns
do some of you use in your db design for primary keys, since identity
columns can't really be used for this? I was thinking of using the select
convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
Other suggestions would be appreciated. Thanks.
Alex IvascuWhy are you saying that identity cannot be used for primary keys? I would estimate that perhaps 95%
of the SQL Server population who uses surrogate keys uses identity. And indeed, identity will find
its way into the next ANSI standard.
Having said that, you might want to read Joe Celko's posts regarding identity, even though many
points are inaccurate. So, if you do that, you should also read a good response to that (one from
Tony Rogerson, for instance). You can search the archives using the link at the bottom of my URL.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> Hi. I have a new OLTP database requirement, and was wondering what columns
> do some of you use in your db design for primary keys, since identity
> columns can't really be used for this? I was thinking of using the select
> convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
> Other suggestions would be appreciated. Thanks.
> Alex Ivascu
>
>|||Hi, Tibor
Look, I am not lawyer of Celko and I agree with you that some his posts are
inaccurate.
Just yesterday I faced the problem where I was need to update the column
that is PK with Identity property.
At beginining our business logic did not allow to do such things but as you
know, came a new requirement of production in our company.
I think we falled in these 5 % that you estimated that should be used
identity property for promary key .
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$ADar5lDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Why are you saying that identity cannot be used for primary keys? I would
estimate that perhaps 95%
> of the SQL Server population who uses surrogate keys uses identity. And
indeed, identity will find
> its way into the next ANSI standard.
> Having said that, you might want to read Joe Celko's posts regarding
identity, even though many
> points are inaccurate. So, if you do that, you should also read a good
response to that (one from
> Tony Rogerson, for instance). You can search the archives using the link
at the bottom of my URL.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> > Hi. I have a new OLTP database requirement, and was wondering what
columns
> > do some of you use in your db design for primary keys, since identity
> > columns can't really be used for this? I was thinking of using the
select
> > convert(int, convert(varbinary, newid())) to insert. Any drawbacks to
this?
> > Other suggestions would be appreciated. Thanks.
> >
> > Alex Ivascu
> >
> >
> >
>|||>> was wondering what columns do some of you use in your db design for
primary keys, <<
There is no "magic, one-size-fits-all, universal" answer. Designing a
database is hard work! So, for each and every individual table:
1) Is there an ISO standard?
2) Is there a national standard?
3) Is there an industry standard?
Examples: GTIN for retail, VIN for vehicles, ISBN for books and
publications.
Other questions to ask, if you have to invent a code:
1) Will a human being input it? So, does it need an easy syntax
(airport codes)? Does it need a check digit(ISBN)? What kind of check
digit algorithm?
2) How do I verifiy it in the reality represented in my data model?
Internally or with a trusted external source?
External: government tax numbers. Internal: ISO tire sizes (155R15 =155 cm width, Radial construction, 15 inch diameter; verify with a ruler
and your eyes).
I have four chapers on designing codes in DATA & DATABASES which you
might want to read.
--CELKO--
=========================== Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

database design question

Sunday, February 19, 2012

Database design

I have to design choices
Here's the situation (OLTP):
I have a WorkOrder table that could contain millions of record and a
WorkOrderId primary key. The record is inserted and then updated once
by a technician. The workorder is created when the user access a web
page and then updated when the user save the work order. The work
order is almost never canceled after it's creation.
The choices
1) I do all my processing directly in the table.
2) I have a temp table which is commited to the real table when the
technician saves the work orders.
What do you think? Are all these OLTP order systems working with temp
tables. Is there some things I should consier like having no indexes
on a temp table so insert is faster and response time is better. I
have a low number of user and not many concurrent requests but a lot of
data can accumulate over time.
Thanks for your time.
ObIt may not be as complicated as you apparently think. Just present the Web
page to the user - collect all then info there and then when the user goes
to save it, do an insert and you're done.
But have I missed something?
You talk about "all my processing directly in the table" or "do all my
processing..."
What processing is that? I couldn't imagine what it could possibly be that
would require any database involvement (temp table or otherwise) prior to
the user saving the new work order.
We could provide you with more specific guidance if you tell us what all
this processing is that you refer to that is apparently required on creation
of new work orders.
-HTH
"oberyn" <oberyn1martell@.yahoo.com> wrote in message
news:1141966631.129590.181350@.p10g2000cwp.googlegroups.com...
>I have to design choices
> Here's the situation (OLTP):
> I have a WorkOrder table that could contain millions of record and a
> WorkOrderId primary key. The record is inserted and then updated once
> by a technician. The workorder is created when the user access a web
> page and then updated when the user save the work order. The work
> order is almost never canceled after it's creation.
> The choices
> 1) I do all my processing directly in the table.
> 2) I have a temp table which is commited to the real table when the
> technician saves the work orders.
> What do you think? Are all these OLTP order systems working with temp
> tables. Is there some things I should consier like having no indexes
> on a temp table so insert is faster and response time is better. I
> have a low number of user and not many concurrent requests but a lot of
> data can accumulate over time.
> Thanks for your time.
> Ob
>|||ok I tried to simplify my situation to much. What I have is a work
order that can be modified multiple times before saving. Right now we
have a flag that represents the state of the work order each time it is
modified. And the same flag is set to completed when the work order is
saved. This state managing could all be done in an object and commited
to the database when the user saves the work order but we decided to
put it in the database to persist the state for up to 1 day because we
don't to want to have many pending asp.net sessions persisted (sql
server session state) all day and the technician has to be able to
resume the work order at any time during the day.
Should I have some sort of Cart table to hold the work orders and
commit them to the WorkOrder table when the flag is set to completed or
inserting and updating the main table (work order) with millions of
record is ok.?
Jordan wrote:
> It may not be as complicated as you apparently think. Just present the Web
> page to the user - collect all then info there and then when the user goes
> to save it, do an insert and you're done.
> But have I missed something?
> You talk about "all my processing directly in the table" or "do all my
> processing..."
> What processing is that? I couldn't imagine what it could possibly be that
> would require any database involvement (temp table or otherwise) prior to
> the user saving the new work order.
> We could provide you with more specific guidance if you tell us what all
> this processing is that you refer to that is apparently required on creati
on
> of new work orders.
> -HTH
>
>
> "oberyn" <oberyn1martell@.yahoo.com> wrote in message
> news:1141966631.129590.181350@.p10g2000cwp.googlegroups.com...|||Instead of a flag in your main table, why don't you have a
WorkOrderStatus table? Minimize UPDATEs, and simply INSERT new rows to
the Status table; when you want to find out what status a WorkOrder
has, join it to the Status table and retrieve the most recent status?
Stu|||Okay, then given this additional information I would go with the
recommendation earlier by Stu. His recommendation makes a lot of sense
because each of your "work order" thingies can each exist in multiple states
over time. Each of those states goes into a "Status" table which gives you
the benefit of knowing a date/time (if you record that, of course) of each
status change. A status "flag" column in your WorkOrder table would not
permit you to record or know the history of the order. Plus, from a data
modeling point of view an [order] is just an [order] regardless of its
current status ([current status] is just an attribute). Modeling an
[incomplete order] as a separate entity from a [completed order] would
likely be a misconceptualization of the data.
Finally and alternatively, you could persist the state data in a cookie (and
set it to expire after 24 hours) on the client and only store completed
orders in the database. But if you do this then you lose out on the status
history info and have a more fragile system to support (e.g., users with
cookies disabled, switching from one computer to another thoughout the day,
etc).
-HTH
"oberyn" <oberyn1martell@.yahoo.com> wrote in message
news:1141998248.065606.52840@.z34g2000cwc.googlegroups.com...
> ok I tried to simplify my situation to much. What I have is a work
> order that can be modified multiple times before saving. Right now we
> have a flag that represents the state of the work order each time it is
> modified. And the same flag is set to completed when the work order is
> saved. This state managing could all be done in an object and commited
> to the database when the user saves the work order but we decided to
> put it in the database to persist the state for up to 1 day because we
> don't to want to have many pending asp.net sessions persisted (sql
> server session state) all day and the technician has to be able to
> resume the work order at any time during the day.
> Should I have some sort of Cart table to hold the work orders and
> commit them to the WorkOrder table when the flag is set to completed or
> inserting and updating the main table (work order) with millions of
> record is ok.?
>
> Jordan wrote:
>|||I disagree. Finding the "latest status" for a workitem would require
either a "group by" or some flag to indicate which row is the latest
which frequently causes problems and requires an insert to also do an
update.
As I see it, a WorkItem only has 2 status 'Complete' and 'In Process'
and therefore will work fine with just the status on the main table.
If properly indexed, SQL server will have no problem updating 1 row out
of millions. If however, the status implies some sort of workflow then
storing each status, as well as other relative audit data historically
may have some value.
You should never rely on storing state in anything other than the
database because servers and computers can be rebooted and any objects
lost, cache cleared etc...|||Differences of opinion, I guess. As I get more experience with
database design, I've come to realize that the only constant thing in
design that you can truly rely on is that the user's specifications
will change the day after you roll something out :) I try to store as
much information as I can, and a bit flag doesn't tell me how long
something has been in that status, nor does it give me any sense of
direction (e.g., how do you handle "re-opened" tickets?). In other
words, even if the user doesn't mention workflow, I try to design
around that.
It has been my experience, though, that INSERTS are a lot less
expensive than UPDATE's, regardless of your indexes. I'd be
interested in understanding what problems you've had when dealing with
GROUP BY, and finding the latest row; it might influence some of the
decisions I'm making on a new project.
Stu|||RE:
<<Finding the "latest status" for a workitem would require either a "group
by" or some flag to indicate which row is the latest>>
Not true at all. If a date column in the Status table defualt value
automatically inserted when the status row is inserted (representing the
date/time the row was inserted), then it is real easy to get the latest
status without any GROUP BY clause - e.g., SELECT TOP N ... ORDER BY
date_column DESC.
RE:
<<a WorkItem only has 2 status 'Complete' and 'In Process'>>
You have no way to know that - and even if it were true today the
requirements could change tomorrow. What if there were 5 steps towards
completion - each of significance to the business. You would of course argue
that an int column could store a flag to indicate which of the 5 steps the
work order is at. The problem there is that you fail to record the
historical information about when the status changed, the employee who
changed the status, etc. Your little flag column really misses a lot of
potentially valuable information.
RE:
<< You should never rely on... >>
You should never say never. Really. If you are correct, then why are there
so many options for storing or managing state (database, View_State,
Application, Session, cookies, client-side objects persisted to file on the
client via XML (easily facilitated in .NET). There's a lot more to life (and
state) than databases.
-J
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142017670.700641.290070@.i39g2000cwa.googlegroups.com...
>I disagree. Finding the "latest status" for a workitem would require
> either a "group by" or some flag to indicate which row is the latest
> which frequently causes problems and requires an insert to also do an
> update.
> As I see it, a WorkItem only has 2 status 'Complete' and 'In Process'
> and therefore will work fine with just the status on the main table.
> If properly indexed, SQL server will have no problem updating 1 row out
> of millions. If however, the status implies some sort of workflow then
> storing each status, as well as other relative audit data historically
> may have some value.
> You should never rely on storing state in anything other than the
> database because servers and computers can be rebooted and any objects
> lost, cache cleared etc...
>|||Ok the issue is a lot clearer for me now. Thank you all for your
answers.

Saturday, February 25, 2012

database design question

database design question

Sunday, February 19, 2012

Database design

Database level lock

Blog Archive

About Me