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 |
|
chadbryant5
Starting Member
32 Posts |
 Posted - 2006-11-27 : 14:21:36
|
| Hi,We have a feature in our asp.net web application that we've added for a customer. They wish to have an Access database of their records that they can generate and download at any time. What we've done is setup a blank Access db with all the tables. We have a SQL 2000 db that drives our web application. The user goes to a web form and enters some input such as date range, etc and that corresponds to a list of records that are within that date range etc. From that resultset we need to build the Access database by making a temp copy of it and then using the resultset from the SQL Server query to insert rows into the Access db tables and then we let the user download the newly built Access db.Right now this is taking forever and the users are complaining so I'm trying to figure out the fastest way to handle this and just looking for ideas.There could be as many as 20,000 rows for the main table and then for each row in the main table there could be 10 or so child rows in the 8 or so child tables. So imagine something like an Orders table and for each order (of about 20,000 potentially) you need to insert child rows into the child tables of the orders table. There are 8 child tables and for a particular order you might have 1 or 0 maybe rows inserted into a table and another could have as many as 10 rows inserted into that particular child table for that particular order.Right now we have an ADO.NEt based data access layer class that gets back a .NET DataSet with DataTables representing the results from the various tables. I am going through each row in the Orders main table and then for each row in there going through the child tables and inserting the rows from that table into the Access table. It seems like this could be optimized somehow so that things are done in more of a batch fashion, but I'm not sure how and I'm not that familiar with Access or OLE Db providers. We typically program against SQL Server and we use the SQL client ADO.NET capabilities rather than the generic OLE Db access methods.Any help with this would be greatly appreciated!!Chad |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 14:57:48
|
| Stop trying to migrate the data into Access for the client. Provide them with SQL Server data instead, which will be much faster to generate.We provide our customers with either a) A complete database backup or b) A bunch of BCP files representing the database. We generate these overnight so that they are available during the day (albeit up to 24 hours out-of-date)Kristen |
 |
|
|
chadbryant5
Starting Member
32 Posts |
Posted - 2006-11-27 : 15:01:27
|
| The client wants it in Access. We have suggested other solutions to them ourselves, but this is what they are adamant about getting so thanks for the response, but I still need to figure out the fastest way to get the Access db populated. Thanks. |
|
|
|
chadbryant5
Starting Member
32 Posts |
Posted - 2006-11-27 : 15:03:09
|
| Also, imagine the database has orders for 500 different customers. Customer X wants only their orders for a specific timeframe....the solution will query the SQL database and get the orders for customer X for the time period they specify and then build them an Access DB to download on the fly. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-27 : 15:32:56
|
| Probably the best way would be to only update Access with differential data.So any changed / new rows are updated/inserted; any rows which are "too old" are deleted.This should dramatically reduce the number of updates to the Access tables.But personally I would tell them to get lost, unless they are prepared to use SQL Server instead. Honestly. If they REALLY want they can have some in-house spotty-youth Summer-intern convert the SQL Server to Access on their machine, using their CPU cycles.Kristen |
|
|
|
|
|
|
|
|
Batch copy problem