How to optimize SQL querry execution?

Question

I was asked this question in the interview. The company has very large size database like 2+ terabytes. They want to optimize the querry execution. I don't know much about the company's business senario. If you're on my shoes, how could you ask and response to this questions? Thx

Answer 1

Hi, hmm the answers you got so far are kinda obselete.

A SQL Query execution can be optimized in many ways. Every scope of optimizations depend on the current SCHEMA that you have currently. A person cannot simply tell you how to optimize your query withought looking at your schema structure and type of queries you wish to commit.

But the most important to SQL Query optimizations in my point of view are the following.
- Normalize your tables (very important, there is a book all about it. Google about normalizations on the net)
- Use Indexes wherever applicable (don't be too wild, it depends on your design. You have to realize which scenario best fits your question)
- Choose a proper design for your database. Sit down and discuss with people about your design. That is the most important step. Once you discuss your design either SQL Diagrams or Relational Diagrams, you could know where you would need Indexes and where to properly Normalize. That is a crucial step when you care about optimzation since It refers to the previous two I stated.
- Remember that Your queries should fit the context. DBMS gave the client (user) the power of functions and quickpoints. USe them with caution. For instance Remember whenever your usin the < or > operator, it will take much longer if you used the BETWEEN Clause simply because you don't have any finish point. There are many tricks on how to do the query. But if your experienced enough you would solve them with easy.

Some databases have a method of EXPLAIN query. What that does it explains which indexes it goes over and which parts lack and which parts are un necessary in your design. Remember as I stated before whenever your doing comparison within SELECT Queries, Do not place an aggregator in the WHERE Clause...try to avoid that, many clients tend to use it thou.

IF you place your structure of your tables and test data, and as well as your query your trying to optimize, then we could help. Remember the keen areas I discussed to you since that will make a huge difference. It doesn't matter if you have a million rows. But if you have a big databases (Gigabytes of info) They there is other measures to go look at.

We have to know your exact problem to help you exactly.!

Answer 2

the most important command you can use to help optimize SQL queries is EXPLAIN {SQL command} (replace {SQL command} with your query).

This will tell you what sort of indexing your query used, how many rows are being searched per table, and more.

A couple key tips to remember:

Always use only one field as a primary key, the entire primary key is copied to each index, so if you use more than one field the indexes will get pretty heavy.

Put the most limiting part of the where statement last that is the first one that will be run.(at least on MySQL, I'm not 100% certain of others).

If you are putting multiple columns in an index, make sure the first one is the field you query the most, second the second most, etc.

Answer 3

View the execution plan in Query Analyzer.

Look for Table and Clustered Index Scans
Index Scans vs Index Seeks
Bookmark Lookups
Join Methods
Sorts
Compute Scalar

Answer 4

An open ended question like this is not intended to get an exact answer from you.
During the interview process, a question like this is designed to get you talking. As you explain your approach, you will ask questions, and your questions and your approach will reveal your experience, qualifications and your proclivities in attacking a problem.
I like to give general answersa to general questions. It shows that I am not narrow minded, and that I keep my optoins open. I speak of a team effort and continued learning. I sppeak about being proactive - anticipating failure - mitigating risk etc.
I hope that in your interview, your answer was something like "The only way to develop an approach to optimization is to understand the database schema and the kind of volume the database is handling."
There are a million different approaches to optimizing a database, but you need to know your constraints, limitations, expectations and business requirements.
I hope you did well.

Answer 5

That's a very big question. There are books about SQL queries and how to best optimize; I'd do a Google search, as people have written many articles as well and you might find some good answers there.

Google "SQL query optimization".

Best of luck and God bless.

Answer 6

Lots of different answers for this one. It kinda depends on your specific application or use of SQL.

1) Do not use wildcards.
2) Only return fields that you need. Don't use tablename.*
3) Use passthroughs depending on what you are doing.

Answer 7

a million. INSERT INTO sumup(vala,valb,valc) VALUES(a million,2,vala+valb); this could consequence into:- vala=a million,valb=2,valc=3 2. INSERT INTO sumup(vala,valb,valc) VALUES(a million,'',vala+valb); Now right here valb=empty(NULL). this could consequence into:- vala=a million, valb="" valc=a million.

Answer 8

Use INNER JOIN instead of WHERE
LEFT JOIN .....WHERE SOMEFILE IS Null instead of NOT EXITS