Adding Indexes to SQL Server 2005 Tables

I had the opportunity this week to tune a SQL Server 2005 database to improve the performance of a web application. I first created indexes on the temporary tables within a stored procedure that was causing much of the delay and later discovered through the execution plan, there was specific bottle-neck that could be cleared. Here is an illustration of the temp table indexing:

IF OBJECT_ID('#TempTable') IS NOT NULL
CREATE NONCLUSTERED INDEX [#idxTemp_TempTable_Field1_Field2] ON #TempTable
(
[Field1] ASC,
[Field2] ASC,
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = OFF, 
ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

But by executing the same stored procedure in a query window and selecting ‘Include Actual Execution Plan’ from the toolbar, I was able to see it individual query’s cost within the stored procedure. Then it is possible to look at the most expense queries, their most expensive operations, and add indexes to the permanent tables that reduce execution time.

Quite Possibly the Best Backlink Finder

After working with a number of Backlink Finders including several I have mentioned on CapturedTech previously, I have really grown to love LinkDiagnosis.com. It is not the 100% solution without imperfections but it provides information about Backlinks that I have not seen in other free and paid services.

Google Backlinks

Getting ranked in Google is still about having backlinks from relevant sites without nofollow rel tags for relevant keywords. Page rank and the number of outgoing links on the linked page are also very important. LinkDiagnosis.com combines all these factors into a Link Quality score which you can then sort report results by. Search someone else’s site backlinks and discover why they are ranked so high in Google and then duplicate their backlinks as much as possible.

One of the interesting facts about the Quality Link Score are that the nofollow tag isnt’ the end all to a quality link like many write. Numerous sites with good page rank on individual pages and few outgoing links score better even with nofollow tags. This is quite a gold mine as everyone searches and comments on pages without the nofollow, leaving endless nofollow pages without many outgoing links. Take a look at LinkDiagnosis.com today as they have just improved their server performance to handle the increased demand they are experiencing.

The Importance of the Landing Page for SEO

The Landing Page is a single page website where a visitor comes after clicking on your PPC ad or natural search engine link. The main objective of the page is to introduce the guest to your product or service. A prospect maybe in search of a service that has the ability to resolve his or her problem, and your lead capture page helps them to find this product easily.

The different gears of the landing page serve to educate the prospect, instill confidence in your service or product, and motivate them to make a purchase. For instance, the headline of the page not only grabs the awareness of the visitor, but also gives them a reasonable idea whether he has reached the right place or a site that does not contain what they are looking for. Likewise, the contents of the page enlist the significant features of the product as well as its benefits. This helps the prospect evaluate the goodness of the product, and to ascertain if the product is actually beneficial for him.

The basis of a successful online business is trust, and it is sometimes difficult to gain confidence with the client. The lead capture page makes this easier for you. It helps you to hold the prospect for sometime without him/her navigating away from your site; it talks to him about his problems, and provides him a solution to alleviate his troubles. In doing all these things, the confidence level of the guest increases. Moreover, by displaying such things as security icons (VeriSign) and testimonials in appropriate places, you can further build a good rapport with your prospective customer.

At this time, you may be wondering if all this can be done through a website. What is the need of a lead capture page? Well, if you bring your buyers directly to your company website, it is quite possible that they might get confused while navigating around. They might not be able to find what they are looking for and leave your site without making that needed purchase. Yet an additional possibility is they might find some other interesting product, and move from your site in search of more information. Thus, the prospect visited your location, but did not take any action. In nutshell, your entire SEO campaign failed.

A landing page helps you to be in command of your SEO campaign completely. It allows you to wholly showcase the product that you want to sell. You get an opening to familiarize the prospect with the diverse aspects of the product. The guest, on the other hand, gets all the vital information, including special offers and discounts, about the product in one place. Furthermore, the lead capture page should not have any navigational links, allowing the prospect to remain focused and not to wander away. It is surely easy to convince a knowledgeable prospect to buy the product from such a page.

The search engines today are accountable for almost 90% of the traffic to a site. A well-built landing page has the potential to capture all this traffic and make your online business venture booming.

Gagandeep Singh, is a Freelance Writer who writes on Landing Page Optimization and Internet Marketing.

Another Great SQL Server Grep Function

The first SQL Server Grep function I posted is one of the most popular items on the site to today therefore I thought I would share another great version of the MS SQL string search function you can use to search for terms within your database. This version uses a temporary table to store the results from a cursor and then displays the results to the output screen before dropping the temporary table.

create procedure searchforstring
@searchstring varchar(100)
as
begin
declare @SQLString as varchar(1000)
declare @TableName as varchar(255)
declare @ColumnName as varchar(255)
declare @Count as int

DECLARE C_SOURCE CURSOR FOR  
select sysobjects.name tablename,syscolumns.name columnname,'select top 1 * 
from [' + sysobjects.name + '] 
where ' + syscolumns.name 
+' like ''%' + @searchstring + '%'' ' SQL_String
from syscolumns , sysobjects, systypes
where sysobjects.xtype='U' and sysobjects.id = syscolumns.id  and 
systypes.xtype=syscolumns.xtype
and systypes.name <> 'sysname'
and systypes.name  in ('varchar','char','nvarchar','nchar','text','text')
order by sysobjects.name,syscolumns.colid

create table #result(outputstring varchar(500))

OPEN C_SOURCE  
FETCH NEXT FROM C_SOURCE INTO @TableName,@ColumnName,@SQLstring
WHILE @@FETCH_STATUS = 0  

begin
exec(@SQLString)
--print @SQLString
if @@rowcount > 0
        insert into #result values( '[' + @TableName + '].' + @ColumnName)

FETCH NEXT FROM C_SOURCE INTO @TableName,@ColumnName,@SQLstring
END  
CLOSE C_SOURCE  
DEALLOCATE C_SOURCE  
select * from #result
drop table #result
END

Creating Tabbed Content With the ASP.Net Menu Control

A recent design required tabbed content on the section page of a website. After doing some research I came upon a solution for tabbed content using the ASP.Net Menu Control. By placing a Menu Control on the page along with a Multiview Control, it is possible to show the different items of the Multiview Control when menu items are selected.

The page markup looks like this:

<table border="0" width="100%">
<tr>
    <td width="151px">
    <asp:Menu
            ID="mnuTabbedContent"
            runat="server"
            Orientation="Vertical"            
            OnMenuItemClick="mnuTabbedContent_MenuItemClick"            
            >
        <Items>
            <asp:MenuItem ImageUrl="~/Images/greentab.png" 
                          ToolTip="Item 1" Text=" " Value="0">asp:MenuItem>
            <asp:MenuItem ImageUrl="~/Images/whitetab.png" 
                          ToolTip="Item 2" Text=" " Value="1">asp:MenuItem>
            <asp:MenuItem ImageUrl="~/Images/whitetab.png" 
                          ToolTip="Item 3" Text=" " Value="2">asp:MenuItem>
            <asp:MenuItem ImageUrl="~/Images/whitetab.png" 
                          ToolTip="Item 4" Text=" " Value="3">asp:MenuItem>
            <asp:MenuItem ImageUrl="~/Images/whitetab.png" 
                          ToolTip="Item 5" Text=" " Value="4">asp:MenuItem>
        Items>
    asp:Menu>
    td>
    <td width="521px">
    <asp:MultiView 
        ID="MultiView1"
        runat="server"
        ActiveViewIndex="0"  >
       <asp:View ID="Tab1" runat="server"  >
            <table cellpadding="0" cellspacing="0">
                <tr valign="top">
                    <td style="width:490px">
            ' Content 1
                    td>
                tr>
            table>
         asp:View>
        <asp:View ID="Tab2" runat="server">
            <table cellpadding="0" cellspacing="0">
                <tr valign="top">
                    <td style="width:490px">
            ' Content 2
                    td>
                tr>
            table>
        asp:View>
        <asp:View ID="Tab3" runat="server">
            <table cellpadding="0" cellspacing="0">
                <tr valign="top">
                    <td style="width:490px">
            ' Content 3
                    td>
                tr>
            table>
        asp:View>
        <asp:View ID="Tab4" runat="server">
            <table cellpadding="0" cellspacing="0">
                <tr valign="top">
                    <td style="width:490px">
            ' Content 4
                    td>
                tr>
            table>
        asp:View>
        <asp:View ID="Tab5" runat="server">
            <table cellpadding="0" cellspacing="0">
                <tr valign="top">
                    <td style="width:490px">
            ' Content 5
                    td>
                tr>
            table>
        asp:View>
    asp:MultiView>
    td>
tr>
table>

While the code to display the desired content is like this:

    Protected Sub mnuTabbedContent_MenuItemClick(ByVal sender As Object, ByVal e As MenuEventArgs) 
Handles mnuTabbedContent.MenuItemClick

    ' Show the Content
        MultiView1.ActiveViewIndex = Int32.Parse(e.Item.Value)

    ' Make All Tabs White
        mnuTabbedContent.Items(0).ImageUrl = "~/Images/whitetab.png"
        mnuTabbedContent.Items(1).ImageUrl = "~/Images/whitetab.png"
        mnuTabbedContent.Items(2).ImageUrl = "~/Images/whitetab.png"
        mnuTabbedContent.Items(3).ImageUrl = "~/Images/whitetab.png"
        mnuTabbedContent.Items(4).ImageUrl = "~/Images/whitetab.png"

    ' Make Selected Tab Green
        Select e.Item.Value
            Case 0 : mnuTabbedContent.Items(e.Item.Value).ImageUrl = "~/Images/greentab.png"
            Case 1 : mnuTabbedContent.Items(e.Item.Value).ImageUrl = "~/Images/greentab.png"
            Case 2 : mnuTabbedContent.Items(e.Item.Value).ImageUrl = "~/Images/greentab.png"
            Case 3 : mnuTabbedContent.Items(e.Item.Value).ImageUrl = "~/Images/greentab.png"
            Case 4 : mnuTabbedContent.Items(e.Item.Value).ImageUrl = "~/Images/greentab.png"
            Case Else : mnuTabbedContent.Items(e.Item.Value).ImageUrl = "~/Images/greentab.png"
        End Select

    End Sub

Creating Rounded Corners and Building the Table in ASP.Net Code

I have worked with a number of rounded corner solutions over the past 6 months since the interns that do design at work love rounded corners. I think the best method we have developed is to slice an image into 8 small images and then build a table surrounding content that shows the corners desired. I like to use Paint.Net to create the 8 images of 15px by 15px each. As you can imagine the images are top_left, top, top_right, left, right, etc.

The table code is like so:

<table cellpadding="0" cellspacing="0">
        <tr id="header">
            <td width="15px" height="15px"><img alt="gray_topleft" src="gray_topleft.png" />td>
            <td height="15px" style="background-image:url('gray_top.png'); background-repeat:repeat-x;">td>
            <td width="15px" height="15px"><img alt="gray_topright" src="gray_topright.png" />td>
        tr>
        <tr id="body">
            <td width="15px" style="background-image:url('gray_left.png'); background-repeat:repeat-y;">td>
            <td>td>
            <td width="15px" style="background-image:url('gray_right.png'); background-repeat:repeat-y;">td>
        tr>
        <tr id="footer">
            <td width="15px" height="15px"><img alt="gray_bottomleft" src="gray_bottomleft.png" />td>
            <td height="15px" style="background-image:url('gray_bottom.png'); background-repeat:repeat-x;">td>
            <td width="15px" height="15px"><img alt="gray_bottomright" src="gray_bottomright.png" />td>
        tr>
table>

With our web solution we also need to create a user control that will be included within other user controls. For that reason we cannot use the html as it is but need to transform the html into VB.net controls and add them to the user control object. Here is an example of the above html code in VB.Net.

        Dim objTable As New Table
        objTable.CellPadding = 0
        objTable.CellSpacing = 0

        Dim objHeaderRow As New TableRow
        Dim objHeaderCell1 As New TableCell
        Dim objHeaderCell2 As New TableCell
        Dim objHeaderCell3 As New TableCell

        objHeaderCell1.Width = 15
        objHeaderCell1.Height = 15
        objHeaderCell1.Style.Add("background-image", "url('../Themes/Images/gray_topleft.png')")
        objHeaderCell1.Style.Add("background-repeat", "no-repeat")

        objHeaderCell2.Height = 15
        objHeaderCell2.Style.Add("background-image", "url('../Themes/Images/gray_top.png')")
        objHeaderCell2.Style.Add("background-repeat", "repeat-x")

        objHeaderCell3.Width = 15
        objHeaderCell3.Height = 15
        objHeaderCell3.Style.Add("background-image", "url('../Themes/Images/gray_topright.png')")
        objHeaderCell3.Style.Add("background-repeat", "no-repeat")

        objHeaderRow.Cells.Add(objHeaderCell1)
        objHeaderRow.Cells.Add(objHeaderCell2)
        objHeaderRow.Cells.Add(objHeaderCell3)

        Dim objBodyRow As New TableRow
        Dim objBodyCell1 As New TableCell
        Dim objBodyCell2 As New TableCell
        Dim objBodyCell3 As New TableCell

        objBodyCell1.Width = 15
        objBodyCell1.Style.Add("background-image", "url('../Themes/Images/gray_left.png')")
        objBodyCell1.Style.Add("background-repeat", "repeat-y")

        Dim objBodyContainer As New MessageContainer
        BodyTemplate.InstantiateIn(objBodyContainer)
        objBodyCell2.Controls.Add(objBodyContainer)
        objBodyCell2.Style.Add("background-color", "#E4E2E2")

        objBodyCell3.Width = 15
        objBodyCell3.Style.Add("background-image", "url('../Themes/Images/gray_right.png')")
        objBodyCell3.Style.Add("background-repeat", "repeat-y")

        objBodyRow.Cells.Add(objBodyCell1)
        objBodyRow.Cells.Add(objBodyCell2)
        objBodyRow.Cells.Add(objBodyCell3)

        Dim objFooterRow As New TableRow
        Dim objFooterCell1 As New TableCell
        Dim objFooterCell2 As New TableCell
        Dim objFooterCell3 As New TableCell

        objFooterCell1.Width = 15
        objFooterCell1.Height = 15
        objFooterCell1.Style.Add("background-image", "url('../Themes/Images/gray_bottomleft.png')")
        objFooterCell1.Style.Add("background-repeat", "no-repeat")

        objFooterCell2.Height = 15
        objFooterCell2.Style.Add("background-image", "url('../Themes/Images/gray_bottom.png')")
        objFooterCell2.Style.Add("background-repeat", "repeat-x")

        objFooterCell3.Width = 15
        objFooterCell3.Height = 15
        objFooterCell3.Style.Add("background-image", "url('../Themes/Images/gray_bottomright.png')")
        objFooterCell3.Style.Add("background-repeat", "no-repeat")

        objFooterRow.Cells.Add(objFooterCell1)
        objFooterRow.Cells.Add(objFooterCell2)
        objFooterRow.Cells.Add(objFooterCell3)

        objTable.Rows.Add(objHeaderRow)
        objTable.Rows.Add(objBodyRow)
        objTable.Rows.Add(objFooterRow)

        Me.Controls.Clear()
        Me.Controls.Add(objTable)

ASP.Net Form Issues Solved

I was working on a ASP.Net form this week that created several issues that I need to research to solve. Radio Buttons on the page were making all other controls undetectable within the code. Validation text was wrapping from next to the validated control to underneath the control. And the Reset button on the page was causing validation to occur which is not desirable.

Radio Button List

I was able to solve the issue of radio buttons on the page affecting all other controls by wrapping the buttons into a radio button list.

"rdSpecificInvoiceInfo" runat="server">
     "rdSpecificInvoiceInfoY" runat="server" value="Yes" />
     "rdSpecificInvoiceInfoN" runat="server" value="No" />

Validation Text Wrapping

The second issue was solved by adding a style tag to the validation control itself.

"white-space:nowrap" 
ID="rfvYourName" visible="False" runat="server" ErrorMessage="Your Name is Required" 
Font-Bold="True" Font-Italic="False" ControlToValidate="txtYourName" 
Enabled="True">

No Validation on Reset

The reset button was the simplest but most difficult to solve. I found one article detailing the use of validation groups which did nothing but waste time in the implementation. Finally I found some information online about setting one property on the button, CausesValidation.

"bReset" CausesValidation="False" CssClass="form_control" 
runat="server" ToolTip="Reset" Width="60" Text="Reset" />

Shoemoney Offers Tools and Training

If you liked the recent post on getting great back links with good page rank then you should take a look at Shoemoney Tools and the corresponding free training called ShoemoneyX. And one of the best internet marketing gurus, Jeremy Shoemaker, is now offering an inexpensive 9 day trial for $3.95 to his exclusive marketing toolset.

I have subscribed to his toolset on and off for the past 6 months and really like his backlinker checker which produces more results and more accurate results than the two tools I mentioned in the backlink article. The training I have been working through in my spare time and have found the material very good.

Wordpress Plugins for Better Posting

On a site that I am the administrator for, there is a writer who likes to not add site tags to an article but instead adds Technorati tags from within Live Writer to a post prior to publishing. If you have ever used Live Writer and inserted these tags to a post then you know that the tags created are bad. They link to pages that do not exist cause they use the directory structure /tags/ instead of the correct directory /tag/ for each link.

Auto-tags

Today I found two plugins that will solve these issues for the writer who refuses to change. The first is called Auto-tags. This plugin works with two different services, Yahoo! and tagthe.net, to add a preselected number of tags automatically to each post that is published to the website. It searches the content of the post for the most relevant tags and adds them. What could be better than automatically tagging posts?

Search and Replace

The second Wordpress plugin is call Search and Replace.You have probably already figured out what the purpose of this plugin is just from the name. But it searches the Wordpress database in a number of different ways that you specify for certain text and then replaces the text. Great for my Technorati tag issue. I tried it today and it found 26 posts that needed to have tags corrected. Now all the search engines love the site once again. At least that’s the hope.

Page Ranked Links with Affiliate Tools

There’s a number of decent articles on the Internet about getting back links to your website by requesting links, writing good content, and leaving comments. But if you’re getting back links on terrible sites that aren’t indexed or have page ranks that look like goose eggs then you are probably wasting your time. I have been there and didn’t like the feeling.

Page Rank Searches

With four affiliate tools in two different categories you can find bank links that have good page rank and therefore are worth the time to pursue. The first category is Page Rank searching. I have found two good tools within this category, they are SEO Chat’s Page Rank Search and SERP Analytics Google Search. With these two tools you can find sites with great rankings by keywords that you are focusing on in your writing. And you can search good sites for pages that rank higher than others by using the ‘site:’ directive. SERP Analytics sometimes stops working after too many searches as Google flags your IP as a spam source. It is necessary to wait an hour or so when this happens and search Google one more time entering a CAPTCHA keyword to begin to get Google search results again.

Backlink Checkers

The other category of tools that can help in finding good link locations are back link checkers. iWebTool Backlink Checker and BRL Backlink Checker Tool are both very good. Once you find sites that rank well for keywords that you are targeting, you can use these tools to locate the best links the sites are receiving that give them the prominent search positions. iWebTool can also stop working after a number of searches so you will have to switch services at some point or wait until the tool is available again.