Table size growing .what are performance techniques? - Interview questions

Question

0.00/5 (No votes)

See more:

, +

Hi Guys,

I am facing few questions in interview,every time i am telling something but interviewer expecting some thing more.So I want to get it know from Experts some possible answers

1.There is a table which is keep on growing (let's say table has 10 millions records) so that performance of some simple join select query degrading.Earlier it used to take 10 mins to give the output.Now it is taking 45 mins.

Question are
How you improve the performance?What are all the possibilities?
Do you need index rebuild or reorganize?
Does partitioning tables will help out in this case?
Suppose I have where clause ,where column doesn't have any index.How will you achieve performance?(he's not expecting creating non clustered index on this column)

Almost in all my interviews I faced these questions.I know code project is the right place to get more info on these questions.So I am posting this question and it will help me and to others.

Thanks In Advance

Posted 22-Apr-15 0:05am

pratap420

Add a Solution

Comments

John C Rayan 22-Apr-15 7:03am

Basically the rebuild and reorganise can help but not much when you have very large data.

If you don't want to create index on where clause column then you have to modify the clustered index to 'covering index'.

Partitioning is one option to split the rows by year and may work well.

If you have historical data then move them to another table or database.

You can pick a magic date and in the application you can form the query based on the date. For example any data required before the magic date (for example 01/01/2012) then you have to use those tables or databases where you have historical data otherwise the current table or db.

You can use different views for different years and the view will retrieve the data from different table/db for different date range.

pratap420 22-Apr-15 9:04am

Thanks John for your response

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen Kumar Upadhyay · Answer 1 · 2015-04-22T01:55:00

First of all I am not an expert but yes I can try to answer your question.

Very first thing if your table has 10 millions of record(means 1 Cr rows), any select query should not take 10 mins, As per my thinking a query which is taking more than 5 sec needs to optimize, irrespective of what no of rows that table has.

First of all, I do not understand that why these people take interviews and what they expect to answer. They simply ask question which can not be answered in one or two statements and these are completely scenario based question, where your scenario and their scenario can be totally different. Even I have conducted interviews and I have hired very nice people, what I see is just the logic building skill and their approach that's it. Process they can learn very easily.

Now coming to the question that you have been asked in your interviews. Below answers can be best fit with Mysql.

How you improve the performance?What are all the possibilities?
Yes, definitely we can improve the performance, provided that what is the field given to play. We can add indexes, we can create summary tables.

Do you need index rebuild or reorganize?
Yes, of course indexing needs to reorganize.

Does partitioning tables will help out in this case?
Can be or can be not. Again depend on scenario.

Suppose I have where clause ,where column doesn't have any index.How will you achieve performance?(he's not expecting creating non clustered index on this column)
I don't think so, it is a right question