To your remark in the comments to your question:
"...SavingChanges (for each
record)..."
That's the worst thing you can do! Calling SaveChanges()
for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:
- Call
SaveChanges()
once after ALL records.
- Call
SaveChanges()
after for example 100 records.
- Call
SaveChanges()
after for example 100 records and dispose the context and create a new one.
- Disable change detection
For bulk inserts I am working and experimenting with a pattern like this:
using (TransactionScope scope = new TransactionScope())
{
MyDbContext context = null;
try
{
context = new MyDbContext();
context.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
foreach (var entityToInsert in someCollectionOfEntitiesToInsert)
{
++count;
context = AddToContext(context, entityToInsert, count, 100, true);
}
context.SaveChanges();
}
finally
{
if (context != null)
context.Dispose();
}
scope.Complete();
}
private MyDbContext AddToContext(MyDbContext context,
Entity entity, int count, int commitCount, bool recreateContext)
{
context.Set<Entity>().Add(entity);
if (count % commitCount == 0)
{
context.SaveChanges();
if (recreateContext)
{
context.Dispose();
context = new MyDbContext();
context.Configuration.AutoDetectChangesEnabled = false;
}
}
return context;
}
I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.
For the performance it is important to call SaveChanges()
after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites, SaveChanges
doesn't do that, the entities are still attached to the context in state Unchanged
. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.
Here are a few measurements for my 560000 entities:
- commitCount = 1, recreateContext = false: many hours (That's your current procedure)
- commitCount = 100, recreateContext = false: more than 20 minutes
- commitCount = 1000, recreateContext = false: 242 sec
- commitCount = 10000, recreateContext = false: 202 sec
- commitCount = 100000, recreateContext = false: 199 sec
- commitCount = 1000000, recreateContext = false: out of memory exception
- commitCount = 1, recreateContext = true: more than 10 minutes
- commitCount = 10, recreateContext = true: 241 sec
- commitCount = 100, recreateContext = true: 164 sec
- commitCount = 1000, recreateContext = true: 191 sec
The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.
Create a diagram for existing database schema or its subset as follows:
- Click File → Data Modeler → Import → Data Dictionary.
- Select a DB connection (add one if none).
- Click Next.
- Check one or more schema names.
- Click Next.
- Check one or more objects to import.
- Click Next.
- Click Finish.
The ERD is displayed.
Export the diagram as follows:
- Click File → Data Modeler → Print Diagram → To Image File.
- Browse to and select the export file location.
- Click Save.
The diagram is exported. To export in a vector format, use To PDF File, instead. This allows for simplified editing using Inkscape (or other vector image editor).
These instructions may work for SQL Developer 3.2.09.23 to 4.1.3.20.
Best Answer
Whether your model is right or not depends on whether your model can capture all the business scenarios you described above - and IMHO, your model does that quite well (Congratulation!); barring few small anomalies such as, not having the
Discount
attribute inOrder
entity to capture overall discount on sales (which is a possible business scenario).But when you create model like this, you should consider the final purpose of this model:
In your case, I believe your final goal is to capture business transactions by designing on Online Transaction Processing (OLTP) system. OLTP models are generally normalised in nature (due to the obvious benefits of normalisation such as reduced redundancy, update/ delete anomaly prevention etc.) Your model is mostly in 3NF form, except for few transitive dependencies. The column
SalesTotal
in entityOrder
appears to be a derivable column (based onPrice
andDiscount
which you have already stored in other entities) and, IMO, need not be separately maintained (read redundant).Also based on your comment above,
I don't think you will be able to do it because your
Extra
entity is designed to store the currentPrice
always. YourOrderDeatils
contain the relationship betweenCar
andExtra
for any given point in time. If the prices of car and extra changes later, you will lose the old value as you will update the old price with new price. Hence, although you will be able to track theID_Car
andID_Extra
fromOrderDetails
, you won't know what were their historical prices during the time of order creation.If you want to store historical prices, consider adding one more table each with
Car
andExtra
to store past prices against dates.In the situation when a client can act as a seller, the person will exist independently in both your
Client
andSalesman
table... That's perfectly alright (Such design technique is called Vertical Separation).Alternatively, if you really need a 3NF model, then you should create a new entity -
Person
containing attributes such asname
,id
etc. ThisPerson
entity will be used to store both your client and salesman details as technically both of them are some person, but their "role" is different. Once you have thisPerson
entity, you can deleteSalesMan
andClient
entities. Then in theOrder
entity, you already have aClent ID
and aSalesman ID
attribute to define the relationship between two different persons (TheseClent ID
andSalesman ID
will beForeign Key
to thePerson ID
inPerson
entity). Whether a person is playing the role of a salesman or a client can be known fromOrder
entity.Hope this helps