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 |
|
jruez
Starting Member
5 Posts |
 Posted - 2007-11-26 : 15:44:01
|
| If this is not the correct forum for this topic, I apologize. Please let me know which I should post it in.I have an application that is currently windows based, but is being ported to the web. I am looking for suggestions or best practices when dealing with a large number of records (5000+) and performing some processes on those records. Some examples:-- Populating a database with 5000+ records using information from 3 different tables. Table one could have a barcode number, table 2 a full name, table 3 a location name. These 3 values would be copied over to the new table, unique for each new record.-- updating each record with its current state (open, closed, deleted, whatever), then adding a new record to an audit log, depending on what the state is of each record. Now there are 10,000 records being processed.The basics are that there are actions that will process large amounts of records at one time. In the client application, we did not have to worry about a web timeout, so the process could go for 20 minutes if necessary, and that would be fine.Are there features in sql 2005 that handle large processes like this and can be used when developing for the web? Would I need an app running on the server database to run the process that can interact with the web? The app is being developed in asp.net 2.0, using c# and will run on sql 2005. I have found useful tips on this site for many of my questions, but not this one. Please let me know if more explanation is needed and thanks in advance. |
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2007-11-26 : 16:33:30
|
| I don't think I really understand. Are you "processing" the data in the web application, itself?? If so, why?? Why not have the web application just send the needed SQL commands to the Database, which could then process magnitudes faster?? Even a so-so designed db should be able to crunch through 5000 records in a few seconds.---------------------------------------------------------SSRS Kills Kittens. |
 |
|
|
jruez
Starting Member
5 Posts |
Posted - 2007-11-26 : 16:53:30
|
| Thanks for the reply Will. I guess I am trying to figure out the best way to do this. I think processing it in the database would be the way to do it. I am worried about timing out on the web side if the processing takes too long. 5000 might be fine, but what about 20,000. And what if multiple users are trying to do the same thing at the same time with different records? There was talk about creating a external app that would load on the server and interact with the web and database to handle these large processes. Is this necessary or can it all be done in the database. If I sound confused, I am a little. So I apologize if this is vague. I am just looking for some methods to handle large amounts of data being processed on a web application. Thanks again |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-11-26 : 19:40:39
|
| 5000 is considered to be a very small database. You could have millions of rows and process in seconds. In almost all scenerios, Any type of record processing from a webserver should be done in a database, minus maybe field validations. What you are looking to do is very simple, it's just a differant mind-frame then what you are used to.Pre-Req1. Must have a db (Prefferably MS Sql 2005, but you can use MSAccess or something else).2. Create your tables, and import your data (Plan this part carefully)3. Must code your application to communicate with the db (You can communicate with a DB in many differant ways, but it depends on the database type).4. Secure the connection between your Web server and the DB.5. Your doneUnfortuantly you did not really give much detail on what exactly you need, so I don't know how else to help you out. If you need specific answers please ask a specific question. |
|
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2007-11-26 : 22:38:43
|
| To make a -huge- simplification, the web application should really only focus on doing a few things: * displaying data to the user (view)* allowing the user to input new data (insert)* allowing user to update data (update)* let user delete data (delete)All the web app should do is send these commands to the database, as a query or stored procedure. If you can think of a process that requires, say, summing or averaging 5000 rows, the web app shouldn't handle that. It should send the request to the db, which can process thousands of times faster, and then get back just a handful of numbers to neatly summarize on a web form. Without knowing what the old app did, I'm going to take a guess: The user collected lots of data either from a db or file, had a collection xxx or so objects, and performed business logic by iterating through the stuff in a loop. This sort of approach is very common for people who are familiar w/ compiled code like say, java or c# but scared of databases, and it could very well explain why the original process took 20+ minutes. A well tuned db wouldn't flinch at 100 times that amount. The best approach, without really understanding your exact requirements, is much like vinnie said. Work out a good, relational, normalized data model that will contain the data you need. This is probably the hardest part. After that, write SP's to access the data, and test those procs w/ lots of rows (estimate about 100 times your expected load of 5000) . Only then should you worry about the actual web app, itself.---------------------------------------------------------SSRS Kills Kittens. |
|
|
|
jruez
Starting Member
5 Posts |
Posted - 2007-11-26 : 23:34:14
|
| Thanks to both of you for these answers. I do appreciate you taking the time to post them. They are very helpful. I will see what can be done with your suggestions. |
|
|
|
|
|
|
|
|
Thousands of records processed in web application