June 30, 2009 at 9:48 am
Hi All
I have been in a confusion here , please help me out,
We are having a database for reporting, it consists of tables separated by month, it got nearly 36 tables, all the tables are indexed, and if i need to retrieve data from all the tables with the indexed columns then it takes me nearly a minute for the first time, then as its been cached it will take me less than 30 seconds.
The other structure is that have a huge table with all the data on it and then have the same index.
What difference will this 2 make on a performance point of view
Please share your suggestions
Cheers
June 30, 2009 at 10:30 am
What is your definition of "huge"?
The probability of survival is inversely proportional to the angle of arrival.
June 30, 2009 at 10:36 am
say 50 million
June 30, 2009 at 10:53 am
How wide are the rows? (I suppose I should have specifically asked that as well).
I would try to keep the table as one (logical) table if at all possible. You have several alternatives... vertical and/or horizontal partitioning of the table. I always try to explore the possibility of breaking the table up vertically because you can end up with more optimized indexing. That really only works well if you have lots of different queries that look at different columns of the table.
Horizontal partitioning will let you spread your table across multiple file groups (disk volumes). Don;t forget about creating indexes on different file groups. If none of this will work for you then you end up using unions which can be more costly.
The probability of survival is inversely proportional to the angle of arrival.
July 1, 2009 at 2:14 am
Thanks,i am only using 2 Quires to retrieve all my reports, each table has 30 columns, and db sits on a file group,
I am not using Union in my t-sql to get data, index is on a varchar column, if i can change this column to a big int variable will this help,
The current SQL takes me 1.5 minutes to get all data out from the database, what kind of partitioning can i consider for this. can i make it faster than this.
if in case i am making a partition using file groups then, what is the best RAID type to store the database in 🙂
July 2, 2009 at 4:18 am
CrazyMan (7/1/2009)
Thanks,i am only using 2 Quires to retrieve all my reports, each table has 30 columns, and db sits on a file group,I am not using Union in my t-sql to get data, index is on a varchar column, if i can change this column to a big int variable will this help,
The current SQL takes me 1.5 minutes to get all data out from the database, what kind of partitioning can i consider for this. can i make it faster than this.
if in case i am making a partition using file groups then, what is the best RAID type to store the database in 🙂
Indexing a varchar is more expensive than a bigint, so that will help. It also matters how different the data is in that column.
Partitioning depends on so many factors, but you can partition on a range value, e.g. date.
RAID 5 is slow for writes. You'll want a combination of striping and mirroring if you need speed.
Insert various other questions here: What are the reports doing? Why do you need ALL of the data at once? etc., etc.
July 2, 2009 at 5:47 am
I will try that, when you say Stripped and mirrored , i thought this will have a impact on performance, may be i am wrong,
I am having a view partition now, can i gain significant performance by having a table partition, this is only been used by reports and we have dates by which i can partition.
any suggestions ??
Thanks for your reply
July 2, 2009 at 7:03 am
Stripe for speed, mirror for redundancy.
Partitioning is a whole nother kettle of fish. You can benefit from table partitioning as long as it's done sensibly.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply