May 22, 2003 at 3:17 am
Hi All,
I am worlking on application which generates 400,000 records per run and i use physical tables to store the temporary tables to hold the data which is used in manaipulation. th eproblem is after some runs the system gets slow down. I have some indexes on the temp tables as i join thme lots of times and i feel that those indexes are causing the problems.
I need suggestions and guidline for handling such large volume.
Regards,
Affan
May 22, 2003 at 6:17 am
400,000 records is not a big number of records. I have runs with populate tables with with anything upto 50 million records.
There could be lots of reasons why some runs slow down. Indexes are useful at retrieving rows from tables but add an overhead when populating tables becuase SQL Server has to maintain the index as well.
In my app, I do not create the indexes until I have finished populating the tables and I find that this improves performance.
As far as you app goes I think you will need to do some investigation. For example:
- do specific types of runs always run slowly
- do specific steps always run slowly
I output start times for each major step to a log table so I can get a handle on which steps take a long time. What I then would do is to break down those steps and see what is actually going on. You will have to use Query Analyser and display the execution plan to see what is going on. When I have identified a problem step, I try several different ways of achieving the same results e.g. remove the index/change the index, split it up into smaller SQL steps/combine it into fewer steps.
HTH
Jeremy
May 22, 2003 at 7:33 am
Thanks jeremy,
actually one of my table takes lot of time as it has 2 big indexes adn i think i shoudl remove those and ltems c what happens, but the point is the user which runs this app does not have rights to create or drop indexes. similarly its a scenario driven application e.developed in ASP and SQL e.g. user can select different products and customers and 400,000 will be generated saved with that scenario and similalry he/she can select different customer product combinations and system will generate rows based onthe selection and user can run as many scenario as he can.
Regards,
AFfan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply