|
Home > Archive > Microsoft SQL Server forum > March 2006 > speeding up inserts
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
speeding up inserts
|
|
| zelnaga@gmail.com 2006-03-29, 9:28 am |
| according to the mysql manual, multiple inserts can be sped up by
locking the table before doing them and unlocking the table afterwards.
is the same true of multiple inserts in mysql? if so, how would the
table be locked?
any insights would be appreciated - thanks!
| |
| Robert Klemme 2006-03-29, 11:28 am |
| zelnaga@gmail.com wrote:
> according to the mysql manual, multiple inserts can be sped up by
> locking the table before doing them and unlocking the table afterwards.
>
> is the same true of multiple inserts in mysql? if so, how would the
> table be locked?
>
> any insights would be appreciated - thanks!
Um, this is a newsgroup dedicated to Microsoft SQL Server. You're
probably better off checking in news:comp.databases.mysql .
Cheers
robert
| |
| Erland Sommarskog 2006-03-29, 8:26 pm |
| (zelnaga@gmail.com) writes:
> according to the mysql manual, multiple inserts can be sped up by
> locking the table before doing them and unlocking the table afterwards.
>
> is the same true of multiple inserts in mysql? if so, how would the
> table be locked?
>
> any insights would be appreciated - thanks!
In case "mysql" in the second paragraph is a type for MS SQL Server, the
answer is that you cannot really lock a table as such in MS SQL Server,
and definitely not unlock it. You can specify a locking hint to get a
table lock when you insert the first row. Note that you need to have a
user-defined transaction in progress, or else the lock will be released
since statements auto-commit in SQL Server. To "unlock" you simply commit
the transaction.
However, normally you don't play these games for insert statments. It's
better to let SQL Srever choose the locking mode.
If you need to load lots of data, you use BCP (a command-line tool) or
BULK INSERT (an SQL Statement). With these you can specify table lock to
speed up the operation.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| zelnaga@gmail.com 2006-03-29, 8:26 pm |
|
Robert Klemme wrote:
> zelnaga@gmail.com wrote:
>
> Um, this is a newsgroup dedicated to Microsoft SQL Server. You're
> probably better off checking in news:comp.databases.mysql .
err... sorry. I should have said mssql in the second paragraph. i'm
more familiar with mysql, hence my example in the first paragraph.
unfortunately, i'm not sure how to translate this to mssql, hence the
second paragraph.
also, why would i quote the manual of some dbms and then ask if "the
same" is true of that dbms?
bleh.
|
|
|
|
|