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.
| Author |
Topic |
|
fcoreyesv
Starting Member
18 Posts |
 Posted - 2006-12-08 : 13:14:50
|
| Hello I am testing an application in VB which upgrades an Access database into a SQL MSDE or SQL server. The application passed a test with 500 Orders. But now I am converting a database with 4000 orders and each order may have several records and each of this record has several records related to it. Somewhere at the Order 1000 or so the server stops responding and timesout. At this point I have clear that the server needs some time either to resize the database or to reindex the tables or simply to process all the Insert calls the application is sending. I am telling my boss that this computer only had 512 MB in RAM and the resources run low at some point. He says that it is unacceptable, arguing that we can not ask our clients to upgrade a computer only because of this process. My question is if it is possible to handle the timeouts and a way to request a status to MSDE in order to stop sending inserts until it is available or a way to commit the inserts and wait for a response to do the process in a safe manner.Thank you in advance for your comments.Jose Reyes |
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-12-08 : 14:45:42
|
| I would put a delay in the conversion process for every 500 orders wating 1 sec. I have had similar problems in the past and that worked for me.-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-08 : 15:17:59
|
| Why not just have an empty SQL Database ready, and copied to the client's site?Then do a normal INSERT from ACCESS to SQL according to FK's.Peter LarssonHelsingborg, Sweden |
|
|
|
fcoreyesv
Starting Member
18 Posts |
Posted - 2006-12-08 : 18:01:01
|
| Thanks PSamsig I will give it a try!Peso... The 4000 orders are copied into MSDE locally from an Access database to an SQL Database. The orders have dependent records in another table. The dependency is the ID of the Order so I need to know the NEW ID of the record in the SQL table to assign it to the related records. Also the related records (order Items) have other related records where also I need to know the new ID of each orderItem to relate the New records. These last new records are 1 million records which need to know the parent ID of its parent.That is why I had to make this application to read each record at a time.At the end, those million records are the ones that are creating the server to stop responding at a given point. Any more comments will be appreciatedJose Reyes |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-09 : 02:18:20
|
| How big is the database?Are you committing each batch, or are they all in one transaction?If the database Recovery model SIMPLE or FULL?What is the File Expansion set to? (Default is 10%)FULL recovery model will log all the transactions (until you back up the TLog). That may require the Tlog files to be extended (several times) to complete your job. Changing to SIMPLE (maybe just for the duration of the import) might solve that - but you will need to COMMIT regularly 'coz if you do it all in one batch it will still log that transaction!)If your files are getting large the 10% may be causing expansion of the files that is taking too long - changing that to a fixed amount, rather than percentage, might help.Kristen |
|
|
|
fcoreyesv
Starting Member
18 Posts |
Posted - 2006-12-12 : 09:47:07
|
| Hello KristenThe access dB is 190 MBI am not using transactions. The application is using ADO to send each transaction at a time. The Recovery model is Simple (Default). The expansion was at 10%. I noticed that the Option "Auto Shrink" was ON. I have changed the initial size to 300 MB to make another test.Thank you in advance for your comments.Jose Reyes |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-12 : 10:09:36
|
| "The access dB is 190 MB"OK, so its in the "small" bracket, so we can rule out Timeout caused by Big Database issues ..."The Recovery model is Simple (Default)"So the transaction log should not be growing (or at least not by much)."The expansion was at 10%."I wouldn't worry too much about changing it to fixed size until the database is > 300MB or even a bit more than that."I noticed that the Option "Auto Shrink" was ON"Well that will be bad news if it cuts in whilst you are using the database. Its not a good thing to have as a default anyway, so worth turning off.Might be worth also double checking that you have AUTO_CLOSE turned OFF (the default for MSDE databases is ON)"The application is using ADO to send each transaction at a time"That might be what is causing your timeout, if I've understood you correctly.So you are looping round in VB, sending one transaction to SQL Server per loop iteration?That's going to be pretty slow for the application (mind you, it shouldn't cause ADO to timeout, only VB)Kristen |
|
|
|
fcoreyesv
Starting Member
18 Posts |
Posted - 2006-12-12 : 10:36:52
|
| The SQL database grows to 400+ MB during the process. I forgot to mention that.The auto close option was OFF. Yes I am looping and sending One Insert per loop iteration. Since I am not working with transactions each insert is a transaction itself. I will explain a little more in depth the structure of the Orders so you can tell me if creating long transations will help.The order has one record with the general information. There are 1 o more detail records related to that order. Each one has a group of detail_option _records from 10 to 50.So copying an Order is actually creating a record on the Orders win insert and get the New ID to create the Detail records referencing that new OrderID. When that Detail record is inserted I need the New detailID also to create the option records referencing that DetailID. That is the reason it has to be done with loops. I guess I can start a transaction on each detail record and end the transaction after the loop of the 10 to 50 options in that loop. |
|
|
|
|
|
|
|
|
Server Timeout