"Erland Sommarskog" <esquel@[EMAIL PROTECTED]
> wrote in message
news:Xns9AD1ED7E17952Yazorman@[EMAIL PROTECTED]
> Arved Sandstrom (asandstrom@[EMAIL PROTECTED]
) writes:
>> 1) I saw that MySQL has a delayed insert feature, that is, inserts wait
>> until the table is not in use by any other thread, plus inserts get
>> written as a block. I can see this being useful if there are lots of
>> selects, and an insert can take a fair bit of time. Does SQL Server
have
>> anything like this?
>
> One wonders what happens with the connection that performs the INSERT?
> Does it block while the INSERT is delayed? Or does this mean that if a
> process inserts, commits and then select for its inserted data, it may
> not be there? Appears likely violation of the ACID principle to me.
The INSERT DELAYED in MySQL returns to the client immediately...there is
no
blocking. The insert data are queued in memory until the insert. This
means
that delayed rows are not visible to SELECT statements until the actual
insert. The MySQL do***entation
(http://dev.mysql.com/doc/refman/6.0/en/insert-delayed.html)
has a lot of
caveats and explanations, which upon further perusal sort of answers my
question: no, one should not normally use the feature.
> No, there is nothing like that in SQL Server. (There is a deferred
deleted
> model, where rows are not always physically deleted immediately, but
> are freed up by a cleanup process, but the rows are logically deleted,
> so this is just an implementation detail.)
>
>> 2) If the new data is being delivered in batches (large batches) it
>> seems to me that scheduling a drop index - bulk insert - create index
>> would be better, at least in many cases, since the index would get
>> rebuilt from scratch. Is this correct?
>
> As always when it comes to performance issues: you will have to
benchmark.
True enough.
> --
> Erland Sommarskog, SQL Server MVP, esquel@[EMAIL PROTECTED]
>
> Books Online for SQL Server 2005 at
>
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
AHS
|
