SQL Performance Tuning Interview Questions


20.What does a hint do? 
Well, it tells the query optimizer to perform a certain action, like if you want to tell the optimizer to use a certain method to join tables, or if you want to tell the optimizer to use a certain index.
  • Understand your optimizer to help you tune SQL queries
  • Knowing how the query optimizer for your particular RDBMS works can be a big help. This is because every optimizer does things differently. Let’s go through some things that you should keep in mind when dealing with query optimizers:
  • Not enough database statistics
  • Suppose there are not enough statistics about the database. Sincecost based optimizersrely on those statistics to perform their analysis, some optimizers may have to use a rule based optimizer instead in that case. And other databases may decide not to use an index at all and just do a full table scan instead.
21.Are order of predicates taken into account?
You should know whether or not your optimizer takes the order of the predicates in a WHERE clause into account, and whether that order has any effect on the order in which the predicates are actually evaluated.

22.What does that mean in plain English?
  • Well, a predicate is the comparison portion of the WHERE clause. So, for example, if we have some SQL that says
  • “WHERE website_name = “ProgrammerInterview.com”, then in that particular SQL query there is just one predicate comparing the website_name column to the text “ProgrammerInterview.com”.
  • But, if we have some SQL that says “WHERE website_name = “ProgrammerInterview.com AND website_subject=”technical”", then we have two different predicates – one that checks for the website name and another that checks for the website category.
  • Now that we’ve cleared up what we mean by predicates, let’s get back to the original topic. So, we said that you should know if your optimizer takes theorder of the predicates in a WHERE clause into account, and if that order affects the order in which the predicates are evaluated.
23.Why should the order of the predicates matter?
  • Well, if you optimizer does take the order into account, thenyou would want the predicate that eliminates the higher number of rows to be evaluated first by the optimizer. So, for example, let’s say that we have a table called Websites which has columns for the website_active and the website_subject. The website_active column is just a “yes” or a “no” entry, and let’s assume that most of the rows (something like 90%) in the table have a “yes” value for website_active.
  • But, let’s also say that there are three possible subjects – like “technical”, “self help”, “cooking”, etc. And, the subjects are evenly distributed amongst the rows – so /3rd of the rows are technical, /3rd are self help, etc.
  • Now, let’s say we want to run a query with a where clause like this “WHERE website_subject = ‘cooking’ AND website_active = ‘NO’. Which predicate of the WHERE clause should be executed first – the website_active = ‘NO’ or the website_subject = ‘cooking’? Well, think about that on your own for a second.
  • Wouldn’t it make more sense to run the predicate which eliminates more rows first? That way, the second predicate has less rows to process.
  • With that in mind, let’s ask ourself which predicate will eliminate more rows? Wouldn’t it be the check to see “WHERE website_active = ‘NO’? Because, that check will eliminate 90% of the rows in the table. But the check for “website_subject = ‘cooking’” would only eliminate 67% of the rows, so clearly 90% is better which means that the predicate that checks website_active = ‘NO’ should be run first.
24.Are order of table names being taken into account?
  • Just as theorder of the predicates being used in the WHERE clause can have a big effect on the efficiency of the query, so can the order of the table names in the JOIN or FROM clause. This is especially true with rule based optimizers. Of course, the best thing for the RDBMS to do is to choose the most selective table first.
  • This way the most number of rows will be removed from the result set, which means that less rows will have to be processed and the query can run more efficiently. You should check to see what your particular optimizer does to see if you need to tune your SQL queries accordingly.
25.Are queries being rewritten?
  • You should also check to see if your optimizer rewrites queries into more efficient formats. One example of this is when optimizers will rewrite subqueries into their equivalent joins, and that will make the processing that must follow much simpler.
  • For some DBMS’s, there are certain options that have to be enabled so that the optimizer can actually rewrite queries.
  • Tune SQL queries by ensuring that your indexes perform well One very important thing that can help your SQL queries run better is making sure that your database indexes also perform well.
  • Summary of tips on how to to tune your SQL queries.
  • In general, you should know the options available to you that may help you in tuning your SQL queries. Of course, not everything we presented above will help make your SQL perform better – because everyone’s particular situation is different.
  • But, knowing your options is critical – as the saying goes “To the man with a hammer, every problem looks like a nail.” Make sure that you have more than just a hammer in your toolbox!
26.How do you tune database performance?
  • Every good Database Administrator (DBA) knows that tuning a database is a constant job. This is because there is always something in the database that can be changed to make it run more efficiently.
  • But, what are some of the more common things that you should keep an eye out for if you are tuning an RDBMS? Well, let’s go through those things, but without getting too specific for any particular RDBMS (like MySQL, Oracle, etc.).
27.Tune database performance by designing tables efficiently
How you design your tables is very important in terms of database performance. In general, non-RDBMS specific terms, here are some guidelines you should follow when designing tables:

28.How do you tune database performance?
Every good Database Administrator (DBA) knows that tuning a database is a constant job. This is because there is always something in the database that can be changed to make it run more efficiently. But, what are some of the more common things that you should keep an eye out for if you are tuning an RDBMS? Well, let’s go through those things, but without getting too specific for any particular RDBMS (like MySQL, Oracle, etc.).

29.Tune database performance by designing tables efficiently
How you design your tables is very important in terms of database performance. In general, non-RDBMS specific terms, here are some guidelines you should follow when designing tables:

30.How do database indexes work? And, how do indexes help? Provide a tutorial on database indexes.
Let’s start out our tutorial and explanation of why you would need a database index by going through a very simple example. Suppose that we have a database table called Employee with three columns – Employee_Name, Employee_Age, and Employee_Address. Assume that the Employee table has thousands of rows.
Now, let’s say that we want to run a query to find all the details of any employees who are named ‘Jesus’? So, we decide to run a simple query like this:
SELECT * FROM Employee
WHERE Employee_Name = 'Jesus'

31.What would happen without an index on the table?
  • Once we run that query, what exactly goes on behind the scenes to find employees who are named Jesus? Well, the database software would literally have to look at every single row in the Employee table to see if the Employee_Name for that row is ‘Jesus’.
  • And, because we want every row with the name ‘Jesus’ inside it, we can not just stop looking once we find just one row with the name ‘Jesus’, because there could be other rows with the name Jesus. ‘
  • So, every row up until the last row must be searched – which means thousands of rows in this scenario will have to be examined by the database to find the rows with the name ‘Jesus’. This is what is called afull table scan.
32.How a database index can help performance
You might be thinking that doing a full table scan sounds inefficient for something so simple – shouldn’t software be smarter? It’s almost like looking through the entire table with the human eye – very slow and not at all sleek. But, as you probably guessed by the title of this article, this is where indexes can help a great deal. The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined.

33.What is an index?
Well, an index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of atable. So, the key points to remember are that an index consists of column values from one table, and that those values are stored in a data structure. The index is a data structure – remember that.Subscribe to our newsletter for more free interview questions.

34.What kind of data structure is an index?
  • B- trees are the most commonly used data structures for indexes. The reason B- trees are the most popular data structure for indexes is due to the fact that they are time efficient – because look-ups, deletions, and insertions can all be done in logarithmic time.
  • And, another major reason B- trees are more commonly used is because the data that is stored inside the B- tree can besorted. The RDBMS typically determines which data structure is actually used for an index.
  • But, in some scenarios with certain RDBMS’s, you can actually specify which data structure you want your database to use when you create the index itself.
35.How does a hash table index work?
  • Hash tables are another data structure that you may see being used as indexes – these indexes are commonly referred to as hash indexes. The reason hash indexes are used is because hash tables are extremely efficient when it comes to just looking up values.
  • So, queries that compare for equality to a string can retrieve values very fast if they use a hash index. For instance, the query we discussed earlier (SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’) could benefit from a hash index created on the Employee_Name column.
  • The way a hash index would work is that the column value will be the key into the hash table and the actual value mapped to that key would just be a pointer to the row data in the table.
  • Since a hash table is basically an associative array, a typical entry would look something like “Jesus => 0×28939″, where 0×28939 is a reference to the table row where Jesus is stored in memory. Looking up a value like “Jesus” in a hash table index and getting back a reference to the row in memory is obviously a lot faster than scanning the table to find all the rows with a value of “Jesus” in the Employee_Name column.
FabGreen Technologies-Madivala
FabGreenTechnologies.com

No comments:

Post a Comment

Subscribe to: Posts (Atom)