9 Tips for creating indexes in SQL Server
(06 April 2008)
To improve performance in SQL Server the first step is to create indexes on the appropriate table-fields. Below a list of 9 tips when creating indexes.
1) Create indexes on the highly selective colums that are used in the WHERE-clause
2) Create indexes on all columns that are used in the WHERE clause in case OR is used
3) Create at least a clustered index on every table. Generally use the column that monotonically increases
4) Create indexes columns that are frequently accessed by WHERE, ORDER BY, GROUP BY, TOP and DISTINCT
5) Only add indexes that will be used frquently
6) Avoid adding too much indexes on dynamic tables (subject to many INSERTs, UPDATEs or DELETEs)
7) For static tables use a FILLFACTOR and PAD_INDEX of 100. For dynamic tables use a lower FILLFACTOR
8) To identify additional indexes use the SS Profiler Create Trace Wizard and trace "Identify Scans of Large Tables"
9) Avoid adding indexes twice.
Of course another step is to improve your queries. For example make them sargable (capable of using indexes). I've created a list with 23 tips to improve your SQL queries.
These tips (together with lots of other facts) can also be found on my SQL Server factsheet for developers. This factsheet (PDF) fits on one sheet can be found here.Labels: Indexes, performance, SQL Server, SQL Server 2000, SQL Server 2005
Posted by Xander Zelders
Performance Tip 1: Avoid non-sargable WHERE-clauses.
Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones.
In case a WHERE-clause is sargable SQL Server can take advantage of an index to speed up the execution of the query. In case it is non-sargable SQL Server can' t.
Sargable operators:
= (best sargable operator)
>
<
>=
<=
EXIST
IS
IN
BETWEEN
LIKE 'abc%' (least best sargable operator)
Non-sargable operators
IS NULL
<>
!=
!>
!<
NOT
NOT EXIST
NOT LIKE
LIKE '%abc'
LIKE '%ABC%'
LIKE '%abc%'
a function on a column
column1 = column1
column1 = column2
For example these queries are non-sargable:
1) SELECT * FROM Customers WHERE Salary IS NULL
2) SELECT * FROM Customers WHERE Left(Name,4) = 'Jane'
3) SELECT * FROM Customers WHERE Name LIKE '%Jane%'
4) SELECT * FROM Customers WHERE PersonID NOT IN (SELECT PersonID FROM Persons WHERE Gender = 'Male')
You can better rewrite them to sargable ones:
1) SELECT * FROM Customers WHERE Salary = 0
2) SELECT * FROM Customers WHERE Name LIKE 'Jane%'
3) Cannot be rewritten
4) SELECT * FROM Customers WHERE PersonID IN (SELECT PersonID FROM Persons WHERE Gender = 'Female')
I have created a factsheet for SQL Server (6.5, 7.0, 2000 and 2005) developers that fits on one A4 (printed on both sides) so that you can laminate it and keep it on your desk. You have to print it with a color-printer to be able to use all information!
This factsheet contains loads of information about SQL Server and can be found here:
SQL Server factsheet for developers
Posted by Xander Zelders
23 Tips to improve the performance of your SQL queries
I've created a list with 23 tips to improve your SQL Server queries. Quite often these tips can also be used for other database-engines like MS Access, Oracle or MySQL.
1) Avoid non-sargable WHERE-clauses. If possible rewrite them to sargable ones
2) In the WHERE-clause use the least likely true AND expression first
3) Avoid using OR in the WHERE-clause if not all colums have an index
4) Avoid using UNION if UNION ALL also does the trick
5) Avoid using UNION of two subsets from the same table. Instead use OR in the WHERE-clause
6) Avoid using SELECT * FROM when only a few columns are needed. Try to specify each column
7) Avoid using COUNT(*) to check the existence of a record. Instead use EXIST
8) Always try to use a WHERE-clause in your query to narrow the results
9) Try to use the best performing operator as possible
10) Avoid using NOT IN. Instead use EXIST, NOT EXIST, IN or LEFT OUTER JOIN with a check for a NULL condition
11) Avoid using IN when EXISTS is also possible
12) Avoid using IN when BETWEEN is also possible
13) In case using IN try to order the list of values so that the most frequently found values are placed first
14) Avoid using SUBSTRING in the WHERE-clause. If possible use LIKE instead
15) Sometimes consider rewriting a query using a OR to multiple queries combined with a UNION ALL
16) Don't use ORDER BY if you don't really need it
17) Keep the width and/or number of sorted columns to the minimum
18) Keep the number of rows to be sorted to a minimum
19) When sorting a specific column often conside making that column a clustered index
20) In case of using HAVING try to minimize the amount of rows using a WHERE clause
21) In case using LIKE on CHAR of VARCHAR colums quite often consider using the full-text search option
22) In case using GROUP BY without an aggregate function try using DISTINCT instead
23) Avoid using variables in a WHERE clause in case the query is located in a batch-file
Thes tips can also be found on my SQL Server factsheet for developers. This factsheet can be found hereLabels: performance, SQL Server, SQL Server 2000, SQL Server 2005
Posted by Xander Zelders
A factsheet for SQL Server developers
(05 April 2008)
I have created a factsheet for SQL Server (6.5, 7.0, 2000 and 2005) developers that fits on one A4 (printed on both sides) so that you can laminate it and keep it on your desk. You have to print it with a color-printer to be able to use all information!
An overview of the contents of this factsheet:
- A checklist for writing fast queries
- Sargability of queries
- A checklist for creating indexes
- Some examples of connectionstrings
- The syntaxis for SELECT, UPDATE, DELETE and INSERT queries
- The syntaxis for CREATE TABLE and ALTER TABLE
- CREATING, ALTERING and DELETING Stored Procedures, Indexes, Views, Triggers and Functions
- A legend of the 'Execution Plan Icons', including some optimizing tips and tricks.
- All datatypes, including memory-usage and indication for best performing types
- All string functions
- All system functions
- All date and time functions
- A list of all dateparts
- The cursor functions
- The mathematical functions
If you have some feedback, please send it to me so that I can improve this factsheet.
 Download the SQL Server factsheetLabels: Factsheet, SQL Server, SQL Server 2000, SQL Server 2005
Posted by Xander Zelders
How to replace certain word with a hyperlink using C#
(09 May 2007)
This code shows, using C#, how to build a function that replaces certain word (-combinations)with a hyperlink and returns a HTML formatted string.
public static string InsertHyperLink(string in_Text, string in_TextToHyperLink, string in_HyperLink)
{
int lv_Pointer = 0;
while (lv_Pointer > -1 && lv_Pointer < in_Text.Length)
{
lv_Pointer = in_Text.ToLower().IndexOf(in_TextToHyperLink.ToLower(),lv_Pointer);
if (lv_Pointer >= 0)
{
in_Text = in_Text.Substring(0,lv_Pointer) +
"<a href=\"" + in_HyperLink + "\" style=text-decoration:none;font-size:11px;>" +
in_Text.Substring(lv_Pointer,in_TextToHyperLink.Length) +
"</a>" +
in_Text.Substring(lv_Pointer + in_TextToHyperLink.Length);
lv_Pointer = lv_Pointer + 78 + in_HyperLink.Length + in_TextToHyperLink.Length;
}
}
return in_Text;
} Labels: formatting, HTML, hyperlink
Posted by Xander Zelders
How to Highlight a specific word in HTML content (C#)
This code snippet shows how to hightlight (or apply a certain style to) a specific word (or combination of words) from a striong in a browser.
public static string HighLight(string in_Text, string in_TextToHighLight, string lv_Style)
{
int lv_Pointer = 0;
while (lv_Pointer > -1)
{
lv_Pointer = in_Text.ToLower().IndexOf(in_TextToHighLight.ToLower(),lv_Pointer);
if (lv_Pointer >= 0)
{
in_Text = in_Text.Substring(0,lv_Pointer) +
"" +
in_Text.Substring(lv_Pointer,in_TextToHighLight.Length) +
"" +
in_Text.Substring(lv_Pointer + in_TextToHighLight.Length);
lv_Pointer = lv_Pointer + 14 + lv_Style.Length + in_TextToHighLight.Length;
}
}
return in_Text;
}
The function returns a HTML formatted string containing the original in_Text with a style (parameter: lv_Style) applied to the given words (parameter: in_TextToHighLight). To change all words 'Internet' to the font color 'red' call the funtion like this:
string MyResultHTML = HighLight(MyOriginalHTML, "internet", "font-color:red"); Labels: formatting, HighLight, HTML
Posted by Xander Zelders
how to extract SRC from IMG elements in HTML code
(22 April 2007)
This piece of code shows how to extract the SRC URL from the IMG element in HTML code, using a regular expression (RegEx). Every match is put into an Array.
public static ArrayList ExtractAllImagesFromHTMLbyURL(string lv_HTML)
{
ArrayList lv_Images = new ArrayList();
try
{
//Find SRC URL from IMG tag
Regex lv_FindAllImages = new Regex(@"]*src\s*=\s*[\""\']?(?
[^""'>\s]*)[\""\']?[^>]*>");
// get all the matches depending upon the regular expression
// and add them to the array.
MatchCollection mMatchCollection = lv_FindAllImages.Matches(lv_HTML);
foreach(Match mMatch in mMatchCollection)
{
string lv_Image = mMatch.Groups["ImageFile"].Value;
lv_Images.Add(lv_Image);
}
return lv_Images;
}
}
Posted by Xander Zelders
|
|
|
|
