 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / January 2007very slow inserts
i need to put 100 000 into my sql ce server database sqlcecommand.commandtext("insert into table_abr (abrabr) values(abrabr)") takes ~ 2 hours what could you recomend to do Thanx, Aurimas AN [Tue, 18 Nov 2003 16:33:39 +0200]: >i need to put 100 000 into my sql ce server database >sqlcecommand.commandtext("insert into table_abr (abrabr) values(abrabr)") >takes ~ 2 hours Imagine that done in about 6 seconds. Hm? From http://40th.com/gt40/bench_gt40_arm.html PXA255/400, Insert: 16,000 items per second (insert rate: 960,000 items/minute) Read: 333,000 items per second (read rate: 20+ million items/minute) [2] > what could you recomend to do ANYTHING BUT pokey MSFT DBs! Grab the Monza applet and watch it run yourself. Zoom!  Signature40th Floor - Software @ http://40th.com/ GT40 encryption-database toolkit is it free? Matteo Cima. > AN [Tue, 18 Nov 2003 16:33:39 +0200]: > >i need to put 100 000 into my sql ce server database [quoted text clipped - 13 lines] > ANYTHING BUT pokey MSFT DBs! Grab the Monza applet and watch it run > yourself. Zoom! Use Parameterized queries. You will see significant performance gains. The SQLCE Books On line has good code samples for using Parameterized queries. If you need any pointers to optimize with Parameters, please let me know. -------------------- | From: "Aurimas Norkevicius" <aurimas_norkevicius@studentas.lt> | Subject: very slow inserts [quoted text clipped - 21 lines] | | Aurimas Hi everyone, i rebuil my application to use parametrysed queries. with no parametrized query 100 000 rows 2 hours with parametriized query 100 000 rows 0.5 hour can you suggest something else to cut that 0.5 hour to be less thanx Aurimas > i need to put 100 000 into my sql ce server database > [quoted text clipped - 6 lines] > > Aurimas One more thing you can try is partitioning your data. Cut the data down to what the user will actually use. Does the user need 100,000 rows? Or does each user only need part of the data? You can also try RDA. Setup a DB on MSDE or Sql Server. Then use RDA Pull to setup (pull data) to your SQLCE database. RDA Pull is very fast. -------------------- | From: "Aurimas Norkevicius" <aurimas_norkevicius@studentas.lt> | References: <uGYF3DerDHA.2772@TK2MSFTNGP12.phx.gbl> [quoted text clipped - 39 lines] | > | > Aurimas each user needs little bit more than 100 000 rows of data. This data is not filterable and it is atomic. I have put all the inserts into a transaction it saved 5 mins with no transaction - 36 mins with transactin 32 mins I still need more advices will it help to use smaller data types? Aurimas > One more thing you can try is partitioning your data. Cut the data down to > what the user will actually use. Does the user need 100,000 rows? Or does [quoted text clipped - 48 lines] > | > > | > Aurimas Batch the transactions? 5000 at a time? You could create the DB in the Emulator, which would make use of your desktop CPU power, and copy to your device. You could switch to eVC and use OLEDB, which would improve again your performance about as much as Parameterized queries did the first time. Use one of SQLCE connectivity scenarios (RDA, Repl) to move the data faster. -------------------- | From: "Aurimas Norkevicius" <aurimas_norkevicius@studentas.lt> | References: <uGYF3DerDHA.2772@TK2MSFTNGP12.phx.gbl> <#e$shXorDHA.4056@TK2MSFTNGP11.phx.gbl> <t3#izesrDHA.2996@cpmsftngxa07.phx.gbl> | Subject: Re: very slow inserts | Date: Thu, 20 Nov 2003 15:06:23 +0200 [quoted text clipped - 7 lines] | NNTP-Posting-Host: adsl-212-59-23-112.takas.lt 212.59.23.112 | Path: cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08 .phx.gbl!tk2msftngp13.phx.gbl | Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.ce:10191 | X-Tomcat-NG: microsoft.public.sqlserver.ce [quoted text clipped - 68 lines] | > | > | > | > Aurimas Do you have indexes on the table? I haven't tried it on SQLCE but in the old times, when we were doing mass inserts to a table in a RDBMS, we used to drop all indexes of the table, do the inserts and re-create the indexes. This was much faster than inserting thousands of rows into an indexed table. Yes i have indexes on the table and i use to drop indexes and to recreate them after insers > Do you have indexes on the table? > I haven't tried it on SQLCE but in the old times, when we were doing > mass inserts to a table in a RDBMS, we used to drop all indexes of the > table, do the inserts and re-create the indexes. This was much faster > than inserting thousands of rows into an indexed table. Do you have indexes on the table? I haven't tried it on SQLCE but in the old times, when we were doing mass inserts to a table in a RDBMS, we used to drop all indexes of the table, do the inserts and re-create the indexes. This was much faster than inserting thousands of rows into an indexed table. If you are using supporting development tool you may try filling dataset with data to insert and then updating dataset (database_name.UpdateDataSet(..) to perform kind of bulk insert - this made enormous difference in performance in my case Rgds >each user needs little bit more than 100 000 rows of data. This data is not >filterable and it is atomic. [quoted text clipped - 15 lines] >> | > >> | > Aurimas Do you have any code sample of how to set the parameters for such insert? I have a similar scenario but I dont have the books mentioned at hand. Im having an error and somewhere saw that "named parameters arent supported in SQL CE". Im really in a hurry Thank you From http://www.developmentnow.com/g/97_2003_11_0_0_383687/very-slow-inserts.ht Posted via DevelopmentNow.com Group http://www.developmentnow.com I don't see the rest of this thread--sorry to come in late... If you're trying to find code examples for SQLCe, you might check out my EBook. If it's an INSERT Command, it's setup like any other SqlCeCommand Parameters collection. What errors are you getting? Signature____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- > Do you have any code sample of how to set the parameters for such insert? > I have a similar scenario but I dont have the books mentioned at hand. [quoted text clipped - 11 lines] > Posted via DevelopmentNow.com Groups > http://www.developmentnow.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.