Question

You are HIRED and now the junior DBA says to you that they have a query that takes over 20 minutes to execute and it only returns 5 records. This query is vital to their business and they need you to fix it right now. The query is below

They sit you down at a computer and show you the execution plan which shows:
            “FULL TABLE SCAN” on the Orders table. (SCAN is a linear search and SEEK is binary)

Start with what the query is doing and why it is running slow. Then explain what techniques can be implemented to help speed up the query. You can provide a list of things you wish to do but explain to me why you would do each step. The query they are executing is:

SELECT *      FROM Orders           WHERE CustomerName = ‘ACME WIDGETS’

Answer 1

The most essential reason an inquiry doesn't perform well is on account of it's working with an excessive amount of information. A few inquiries simply need to filter through a considerable measure of information and would not benefit from outside intervention. That is abnormal, however; most terrible questions can be changed to get to less information. We've thought that it was valuable to investigate an ineffectively performing inquiry is in (1)Find out whether your application is recovering more information than you require. (2)That more often than not means it's getting to an excess of lines, yet it may likewise be getting to an excess of segments.

Figure out whether the MySQL server is breaking down a bigger number of columns than it needs.

Here are some great standards you can take after that ought to yield results in one blend or another.hese strategies ought to give you somewhat more understanding into the brains of your DBAs, and in addition the capacity to begin considering procedures in a creation situated manner.

1. Try not to utilize UPDATE rather than CASE:

This issue is exceptionally normal, and however it's not hard to spot, numerous engineers frequently ignore it in light of the fact that utilizing UPDATE has a characteristic stream that appears to be coherent.

2 Don't aimlessly reuse code:

This issue is additionally exceptionally regular. It's anything but difficult to duplicate another person's code in light of the fact that you know it pulls the information you require. The issue is that all the time it pulls significantly more information than you need, and engineers once in a while try trimming it down, so they wind up with a gigantic superset of information.

3. Do pull just the quantity of sections you require:

This issue is like issue No. 2, yet it's particular to sections. It's very simple to code every one of your inquiries with SELECT * as opposed to posting the sections exclusively. The issue again is that it pulls more information than you require. I've seen this mistake handfuls and many times. An engineer does a SELECT * inquiry against a table with 120 sections and a huge number of columns, however winds up utilizing just three to five of them. By then, you're preparing a great deal more information than you need it's a marvel the question returns by any stretch of the imagination. You're handling more information than you require, as well as detracting assets from different procedures.

4. Try not to twofold plunge:

Here's another I've seen a bigger number of times than I ought to have: A put away technique is composed to force information from a table with countless lines.

5.Do know when to utilize temp tables:

This issue is a touch harder to understand, yet it can yield great increases. You can utilize temp tables in various circumstances, for example, keeping you from twofold plunging into substantial tables. You can likewise utilize them to enormously diminish the handling force required to join vast tables. On the off chance that you must join a table to a huge table and there's a condition on that extensive table, you can enhance execution by hauling out the subset of information you require from the huge table into a temp table and joining with that.

6. Do pre-stage information

This is one of my most loved subjects on the grounds that it's an old strategy that is regularly ignored. On the off chance that you have a report or a methodology or even better, an arrangement of them that will do comparable joins to vast tables, it can be an advantage for you to pre-stage the information by joining the tables early and persevering them into a table. Presently the reports can keep running against that pre-arranged table and maintain a strategic distance from the huge join.

7.Optimize question execution by expecting referential trustworthiness

When you are working with numerous tables in an information source, and you have joined different tables, you may have the capacity to enhance inquiry execution by selecting the choice to Assume Referential Integrity from the Data menu. At the point when this choice is chosen, Tableau will incorporate a joined table in the inquiry just in the event that it is particularly referenced by fields in the perspective.

8.For the DBA:

On the off chance that the above focuses are not adequate to address your execution issues, it may be the case that the issues are at a more profound level than can be tended to by an end client. Scene recommends that you draw in your database overseer (DBA) and have them take a gander at the accompanying area for proposals.

9.Seriously, comprehend what you are inquiring:

As pointed out before, comprehending what you are requesting that the database do is an imperative piece of execution tuning. By running a review or follow on the database, you can detach the inquiry that Tableau has gone to the question motor, and can verify whether it is the thing that you anticipate. For instance, does it have the normal GROUP BY and channel conditions; is it doing accumulations in the question instead of returning crude field values; and so forth.