Can some one suggest
-
Hi
I am having a problem in the database (SQL Server 2000) A table is growing quite rapidly and it has already reached few millions so giving timeouts on sps.This table is about to grow more.
I was thinking of implementing some thing inteligent dont know what...So that the code accessing this table does not change and I split the table into few tables and create something (don't know what) so that logically the code sees the same table but physically i have more than one table thus easy to manage
Can some one help me out on this
Vivek
-
Take a look at Partitioned VIEWS on BOL
* Noel -
And maybe CREATE TRIGGER ... INSTEAD OF INSERT ....
for inserting into the partitioned view while having new records going to correct uderlying table.
Once you understand the BITs, all the pieces come together
-
Before doing the tricky bits, have you profiled the statements that are timing out and run index tuning wizard against them?
Cheers,
- Mark -
I agree with mccork!
Before embarking on Partioned Views, how bout ascertaining the query plans of your stored procedures to see whether Indexes are used and perhaps create Indexes where appropriate!
Just because a table has a few million records it doesn't mean that you have to begin Horizontal Partioning!
-
profile, profile, profile and don't forget index-tuning-wizard.
There has to be a reason for this latency.
Maybe an other clustering index can help out. Choose it wizely, based upon your usage info.(profiler, ITW)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
When you have worked out what indexes you need if at all possible place them on a different disk to your datafiles, with your log files on yet another disk and your virtual memory file and system files on yet another disk.
Of course you need to have the right indexes and well tuned Stored procedures and SQL in place first!
Nigel Moore
====================== -
If you use a composite clustered index on all your lookup columns you will get "Index Seek" lookups. These are the fastest by far; it doesn't matter if you have millions of rows or 100s of millions.
That's the payoff, but the trade-off is the time taken to maintain the clustered index.
cl
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply