Unanswered: New challenge to improve searching method!
One of the department in my company having data searching and responding in slow time problem. I will eloborate the current system.
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...lol
Im in deep trouble as I have to submit the proposal in 2 days times.
Thanks a lot!!!
Thanks & Regards!
Live your life to the fullest Whilst u can...=_="
Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
Where there's a will, There's a way....;^D
No one can tell you what response times to expect. That is a factor of your hardware, the cardinality of your data, the efficiency of your code, and the activity on the database.
What database is currently being used? If you are considering moving to SQL Server from some less efficient server or database design, then that alone might give you a performance boost.
Why are you maintaining four separate databases? If, as you indicate, some of the delay is caused by having to check databases A, B, and C prior to inserting into database D, then consolidating your data may improve performance.
What kind of response times are you getting, and what maximum response time can you tolerate? SQL Server can handle 600K rows with ease.
If it's not practically useful, then it's practically useless.