Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be.
We have a stored procedure that is taking 26 seconds to return information that is grabbed from 5 tables. Our server times out after 25 seconds. How can you solve this problem?
This is not verbatim but it is close to the question that was asked. I had no clue, and I don't even think we went into that kind of detail in our db class. All I could think of telling them is to make sure that the tables are indexed and I don't even know if that is right.
Here's an example of how this could look on creation:
CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20)) AS BEGIN SELECT * FROM orders o JOIN state s ON s.stateID = o.stateID JOIN customers c ON c.customerID = o.customerID JOIN items i ON i.itemID = o.itemID JOIN parts p ON p.partListID = o.partListID WHERE c.customerID = @CustID END
Once again this would be taking slightly more time than the timeout time allows.
- What are my options for speeding up this stored procedure syntactically?
- What are the different types of metadata that might help solve the problem?
- What practices should be avoided that are seen in the procedure above?
- (As a non-required aside question) Would it be acceptable to tell an interviewer that I would ask the real-life question on Stack Overflow?