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 |
|
tommyboy1910
Starting Member
2 Posts |
 Posted - 2007-08-20 : 12:12:44
|
| Dear frenz,One of the department in my company having data searching and responding in slow time problem. I will eloborate the current system.Problem description:====================There is 4 lanes. Note : Our focus here is only to one lane!each lane will have 4 machines.Each machines will have local database to its corresponding sub machines. All the information from the sub machine's local database server will then finally pass all the data to the main database server after 2 weeks.Within two weeks, one table may grow upto 600 000 rows of records and search record time from all the sub database server is varied depend upon phase by phase.Phase by phase:===============Assume 4 machines label, A,B,C and D respectively. The user from location A will check one item and scan the item's barcode to get info from the db and will update the 'result' field from table in stage A whether past / fail and also update the current process whether its process status = started/halted/interrupted/succefully completed/canceled.Assume each table [all the tables are identitical version]in 4 sub database server will now having 600 000 rows respectively.When the User A scan the barcode from that particular Item belongs to group 1.1, it will search from database A and display info. the respon time is quite ok since not much filtering in searching method. When another items belongs to group 1.1 as well, is scanned by user B then it will search the info from database A, and displaythe item's [group 1.1] status and process status from stage A.The respond time here will be slower since it will check from db A. Same scenario will apply when any item from the similar group items' being scanned in db C and db D and check if that particular item is passed in previous stage before proceed to its current phase[A,B,C & D].Currently, the user will have to wait for few seconds to get the info when the db is growing each time and due to this they have to clear the table and send to main database server once in 2 weeks.We hope can keep for atleast 1 month but it is delaying few seconds per scan and the respond times are varied for phase A, B ,C and D.D is slowest since it needs to check in A,B and C before it accept in its own phase which is D.I am trying to propose to use SQL Server 2005 and try re-design the current structure in each lanes. I am now suppose to state in my paper proposal, that one each server for roughly 600k rows contents, if apply search and use stored procedure to habndle it and return the output then what is the average time for all the 4 machines.if can, better specify by each stage one how many miliseconds/seconds will be utilized. Please someone whom have face similar case or come accross to new method , help me suggest new way of solving this problem.Also plz give me an accurate time if can for each phases as I need to specify in my proposal documents. Can someone give a accurate time for each stage also a better solution for re-designing process and estimate a new respon time.realy appreciated , dear my frens.I trust you are genius and chosen from around the world.;) Im sure someone would have done something miracle...lolIm in deep trouble as I have to submit the proposal in 2 days times.Thanks a lot!!! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 12:21:08
|
Is this the only question on the exam you are having trouble with?Or, if I am mistaking, are you a consultant who find this assignment way over your head?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
|
|
New challenge to improve search method!