SQL Server 2005 Paging Performance Tip

This quick tip demonstrates how to get the total rows as part of the paging query as well as how avoid a common coding error with joins that can harm performance.
I've seen the following technique in several beginner code samples for demonstrating SQL Server 2005's ability to return paged results.
I've added the TotalRows = Count(*) OVER() line to demonstrate how return the total rows returned above and beyond the row count for the paged set. This removes the need for a second query to get the total rows available for paging techniques in your application. In your application, just check to make sure your resultset has records, then just grab the first record and retrieve its TotalRows column value.
Notice that in this query, the JOIN between the Orders table and the Users table is being run across all records that are found NOT just the records returned in the paged set.
declare @StartRow int
declare @MaxRows int
select @StartRow = 1
select @MaxRows = 10
select * from
(select o.*,u.FirstName,u.LastName,
TotalRows=Count(*) OVER(),
ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
from Orders o , Users u
WHERE o.CreateDateTime > getdate() -30
AND (o.UserID = u.UserID) )
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1

If you adjust your query as follows, you will see a substantial boost in performance. Notice this query only performs the join on the returned resultset which is much, much smaller.

SELECT MyTable.*,u.FirstName,u.LastName
FROM
(SELECT o.*, TotalRows=Count(*) OVER(),
ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
FROM Orders o
WHERE o.CreateDateTime > getdate() -30
) as MyTable, Users u
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1
and (MyTable.UserID = u.UserID)

URL validation in vb.net 2005

' URL validation
Public Function UrlIsValid(ByVal url As String) As Boolean
If url.ToLower().StartsWith("www.") Then url = "http://" & url
Dim webResponse As Net.HttpWebResponse = Nothing
Try
Dim webRequest As Net.HttpWebRequest = Net.HttpWebRequest.Create(url)
webResponse = DirectCast(webRequest.GetResponse(), Net.HttpWebResponse)
Return True
Catch
Return False
Finally
If webResponse IsNot Nothing Then webResponse.Close()
End Try
End Function

Email validation in vb.net 2005

' Email validation
Public Function EmailValid(ByVal email As String) As Boolean
' The regular expression rule
Dim Expression As New System.Text.RegularExpressions.Regex("\S+@\S+\.\S+")
' If the email matches the regular expression
If Expression.IsMatch(email) Then
' MessageBox.Show("The email address is valid.")
Return True
Else
' MessageBox.Show("The email address is NOT valid.")
Return False
End If
End Function

Numeric Validation in vb.net 2005

Public Function NumValid(ByVal key_char As String) As Int32
If (Microsoft.VisualBasic.Asc(key_char) <> 57) Then
'e.Handled = True
handel1 = True
End If
If (Microsoft.VisualBasic.Asc(key_char) = 8) Then
'e.Handled = False
handel1 = False
End If
If handel1 = True Then
Return 1
Else
Return 0
End If
End Function

Character Validation in vb.net 2005 without using control

' Text validator
Public Function CharValid(ByVal key_char As String) As Int32
If (Microsoft.VisualBasic.Asc(key_char) <> 90) _
And (Microsoft.VisualBasic.Asc(key_char) <> 122) Then
'Allowed space
If (Microsoft.VisualBasic.Asc(key_char) <> 32) Then
'e.Handled = True
handel1 = True
End If
End If
' Allowed backspace
If (Microsoft.VisualBasic.Asc(key_char) = 8) Then
'e.Handled = False
handel1 = False
End If
If handel1 = True Then
Return 1
Else
Return 0
End If
End Function

UTF8 encoded php mail function

create a php mail function that can handle UTF8 characters across most email readers.

The problem with the php mail is that it does not encode the names and subjects and they could get lost in the transport or be misinterpreted from the email readers. This function actually does the proper encoding and overcomes the php mail deficiency.
—————————–
function UTF8_mail($from,$to,$subject,$message,$cc=”",$bcc=”"){
$from = explode(”<”,$from );
$headers =“From: =?UTF-8?B?”.base64_encode($from[0]).”?= <”. $from[1] . “\r\n”;
$to = explode(”<”,$to );$to = “=?UTF-8?B?”.base64_encode($to[0]).”?= <”. $to[1] ;
$subject=”=?UTF-8?B?”.base64_encode($subject).”?=\n”;
if($cc!=”"){$cc = explode(”<”,$cc );$headers .= “Cc: =?UTF-8?B?”.base64_encode($cc[0]).”?= <”. $cc[1] . “\r\n”;}
if($bcc!=”"){$bcc = explode(”<”,$bcc );$headers .= “Bcc: =?UTF-8?B?”.base64_encode($bcc[0]).”?= <”. $bcc[1] . “\r\n”;}
$headers .=“Content-Type: text/plain; ”. “charset=UTF-8; format=flowed\n”. “MIME-Version: 1.0\n”. “Content-Transfer-Encoding: 8bit\n”. “X-Mailer: PHP\n”;
return mail($to, $subject, $message, $headers);
}
UTF8_mail(“Γιω�?γος Κοντοπουλος ”,“First Last ”,“Θέμα Subject”,“Κείμενο Text”,“”,“Κ�?υφός Φίλοςhidden_friend@email.com”);
—————————–
All this function is accomplishing is to encode each
Name
to
=?UTF-8?B?zpzOuc+HzrHOu863z4I=?= email@domain.com

The emails themselves don’t need to be encoded since an email conventionally can only consist of of latin characters but, we could also confuse the mail server if we did encode them.

Sending E-Mail(s) in PHP

PHP mail() function
E-mails in PHP can be easily sent using the library function 'mail'. This function takes four arguments to send E-mails from a PHP web page and returns 'true' upon successful delivery of Email. The parameters of this function are as follows:
-Recipient E-mail address
-E-mail Subject
-E-mail message (body)
-Headers and additional parameters like the Sender E-mail address
Syntax:
mail( string to, string subject, string message [, string additional_headers [, string additional_parameters]] )

This function returns the boolean value 'True' if the mail is sent successfully, otherwise it returns 'False'.

Example:
To : textbox
From : textbox
Subject : textbox
Message(body) : textbox or textarea
Send : button
Above forms shows how to send email through PHP. And code for the above examples given below, use this code and try it.

Sample PHP Code
//Check whether the submission is made
if(isset($hidSubmit)){
//Declarate the necessary variables
$mail_to=$txtEmailto;
$mail_from=$txtEmailfrm;
$mail_sub=$txtSub;
$mail_mesg=$txtMsg;
//Check for success/failure of delivery
if(mail($mail_to,$mail_sub,$mail_mesg,"From:$mail_from/r/nReply-to:$mail_from"))
echo "E-mail has been sent successfully from $mail_sub to $mail_to";
else
echo "Failed to send the E-mail from $mail_sub to $mail_to";
}
?>