Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-01-15 : 22:14:48
|
| hi i need all u expert to suggest me...i have a huge base in oracle, sql very huge...i have to maintain a snapshot of both the database (selected data)for every month and generate MIS reports..... i dont know i am talking about datawarehouse....? so i dump data every month in sql 2000 server.... should i havediffrent database for every month or same database with diffrent tables...issues to consider...1. performances2. handling3. coding4. reterivaland any more i have Compaq server PIII 1.3g mhz with a lot of hard disk and 512 ram and SQL 2000 loaded.... any help or advice is welcomethanks |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-15 : 22:30:37
|
| khalik,What's your definition of huge?? 100Gig??Anyway...My $0.02Assumption: - Read Only dataUse a single DB with a table per reporting month...Create 2 filegroups...with as many files as you like in each..Place all your Data on 1 filegroup and all your indexes on the other.(A SCSI RAID will love that)If you are doing these loads outside work hours...Set the DB to Read/WriteInsert Data into a new table with NO indexes..Index the hell out it.....Set the DB to ReadOnly.....Get more RAM!DavidMTomorrow is the same day as Today was the day before. |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-15 : 23:12:30
|
| hi thanks a lot david...data is around 10 gb month.. and i should maintain aleast 3 month data.. so i will create 2 filegroups as u said... do u mean first i should create table and insert the data and and then index... cant i create the table and index and then insert the data.... does this make any diff....... thanks a lotkhalik |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-15 : 23:36:11
|
| If you create the Table and Index before you load the data, you will incur a performance penalty due to Index creation and organisation..With 30Gb that will hurt a lot....With any large data load it is best to index after the load...HTHDavidMTomorrow is the same day as Today was the day before. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-16 : 01:06:18
|
| thanks for the info... do we have any thing like rebuild index.say after the data load and some updations do we have to reindex it.... one more issue... i have to run the process(batch process) i was using cursors but i was to a extent i was able to eliminate cursors by using sql statment.... but still a major part runs on cursors i have gone throug several articals but none talk about what can replace the cursors..... some info on any other issue to improve the speed and performancethanks |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-16 : 01:26:27
|
| If after the data is loaded and you have performed whatever other data action, you create the index.. there will be no need to re-index..If you have too run sp_updatestats and dbcc dbreindex.. see BOL for more infoI am sure that there are fellow SQLTeam members who would love to have a stab at getting rid of the use for cursors... Send us your current SQL with "Create Table" and some "Insert" statements for testing...DavidMTomorrow is the same day as Today was the day before. |
|
|
|
|
|
|
Expert Advice