Best way to Insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best way to Insert

Author

Topic

eligazit
Starting Member

14 Posts

Posted - 2005-03-30 : 04:30:17

Hello.
I'm creating a stand-alone windows application using the MSDE server.
Performance issues in this application are big-deal to my client.
I need to insert data to the database in rate of – 2MB / sec.
Each insert action should insert ~ 10-40 MB to the database.

My questions are:
1. What is the fastest way to INSERT data to the database? Using insert from c# code, scripts, stored procedure, other?
2. What is the fastest way to retrieve information from the database?

Thanks.

Eli Gazit
Simplementech Ltd.
www.simplement-tech.com

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-03-30 : 07:08:13

1. BCP...read more about it here and in BOL...depends on input coming from some non-interactive data source...otherwise SP's.

2. SP's.

and in both cases....damn good hardware + an excellent DBA would be of great use!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-30 : 08:44:50

1. BULK INSERT or bcp
2q. How do You want to retrieve the data ?
Export data in large chunks, or do You mean just querying the database.
2a. bcp, good db & index design + SP's.

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 08:58:44

You might want to look at the size limitations for msde.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_67ax.asp

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eligazit
Starting Member

14 Posts

Posted - 2005-03-30 : 16:00:33

Thanks everyone for the quick and professional replays.

1. What are the differences between BCP and INSERT BULK?
2. about the data retrieving, I'm will address this subject after performing the INSERT good enough, so I'll keep u posted on that matter.

Thanks again, I'm starting to read the spec, and it seems to be a good way to do it.

Eli

Eli Gazit
Simplementech Ltd.
www.simplement-tech.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-30 : 16:11:47

1. This is straight from BOL:

quote:
The BULK INSERT statement allows you to bulk copy data to an instance of SQL Server using the functionality of the bcp utility with a Transact-SQL statement, rather than from the command prompt.

2. Please do

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-30 : 18:19:04

Did you look at the size limitation and have you estimated the max size of the database?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eligazit
Starting Member

14 Posts

Posted - 2005-03-30 : 18:27:25

I'll give a system overview:
The application is a log analyzer.
It takes log files (txt) and parsed each line by specified rules, to build a data item (event of some machines).
Now I should insert this items to the database (size should from 10MB up to 500MB).
After insert, the user should be able to watch reports that are derived from the database, such has - time between events.

This should be a stand-alone windows application using SQL MSDE.
Hope it should give some led to any solution...

Eli Gazit
Simplementech Ltd.
www.simplement-tech.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-30 : 21:00:37

BTW, have you seen this tool yet:

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

If you've used previous versions of Log Parser, check this one out anyway, it's greatly improved. It may help you with the parsing process, and you can direct the output to a formatted file that is easier and faster to import with bcp or BULK INSERT. You may even be able to create reports with it without having to import into MSDE at all.

eligazit
Starting Member

14 Posts

Posted - 2005-03-31 : 02:36:07

Can u link me to a good example of using the BULK INSERT inside the c# code?
(I mean, I've managed to send the command by execute-non-query by I'm unable to build txt file with all the data that has no error).

Thanks again for all your help.

Eli Gazit
Simplementech Ltd.
www.simplement-tech.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-31 : 08:17:55

BULK INSERT works like any other SQL command, you can get the exact syntax from Books Online.

BULK INSERT can only load files that are visible to the SQL Server, either on its local drives or network paths visible to it. bcp can work from any client computer that can ping that SQL Server. It's a pull (BULK INSERT) vs. a push (bcp) operation. BULK INSERT may not be the easiest way to get the data into the database; from what you've described it doesn't sound like it would be.

We'd need more detail on what you're trying to do and how you're doing it to give a better answer.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-31 : 09:40:02

>> I'm unable to build txt file with all the data that has no error
You mean that You encounter errors in the textfile ?

Ususally I wrap the BULK INSERT or the bcp commands in a Stored Procedure.
In the stored procedure I do:
EXEC master.dbo.xp_executesql @bulkinsertcommand
or if it is bcp:
EXEC master.dbo.xp_cmdshell @bcpcommand, NO_OUTPUT

I call the procedure from c#, just like a regular proc.
The paramaters to the proc can be filepath, filename, destination table and such things.

Most commonly a client creates a textfile somewhere on the filesystem (client pushes files to fs).
Then the SQL Server is told to BULK INSERT that file (pull the file into sql server).

rockmoose

eligazit
Starting Member

14 Posts

Posted - 2005-03-31 : 13:55:21

I've managed to use the BULK INSERT command.
The application steps now are as follows:
1. Read log files from selected directories, parse them, and creates a text file that is tab delimiter containing all the fields I want to insert to the db.
2. Saves the file to the hard drive
3. Send the bulk insert command as follows:

SqlConnection connection = new SqlConnection(connection_string);
SqlCommand command = new SqlCommand("", connection);
command.CommandText = "BULK INSERT testTbl FROM 'C:\\mydata.txt' " +
"WITH (ROWS_PER_BATCH=5000)";
connection.Open();
int lines = command.ExecuteNonQuery();
connection.Close();

All data parsed and insert to the db in 2Mb/sec.

I still have some problems:
1. using sp to bulk insert, how to do it? is it necessary in MSDE?
2. Converting datetime from string in the text file to datetime format in the db.

Thanks again...

Eli Gazit
Simplementech Ltd.
www.simplement-tech.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-31 : 19:03:30

1. It is recommended, not necessary.
Basically in the sp You dynamically create the same SQL that you do in c#.
Then in the sp you execute the dynamic sql with sp_executesql.
2. use the CONVERT function. (if You find dirty data, ISDATE function might be useful)

rockmoose

eligazit
Starting Member

14 Posts

Posted - 2005-04-03 : 01:28:07

Hi rock, I'm back, with more questions...
I hope its ok I'm using you never-ending patience.

The file I'm creating for the bulk insert has a date format such as:
dd/mm/yyyy hh:mm (derived from the log files I'm parsing)
The msde-sql table format is mm/dd/yyyy hh:mm.
Due to that, when bulk insert is executed, I'm receiving a cast exception.
I think I can solve this by code, using DateTime object of c# and converting the format to the sql format, but this method has some drawbacks:
1. Performance - each line should be converting before inserting it to the text file.
2. Scalability - this application should be run with no respect to the regional settings, and dates format may be different in each country.

Is their a better way fir that? Something like setting the date format in the creating of the table?

Thanks

Eli

Eli Gazit
Simplementech Ltd.
www.simplement-tech.com

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-03 : 05:01:11

The best solution:
Always handle the dates as: "yyyymmdd hh:mm:ss:mmm" strings in all your imports
(Optionally specifiyint the seconds:milliseconds)
That way SQL Server will always import the dates correctly, regardless of local settings.

Another solution for You is to SET DATEFORMAT xxx when You load the file.
Dates in SQL Server do not have an intrinsic format per se, that is a display issue.
Also when You convert a string to a datetime (implicitly as You are doing when loading table)
then You have to make sure that SQL server "understands" the format of the string being converted.

See the following example:

--dd/mm/yyyy hh:mm
DECLARE @datestring CHAR(16)
SET @datestring = '31/12/2005 12:12'
-- SET @datestring = '20051231 12:12' -- this will always be correct !

-- This will be ok
SET DATEFORMAT dmy
SELECT CAST(@datestring AS DATETIME) AS ok_cast
SELECT CONVERT(DATETIME,@datestring,121) AS ok_cast

-- shows current setting
DBCC USEROPTIONS

-- this will not be ok
SET DATEFORMAT mdy
SELECT CAST(@datestring AS DATETIME) AS not_ok_cast

-- show current setting
DBCC USEROPTIONS

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources