SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Performance


Database Performance

Author
Message
mayank jarsaniya
mayank jarsaniya
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 427
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 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.
Silverfox
Silverfox
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3612 Visits: 1161
Not exactly sure what your question is, your tests are very generalised.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
mayank jarsaniya
mayank jarsaniya
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 427
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.
mayank jarsaniya
mayank jarsaniya
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 427
same index are created on table for both cases.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91291 Visits: 45285
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mayank jarsaniya
mayank jarsaniya
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 427
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search