Sql Database Pbm

[pillai]

Hi every 1

we have an sql server 2000 it installed before 2 years on windows 2000 advanced server.Now it's database size is 4 gb.My pbm is that when i try open last month or last year record from client machine it takes 15 to 20 minutes to opening the records.during that time the client machine is busy fro 15 to 20 min.We can't do nothing till the operatin is completed.Also entries from client machine also working slow.After I shrinked and reindexing the database it working normal for 2 or 3 days.But after that the same pbm occured.Any way to permanently solve the pbm.When i want to see last years records it will show withing a minutes.Plz help me.

Tahnkx.

 

[DelJo63]

Hopefully, you know relational theory and have 'normalized' the data.
When a given table gets too big, a typical solution is to export portions into
new tables with a 'fixed value' column, eg: year. If the size of the year-table(s)
are still too large, the next solution is export into second database.

DBMS startups can be costly -- the indices get read and memory structure are build.
Primary index will have only one entry per row of the table (by definition).
Your secondary indices can have many entries per table row.

Consider DROPing all secondary indices (they can be rebuilt later) and restart the system.
If this has a major improvement, then you know that you should consider
which indices you actually need and rebuild each and restart to see the
impact. When it become 'too costly', the last index is your problem --
consider why you have it and how you use it.