| Author |
Topic |
|
eligazit
Starting Member
14 Posts |
 Posted - 2005-03-30 : 04:31:55
|
| 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 GazitSimplementech Ltd.www.simplement-tech.com |
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2005-04-13 : 12:03:45
|
| happy April fool's day to you too... you are kidding, right? |
 |
|
|
eligazit
Starting Member
14 Posts |
Posted - 2005-04-13 : 12:06:40
|
| mmm... no....Why?Eli GazitSimplementech Ltd.www.simplement-tech.com |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-13 : 12:42:00
|
| Why are you measuring the amount of that that needs to be in inserted in terms of MB? you need to provide much, much more information, it is an extremely vague questions. Have you ever worked with a SQL database?(I admit I am looking forward to seeing where this thread is heading ....)- Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-13 : 13:21:45
|
| Why MSDE?Kristen |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-13 : 13:42:34
|
| This may be an indication of a bigger problem."perfect suit that will automates each and every one of the business processes in your company."JimUsers <> Logic |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-13 : 16:44:07
|
| Its OK chaps, I've fed the pigs and they are ready to fly ...Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-13 : 18:00:04
|
quote:
Cause Implement is Simple, To us
What does that even mean? Translation problem?Tara |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-13 : 22:20:53
|
| Contuct us !I hope they don't use Visual Studio 2005, it's still in beta .......- Jeff |
|
|
|
eligazit
Starting Member
14 Posts |
Posted - 2005-04-14 : 04:20:20
|
| My mistake, I thought that this is a serious forum, but instead I'm hearing insulates from'Dr. Cross Join' (?) And 'Almighty SQL Goddess' (?) …If you need more data on the problem, I can elaborate, but don't waste my time.About the spelling-mistakes, well my bad, my English writing skills are not so good… but everyone make mistakes (well, maybe no the 'Almighty SQL Goddess').Eli |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-14 : 08:14:31
|
| eligazit What we are trying to get at is that your company is trying to do something they are not ready for as indicated with the bad grammar and spelling on your web site. (it is like saying "hire us we do bad work") Second you are trying to do something with a desktop version that would tax a main server. Many of us spend a lot of time cleaning up poorly designed systems. This also reflects poorly on SQL developers on the whole and that includes us. JimUsers <> Logic |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-14 : 08:44:11
|
| I apologize, that wasn't too nice, but what Jim is saying is true -- if you put out a website you really should try to make sure the grammar and spelling is accurate so you can represent yourself in a positive manner. Ironically, the very forum that may have offended you (SqlTeam) would be a good place to say "hey guys, my English isn't good, can you let me know how this looks?" and people would be happy to help you out.If you want assistance with your problem, try to provide more specific details and we'll be happy to help you out. BUt the question you asked is very troubling to me for these reasons:1) you mentioned you are writing a windows application using MSDE. But, MSDE isn't an application programming language. It's a small, light-weight 1 to 4 user database engine. What front-end are you using to provide the user interface? 2) you talk about the amout of data stored in terms of MB and MB/Sec, which isn't how things are measured with databases. In addition, you provided no information regarding the kind of data you are storing, or your table schema, or business requirements. Also, is there a network involved or is everything local on a single PC? moving data accross a network will of course cause issues since SQL may not be the bottleneck but rather the speed of the network.3) most troubling of all, you indicated that a client is paying you to do this, and your questions kind of imply that perhaps you aren't fully qualified to do this job for them ....- Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-14 : 13:28:36
|
| Sorry, no offence intended. All too easy for us regulars to have idle banter, and for that to be inappropriateKristen |
|
|
|
eligazit
Starting Member
14 Posts |
Posted - 2005-04-14 : 18:53:41
|
| Well, ok, after everyone got everything out of their systems, lets talk busieness… :1. We are using MSDE due to the fact that the client wants the application to be stand-alone application, on computers around the world, without any connection between them.The application will be writing in C#.2. The data that will be stored is some events log files, that are being parsed and than inserted into the database. Later this data should be extracted to retrieve some reports (such as time between faults etc.).The data itself is limited to 1-1.5GB, so the MSDE will not be reached. 3. The performance issues are measured in MB/Sec due to our client approach – he wants to load 100MB to the data, and don't want to wait to long.4. The main answer I'm trying to get is the best way to insert large amount of rows to the database, the database scheme itself is irrelevant because it is very simple and not the main problem here.5. I'm using the BULK INSERT command to insert the log-parsed files, and it is working fine, but I'm looking for ways to optimize the insert method.if anyone knows a better way or a way of using BULK INSERT with better tuning to allow better performance when inserting large data blokes together.Eli |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-15 : 02:24:20
|
| If you want performance then putting MSDE on workstations is not going to be as good as a dedicated, carefully optimised, server.OTOH Using BULK INSERT, or BCP (does that come with MSDE, I'm not sure), is going to get the data in pretty quickly.You might want to check whether un-setting AUTO CLOSE makes a difference to your application - depends how long it holds connections open - as that is the default setting for DBs created on MSDE.You may want to DROP indexes before bulk loading, and recreate them afterwards.You may want to pre-sort your data into the order of any Clustered Index on the table being imported into - to assist creation of the Clustered Index (I think there is a Hint you can provide during import that the data is pre-sorted)Kristen |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2005-05-12 : 07:22:50
|
| Now I understand. From reading his original post, I thought he meant he planned to CONTINUALLY write 2MB/SEC of data to the database :-) |
|
|
|
|
Best way to insert data to msde