Instead of telling the database how to retrieve records , you need only tell the database which records you need, and the database should figure out the most efficient way to get that information. Consequently, much of the advice about improving the efficiency of queries is simply about showing people how to use the tools in SQL to articulate their needs with more precision. Familiarize yourself with your data before your write a single line of code by studying the metadata to make sure that a column really does contain the data you expect.
The SQL editor in Metabase features a handy data reference tab accessible via the book icon , where you can browse through the tables in your database, and view their columns and connections figure 1 :. Metabase gives you many different ways to explore your data: you can X-ray tables, compose simple and custom questions using the query builder and Notebook Editor , convert a saved question to SQL code, or build from an existing native query.
This differs from the reading order left to right, top to bottom you use to compose your query. Query optimizers can change the order of the following list, but this general lifecycle of a SQL query is good to keep in mind when writing SQL. The following tips are guidelines, not rules, intended to keep you out of trouble. Each database handles SQL differently, has a slightly different set of functions, and takes different approaches to optimizing queries.
Help people out including yourself three months from now by adding comments that explain different parts of the code. The catch here is that you introduce a little maintenance overhead: if you change the code, you need to make sure that the comment is still relevant and up to date.
Note that if you have columns with the same name across multiple tables, you will need to explicitly reference them with either the table name or alias. That and your queries might break if you join a table with an ambiguous column name e. Only after removing irrelevant rows, and after aggregating those rows and grouping them, should you include a HAVING clause to filter out aggregates.
Using a function on a column in a WHERE clause can really slow down your query, as the function makes the query non-sargable i. Instead of using the index to skip to the relevant rows, the function on the column forces the database to run the function on each row of the table.
Best to check contents of collection to confirm that something was retrieved. It's so easy to write SQL, it is too easy. Let's take a look No SQL statement is repeated. How can this be done? Set firm standards and lay down law? Keep team sizes really small and hope for the best? Auto-format code to avoid whitespace issues? None of these will be sufficient. Get me all rows for a foreign key.
Get me one row for a primary key. Insert a row; insert a collection of rows. Why write these over and over? Instead, rely on a standard, hopefully generated programmatic interface that takes care of this "basic plumbing. Much less likely to be ignored when the developer writes SQL directly in the application. Copyright Steven Feuerstein - Page 24 Hide single row queries Let's look at specific examples of encapsulations. First: single row queries. Does a row exist? Get me the row for a unique value.
Steps to follow: Do not write your query directly in application code. Build or generate a function to return the information, usually in the form of a record. END; And now call the function Copyright Steven Feuerstein - Page 26 Hide multi-row queries A trickier encapsulation challenge: how do you return multiple rows? We will need a "container" or mechanism that is not just a single instance of a row.
Can do so in package specification or even as a schema level object. Net, VB, etc. Uses the OPEN FOR statement to associate the variable with a query. Table functions to the rescue! A table function is a function that returns a collection and can be called in the FROM clause of a query.
Combine with cursor variables to return these datasets through a function interface. Error management is more complex and critical. Performance impact is greater. Probably will need to hand-code specific update column combinations yourself. Some general guidelines: Avoid hard-coding of error numbers and messages. Build and use reusable components for raising, handling and logging errors. What was the context in which the error occurred? Difficult to ensure execution of common error handling logic.
Usually end up with lots of repetition. Restrictions on how you can specify the error Only for application-specific errors Copyright Steven Feuerstein - Page 35 Object-like representation of an exception An error is a row in the error table, with many more attributes than simply code and message, including: Dynamic message substitution variables Help message how to recover from the problem An error instance is one particular occurrence of an error.
Associated with it are one or more values that reflect the context in which the error was raised. Copyright Steven Feuerstein - Page 38 Specifying the error How should I specify the application- specific error I need to raise?
Benefits: Easier to avoid hard-codings of numbers. Support positive error numbers! Let's revisit that trigger logic using the infrastructure elements Copyright Steven Feuerstein - Page 41 Summary: an Exception Handling Architecture Make sure you understand how it all works Exception handling is tricky stuff Set standards before you start coding It's not the kind of thing you can easily add in later Use standard infrastructure components Everyone and all programs need to handle errors the same way Don't accept the limitations of Oracle's current implementation.
You can do lots to improve the situation. This should be a primary objective for any program. Let's look at Named notation Sometimes the extra typing is worth it! A similar effect exists with similar books relating to other languages. The other potential problem with a work like this is that the techniques only work well when everybody on your team follows them.
So, don't buy a copy of this book; instead, buy 12 and make everybody on your team read it -- only that way will you get the full benefit of the best practice. Once you begin to put the recommendations into use, you'll rapidly wish you had a concise list of all the recommendations. One is provided in the back of the book, but the inevitable fate of that section is to be photocopied and pinned to a vertical surface. I trust that I will not be spoiling the surprise if I whet your appetite by reproducing here -- in order -- the top 8 recommendations but naturally, not their discussion, examples and associated discussion
0コメント