Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Performance Expand / Collapse
Author
Message
Posted Tuesday, November 17, 2009 3:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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.


Post #819913
Posted Tuesday, November 17, 2009 3:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:04 AM
Points: 2,728, Visits: 1,116
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
Post #819927
Posted Tuesday, November 17, 2009 4:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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.
Post #819934
Posted Tuesday, November 17, 2009 4:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
same index are created on table for both cases.
Post #819935
Posted Tuesday, November 17, 2009 4:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #819938
Posted Tuesday, November 17, 2009 4:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 24, 2014 1:16 AM
Points: 347, Visits: 412
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


Post #819942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse