Image : http://www.flickr.com
So, you want help writing a query in SQL? You got it! You are about to learn some best practices you can use the few seconds that will reward you for years to come.
OK, I admit, I'm a programmer, too. So I got to show off the MCDBA, I knew something about the database. So lately, I did more DBA work than programming. Yes, I hear you cry "traitor!" I walked over to the dark-side, back-end. "
The good news is that I can(To coin a well overused phrase), feels your pain. Many developers view the database as a kind of necessary evil. In my experience, that the average developer knows just enough SQL to insert, update and delete one record from the database. Let them handle tough DBA . Unfortunately, many developers do not have access to DBA (at least not one willing to work with them), and must come to SQL on your own. Likewise, imagine, peer respect, the generous increase in salary will beprofit, and a nomination for sainthood you get when you can do "stupid to go faster."
So, here are three biggies that I have found that the average "Joe Programmer" can give force to raise their query response time.
One: it works in Dev!
To begin, let's thInk outside the box a bit. I know I said I was going to give tips on how to make your query faster … and I did. However, this may be a little off the beaten path. Butno, thanks for the great tip? Something you have not heard a hundred times?
Here's the background: an app is possible, working great and Roll begins. Within a few days, you start hearing noises that some of the applications are slow. These grumbles soon help tickets. Help those tickets soon be meeting with the boss and users. These meetings often turn into alcoholism …. You will get the point.
This unfortunate situation arises very often, andthere is a relatively simple solution to it. You see, everything always works well in your development environment, because you have quite a few records in the database development! Sure, if I only had 30 entries in my main table, my application will work great too! Once in the real world, and several hundred thousand or millions of records are input or imported, the need for performance tuning becomes critical apparentness.
The solution is simple, create the samenumber of records in its development environment, as you would expect that in the first year of operation. If you import data (say, from legacy systems or third-party channels) is pump them in. If you do not, then there are many open source tools for creating false information that closely correspond to your current data. Now, how did you first written request, we have a really good idea if you need to focus on specific request, to avoid future headaches and adviceaccounts.
B) Find index
Many developers ignore the index altogether. Many do not understand them. Index is the only content of the data in the database. While application development, print database schema, including indexes, so you know what they are when writing your questions. If the appropriate one is missing, someone has experience with indexes to create (whether it's you, the developer on the other side, orDBA)
Here's impact. Suppose you write a contact management system. You should probably table with first name, last name, etc. For simplicity, we will have it all in one big table with a clear field names. You could probably write a query that looks something like this:
Select Person_ID, Name_First, Name_Last
From Person_Data
If Name_Last = @ NameLast
A = @ Name_First NameFirst
With 100 lines, it works immediately. However, as your database growsup to several thousand records, the query will be slow, possibly hitting seconds range. If you just add the column index and Name_Last Name_First, you have essentially an immediate return for the life of the application.
III) function calls are evil incarnate!
The following questions look very innocent. However, they are all killers applications as soon as the table grows to more than a few thousand lines:
Select Person_Data_ID, Name_First,Name_Last
From Person_Data
If Next_Contact_Date> GetDate ()
SELECT COUNT (*)
From Person_Data
If Upper (Name_Last) = 'Smith'
Select AVG (Collar_Size)
From Person_Data
If dbo.MyFunc (Person_Data)> dbo.MyOtherFunc (22)
For each of these statements, table scan will be used to try and retrieve data. Table scan is just an operation where the database looks at each entry in the table to see which ones meet thecriteria in the WHERE clause. We'd much rather use a database index, and retrieve only 20 records, which requires more than reading 200,000 records in a table scan for the 20 records it requires. However, the use of functions (GetDate (), upper (), myfuncQ () MyOtherFunc (), etc.), keep it at that.
Why features to the database using indexes? It is simply because the database is the outcome function. Whenever GetDate () is called, otherThe answer could come back. Whenever myfuncQ () is called to return with a different result, because the function could be based on input values, time of day or date, which is covered elsewhere in the database. The only way the database can not be sure answers to the call function is to call and see what is the answer. Since the database does not know in advance what the answer is, you can not go to the corresponding indexinput.
So, if possible, avoid the use of functions. Great solution to these problems are (in order):
Select Person_Data_ID, Name_First, Name_Last
From Person_Data
If Next_Contact_Date> GetDate ()
Becomes
Declare @ DateTime DT
SET @ DT = GetDate ()
Select Person_Data_ID, Name_First, Name_Last
From Person_Data
If Next_Contact_Date> @ DT
Ox, the database gets value GetDate () in advance. Since the value@ DT of change, while the query runs, you can use a suitable index.
SELECT COUNT (*)
From Person_Data
If Upper (Name_Last) = 'Smith'
Becomes
SELECT COUNT (*)
From Person_Data
Where Name_Last = 'Smith'
"Huh?" I heard that you cry. Everything she did was take the upper () function out. What about small and big letters?? Actually, I'm cheating a bit here. With Microsoft SQL Server, the default index collations are case insensitive. In plainterms, this means that the search is case-sensitive by default. With Oracle and other database engines, you can choose to keep your index-sensitive, so why not? For a simple name search, it's a good idea. And it avoids the dreaded table scan.
The third example is a bit tougher. Basically, if your function is extremely complicated, you probably do not have no choice but to use it and let the user know to expect poor performance (please wait screen goes a long way here, guys).However, if the function is only a simple multiplication (ie Unit_Price * Quantity), then simply return the math and avoid the office. Please note: Many developers are beaten them to the reusable code, so changes can be made at one central location. The fact is that SQL is not the same as the C! You should adjust. One of the adjustments is to give up the code repeatedly in this context for betterperformance.
End
Well, there you have it. Three tips to start you on the path to better performance. Incorporate these into your daily and get results without diets, without exercise, and no drilling Counting calories.
±1±: New Computer Monitors ±1±: Lcd Tv Monitor Prices
