November 17, 2009 at 3:20 am
Hi,I have one test and i have test results are:
Nine Users All Using DIFFERENT Database on same instance
per user one database and perform
inserting 100k records and update same 100k records one by one (not in group).
Insert takes 1.476 minutes
Update takes 14.85 minutes
Nine Users All Using SAME Database(with single database file and log file)
Direct Insert2.24 minutes
Direct Update10.12 minutes
i think using different database per user should give performance because it has separate datafile and logfile but update takes 14.85 minutes which is more than single database 10.12 minutes.
actually i want to use partition on table to gain IO performance and more than one datafiles for a table to perform insert/update faster.
November 17, 2009 at 3:58 am
Not exactly sure what your question is, your tests are very generalised.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 17, 2009 at 4:09 am
detailed explanation:
nine Users each has one db like db1 for user1/db2 for user2/.../db9 for user9
All Using DIFFERENT Database on same instance
user1 connects to db1 and insert a record.....again insert one record until 100k records
after that it updates one record again update one record upto 100k records.
this way
Insert takes 1.476 minutes
Update takes 14.85 minutes
Nine Users All Using SAME Database user1,user2...user9 connects to database 'db9'
each user has 100k and for each records user fires 1 insert for each record upto 100k records
and after inserting 100k it will update 1 record at time upto 100k records
Direct Insert 2.24 minutes
Direct Update 10.12 minutes
i think using different database per user should give performance because it has separate datafile and logfile but update takes 14.85 minutes which is more than single database 10.12 minutes.
actually i want to use partition on table to gain IO performance and more than one datafiles for a table to perform insert/update faster.
November 17, 2009 at 4:11 am
same index are created on table for both cases.
November 17, 2009 at 4:16 am
mjarsaniya (11/17/2009)
i think using different database per user should give performance because it has separate datafile and logfile but update takes 14.85 minutes which is more than single database 10.12 minutes.
Not really, unless those files are on separate physical drives.
You're possibly seeing locking.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 4:24 am
I think locking is not possible for any case because each user just say
user1 has 1 to 100k records to insert and same for update
user 2 has 101k to 200k records...same user9 has 801k to 900k records
what i want to know as per current scenario it is advisable to create a table partition
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply