|
|
Posted by Hugo Kornelis on 09/26/06 19:15
On 26 Sep 2006 03:18:17 -0700, nano2k wrote:
>Hi Hugo
>
>Thank you for your quick reply.
>
>It's hard for me to provide all data that you need (and I fully
>understand the needing) because all (many) objects involved (SP,
>tables, indexes, triggers) are big (as number of lines and also as
>complexity).
Hi nano2k,
I concur with Dan that it's hard to help you without the data we need.
In general terms: you need to ensure that "old" invoices and "new"
invoices can be completely seperated. Any read or search operation on
either the "old" or "new" subset must be able to use indexes in which
potentially locked rows from the other subset can be bypassed, and any
insert, update or delete in one of the subsets should only incur such
locks that the other subset is not affected at all.
> The SP
>***may*** modify (that is, UPDATE) ***all*** invoices until 22nd
>(including). The SP runs 2h.
This is your main problem. I am 99.9% sure that this SP can be optimized
to perform much faster. I'm not talking about saving 10 or 20% on
execution time, but aboout bringing execution time back to a couple of
minutes, at most.
>My questions:
>1. What indexes should I set for my invoice table? Currently I have an
>uniqueidentifier as primary key. I suppose I should set another
>non-unique index for the [invoicedate] column.
That's not a good design, for several reasons.
First, if this uniqueidentifier is your ONLY key, then you have no
protection at all against duplicate rows. Imagine that the data entry
typists accidentally puts a paperweight on the <Enter> key - the program
will just keep on generating uniqueidentifier values and inserting the
same row over and over again.
Using a surrogate key (the official term for key values generated by the
database) can, in some cases, be a good idea, but only IN ADDITION TO
THE BUSINESS KEY. The business key is an attribute (or combination of
attributes) that uniquely identifies an instance of the object/entity
type within your Universe of Discourse.
Second, if you have to use a surrogate key, then uniqueidentifier is in
most cases the worst possible choice. In almost all cases, IDENTITY is
the preferred way to generate surrogate key values.
Since uniqueidentifiers are not monotonically increasing but generated
in a pseudo-random pattern, and since the primary key by default results
in a clustered index, insertions will often result in page splits. That
is in itself already pretty bad for INSERT performance, but in cases
when many rows in the database may be blocked (as in your scenario),
it's an open invitation to major blocking problems. Consider what
happens if an in INSERT needs to store a row in a page that happpens to
be full - the page has to be split and half the rows in it have to be
moved. But what if they are locked by another transaction? And what it
that transaction happens to be running for 2 hoours?
I don't think that this is the only cause for your locking problems, but
it's definitely one of the causes!
>2. What types of transactions should I use inside the SP, so that
>inserting new invoices will be accepted by db engine?
Use the transaction isolation level that you need to maintain the level
of integrity that your application requires.
Never use a lower level: if you are willing to sacrifice correctness for
speed, just ditch the database and program your reports to produce
random data - lots faster and cheaper!
But don't set the transaction isolation level higher than you need
either, becuase (as Dan already pointed out) higher isolation means
lower concurrency.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|