Sql 2000 questions

[spyrosc]

Dear Experts,

We have the following scenario:

We will create a system that will perform many functions.

Database SQL 2000
Client Visual Foxpro

Function A:
Import and export data from-to text files. Data import will consist of 20,000,000 records

per week.(Every week is treated as one period)
Each record will pass from several verification steps as well as data transformation steps.

This will be a very heavy duty job for the system.
In the transformation process a period (week number) will be stored for every record

processed.
Afterwards data will be appended/replaced into a table that will be used in function B.

Function B:
This function performs data viewing and editing taken from Function A.
we want to keep in the system at least 15 periods.(300,000,000 records)
Data for each period will be taken from Function A.

Function C:
Reporting of data.
I) Selection of data based on 2 periods (20,000,000 records), based on 15 periods

(300,000,000 records). One Table with 300,000,000 records
II) Selection of data based on 2 periods (20,000,000 records), based on 15 periods

(300,000,000 records). Data is splitted into 15 tables(one for each period), therefore if we

select more than one period, various tables consisting of 20 million records each, must be

joined. Worst case is to join all 15 tables. (300 million records)

.....

Function ...

.....


Questions:
----------

1) When do we split a Database in 2 or more? Is it good to split the database in our case,

or can we assing some priorities for job processing. (low priorety for import, so that data

viewing/editing/reporting performs very good)

2) What is the best strategy to cleanup database? (Remove every time 20 million records)

3) What benefits we will get from using clustered index for huge tables? (one will have 300

million records)


Waiting for your helpful reply.

Thanks in advance,

Spyros Christodoulou

 

[sguslan]

Well, it is hard to say. We do not know what sort data viewing and how involved it is: like are there any queries that do a table scan, or are all queries optimised to use only indexed columns or primary key? Is the server attending to only these functions you listed and not shared by another application ?

I am not sure splitting data into many tables would help. If you have a server with hefty resources like being dedicated with many CPUs , lots of memory and a fast RAID disk subsystem, then the server may take the heat nicely. I would put as much hardware as i can afford and test it out !
Another way would be to create many cheap servers [many machines] and use SQL2000 Distributed Partitioned Views which splits a table amongst as many servers as you want. Though, you have to understand how to plan for such beast.
Read on :

http://msdn.microsoft.com/library/en-us/optimsql/cm_fedserv_4uib.asp

As for maintenance and data loading/transformation it is only sensible to perform such chores in low load periods as during night time or weekends.
By the way if data is only loaded by admins and there is no application to change data, then may be you should consider running SQL in Non-Logged Simple mode where the SQL does not keep track of changes in logs. This helps in making data loading/deletion faster, needless to say you have to do full backup.