• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Table being Big

My application  collects data from 20 different application. The extracts are in the form of a txt file and is loaded in to temp database ( working database). There are no referential integrity here and data is not indexed. It is validated and cleaned and non validated data are held in suspense. the validated data is posted to a main table in the principle database ( disktrans table) and indexed. the batch is run against this table and data is posted into other tables. Mostly updates and inserts. Then the rows processed are flagged as processed. But the data continues to reside in this table. when the next batch occurs, rows are appended to this table with flag as unprocessed and all unprocessed data are taken for processing. The concern is that this table has 40 million rows. Another table -- disttransdt has 96 million rows. These two are the main table against which all queries are run. Is there any other way to make this better? Any suggested solution.
0
jagishiv
Asked:
jagishiv
3 Solutions
 
ram2098Commented:
I guess you can split your working data to tables one is the working table and other is archive.

You can keep only the working data into the main table and the remaining data (the old data) you can move to an other table called "Archive". (I guess you may not be using the processed data any more, just as a backup).

This way, you can keep the working database size consistent and improved performance.
0
 
ram2098Commented:
Or even you can think of an other "archive" database too..where you archive the data on a regular basis.
0
 
jagishivAuthor Commented:
Database is archived once a year because people access the info through the year. it is a banking commission application.The data size, I am refering to, is in the main table not the working table.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Parag_GujarathiCommented:
one way of overcoming this problem is that u partition the big table into smaller tables. this will definitely speed up your access, but the downside is that u'll have to modify your application logic, so that it fetches data from the proper table. U may also consider creating partitioned view on the tables.

For further info on partitioning data in the table check the link below...

http://www.sqlteam.com/Item.ASP?ItemID=684

cheers
Parag.
0
 
arbertCommented:
Agree with Parag, this is a perfect application for partitioning....Your data still gets spread over multiple tables, but all the data is "online" and usable for people to report on...

96million rows isn't very many rows for sql server--have you looked at SQL Profiler and query plans to see if there are any improvements that can be made to the existing indexing/structure?
0
 
ram2098Commented:
Since you have an archival process in place...I guess this should be fine.

Atleast you know what is the maximum limit of your database (no. of rows in an year) and you can tune your queries accordingly. (Like tune your queries, add views etc).

Just an FYI, I have a database of 200 Million rows (Of course ..I keep only three months of data in that) in prod..but managing our queries to run faster (reasonable limit)..with proper indexes etc.

0
 
arbertCommented:
"Just an FYI, I have a database of 200 Million rows (Of course ..I keep only three months of data in that) in prod..but managing our queries to run faster (reasonable limit)..with proper indexes etc.
"

Yep, I totally agree--I have several large ware houses that don't utilize any partitioning at all.  In fact, partitioning can actaully create some VERY bad plans if it isn't implemented properly.  It really does do a great job from a maintenance perspective though....You'll find much better partitioning and VLDB support in Yukon...
0
 
ram2098Commented:
"You'll find much better partitioning and VLDB support in Yukon..."

Arbert: Do you know any article explaining these new features..I am actually looking for that..
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now