| Author |
 Topic  |
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
 Posted - 08/22/2007 : 10:49:11
|
quote:
Originally posted by combi
Hi.
What about calculated values?
When I try to bulkcopy a table with a calculated column, i get a sqlexception.
Any Ideas?
Thany You.
Chris.
Specify the column mappings and exclude the calculated column. The article explains how to do this.
- Jeff
http://weblogs.sqlteam.com/JeffS
|
 |
|
|
wieitan
Starting Member
Israel
6 Posts |
Posted - 06/09/2008 : 05:09:26
|
Hello
I have a massive performance problem, when trying to insert a large amount of input data into SQL Server. The data is stored in 60 (more or less) tables.
Does this mechanism of SqlBulkCopy works also on
DataSet - Collection of several Tables ?
Thanks in advance
Eitan 
Eitan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/09/2008 : 06:20:16
|
you have to load each table by itself.
what kind of a perf problem?
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
wieitan
Starting Member
Israel
6 Posts |
Posted - 06/09/2008 : 08:07:57
|
Hello!
Thanks for the quick reply.
I Insert into Database by using SqlDataAdapter.Insert Command
The performance problem are as follows:
- At first it seems work O.K. (Counting every 500 Event ==
Rows of primary table).
- After a porion of time, insert rate becomes slower and slower.
The Task Manager shows: less ans less activity
of Sql Server & My Application processes.
I understand I need to insert many rows in one operation,
this is the reason I thaught SqlBulkCopy would help me.
I need to insert data into several tables together becuse of thier
connectivity. I hope SqlBulkCopy can be tied somehow to DataSet
I hpoe I make myself clear.
Thanks
Eitan
Eitan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/09/2008 : 08:29:42
|
well it isn't.
SqlBulkCopy inserts per table. you have to disconnect your PK FK constraints
or load the tables in the correct order.
and try using the Tablock when loading. this will speed up bulk insert but will prevent other processes to change data while bulk insert is in progress.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
wieitan
Starting Member
Israel
6 Posts |
Posted - 06/10/2008 : 14:54:35
|
Hello!
Thanks again for the quick reply.
I woukd like to ask the following:
1) Can I solve my performance problem
(slower & slower for large amount of data), by adding rows to
Tables exist within In Memory DataSet.
Every 10,000 Event(== Rows), I shall call DataSet.AcceptChanges ()
Today I insert every row direct into DB, by using DataAdapter.
Is this approach better then previous one ?
2) If the answer to first question can be true, How shall I connect
between DataSet & DataAdapter, used for Connection & Transaction ?
Thanks again
Eitan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/10/2008 : 14:57:20
|
so why don't you use SqlBulkCopy?
adding so many rows to a dataset isn't reccomended and you can run out of memory if there are to many rows.
dataset was never meant for bulk inserts.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
wieitan
Starting Member
Israel
6 Posts |
Posted - 06/10/2008 : 15:13:26
|
Hello!
1) I think I can not use SqkBulkCopy, because some of the tables are
connected by PK & FK.
2) About memory run out. I mean every 10,000 (or other constant),
The contents of DataSet shall be inseted to DB, and all rows
shall be deleted from DB. Do you think insert performance
can be better from other approach, Every row is inserted into DB ?
Thank you very much for your support
Eitan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/10/2008 : 15:18:24
|
so drop PK-FK relationships before you insert and recreate them after that.
be aware that when doing inserts with dataadapter you are inserting row per row. this means that your insert command is
getting called once for each row. this is tremendous overhead.
again this is not the way to go for bulk inserts.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
wieitan
Starting Member
Israel
6 Posts |
Posted - 06/11/2008 : 14:37:20
|
Hello!
Thanks again for the quick reply.
I would like to ask about inset perfromance
(again, hopefully for the last time):
- Can I use SqlBulkCopy for each DataTable within DataSet,
without deleting the relationship: PF - FK ?
- Can I insert via SqlBulkCopy the Tables with PK first
and then load the tables with FK after that table ?
- Every N:M connection is being divide into 1:N connection:
PF - FK.
Thank you very much for your support
Eitan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/11/2008 : 14:45:13
|
ok once more:
forget the dataset.
load the tables with SqlBulkCopy from the parent to the child in correct order and that's it.
why are you so stuck on that dataset?
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
wieitan
Starting Member
Israel
6 Posts |
Posted - 06/11/2008 : 15:39:17
|
Hi!
First of all Thank you for your answers & patience.
I shall try it tomorrow at work.
Yoy ask: "why are you so stuck on that dataset" ?
As I see it:
- Dataset contains relations between DataTables (PK-FK).
- DataSet is a collection of all data in DataTables.
Thank you very much
Eitan |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/11/2008 : 15:53:08
|
yes it is.
but for the purpose of batch loading it is completly useless.
_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
karthi2009
Starting Member
23 Posts |
Posted - 05/20/2009 : 05:30:24
|
Hi,
I am using SqlBulkCopy Class to import data from Excel File. Currently I am facing an issue with DateTime Type Column.
Database Table Desc
--------------------
emp# int,
empname varchar(100),
timein datetime (Date with Time)
DataTable Columns Spec
-----------------------
emp# string,
empname string,
date string,
time string
before importing the data, I merge date and time columns to match database table column named 'timein'.
DateTime dTime = Convert.ToDateTime(date + " " + time);
But when I try to execute it, I am getting an exception in WriteToServer() method. The exception I am getting is "System.FormatException: String was not recognized as a valid DateTime.".
Please guide me out in this regard.
Thanks in advance.
Karthikeyan
|
|
|
|
Topic |
|
Article: Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server
