Streamline Quesries to MSSQL to stop timeouts with ouver 3 million lines to query
I have a DB Table storign records for ALL my clients in this same database and my clients can run reports on what they have completed on the site
The table looks like this
CREATE TABLE [dbo].[messagelog_archive]
[autoid] [int] IDENTITY(1,1) NOT NULL,
[SN] [int] NULL,
[TransID] [nvarchar](50) NULL,
[PhoneNumber] [nvarchar](50) NULL,
[Message] [ntext] NULL,
[MessageID] [nvarchar](50) NULL,
[MessageStatus] [nvarchar](50) NULL,
[Originator] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL,
[Pass] [nvarchar](50) NULL,
[Credit] [nvarchar](10) NULL,
[DateSent] [smalldatetime] NULL,
[TimeSent] [smalldatetime] NULL,
[Status] [nvarchar](1) NULL,
[PC2SMSID] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
When a report is run at the moment, There are over 3 million roows and growing. It continually times out with clients.
How do I more efficiently index this table to allow better filtering.
My search code follows
The table looks like this
CREATE TABLE [dbo].[messagelog_archive]
[autoid] [int] IDENTITY(1,1) NOT NULL,
[SN] [int] NULL,
[TransID] [nvarchar](50) NULL,
[PhoneNumber] [nvarchar](50) NULL,
[Message] [ntext] NULL,
[MessageID] [nvarchar](50) NULL,
[MessageStatus] [nvarchar](50) NULL,
[Originator] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL,
[Pass] [nvarchar](50) NULL,
[Credit] [nvarchar](10) NULL,
[DateSent] [smalldatetime] NULL,
[TimeSent] [smalldatetime] NULL,
[Status] [nvarchar](1) NULL,
[PC2SMSID] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
When a report is run at the moment, There are over 3 million roows and growing. It continually times out with clients.
How do I more efficiently index this table to allow better filtering.
My search code follows
<%
' Our own constants:
Const PAGE_SIZE = 75 ' The size of our pages.
' Declare our variables... always good practice!
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Standard looping variable
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("SCRIPT_NAME")
' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request("what")
' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.
%>
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="2" CELLPADDING="2">
<TR>
<TD class=inputbox><h4>SMS Reporting </h4></TD>
</TR>
</TABLE>
<FORM name="demoform" onsubmit="" action="<%= strURL %>" method="get">
<input type=hidden name=what value=reports ID="Hidden2">
<table width=100% ID="Table2" cellpadding=2 cellspacing=2>
<tr>
<td><strong>Start Date:
<input class="formitem" name="startdate" value="<%=date()%>" size="12" onfocus="this.blur()"
readonly ID="Text2">
<a href="javascript:void(0)"
onclick="if(self.gfPop)gfPop.fStartPop(document.demoform.startdate,document.demoform.enddate);return false;"
HIDEFOCUS><img class="PopcalTrigger" align="absmiddle" src="calbtn.gif" width="34" height="22" border="0" alt=""></a>
</strong></td>
<td><strong>End Date:
<input class="formitem" name="enddate" value="<%=date()%>" size="12" onfocus="this.blur()"
readonly ID="Text3">
<a href="javascript:void(0)"
onclick="if(self.gfPop)gfPop.fEndPop(document.demoform.startdate,document.demoform.enddate);return false;"
HIDEFOCUS><img class="PopcalTrigger" align="absmiddle" src="calbtn.gif" width="34" height="22" border="0" alt=""></a>
</strong></td>
</tr>
<tr align="left" valign="top">
<td colspan=2><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="30%"><strong>Containing this number
</strong></td>
<td><strong>
<input name="number" type="text" class="formitem" id="number" value="any" size="40">
</strong></td>
</tr>
</table></td>
</tr>
<!-- <tr align="left" valign="top">
<td colspan=2><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="30%"><strong>Status of Message</strong></td>
<td><strong>
<select name="status" class="formitem">
<option value="%" selected>Any</option>
<option value="0">Delivered to Handset</option>
<option value="?">Delivered to SMS Gateway</option>
<option value="-1">Queued for Submission</option>
<option value="Awaiting delivery receipt">Pending</option>
<option value="error">Error</option>
</select>
</strong></td>
</tr>
</table></td>
</tr>-->
<tr align="left" valign="top">
<td colspan=2><strong>With this status </strong>
<select name="statusreport" size="1" class="formitem">
<option value="all">All reports</option>
<option value="No Report">No reports</option>
<%
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON
MySQL="Select errormessage, errorcode "&_
"from tbl_errormessages "&_
"ORDER BY errorcode;"
Set ObjRs=objDB.Execute(MySQL)
if objrs.eof then
%>
<option value="">No Status Data</option>
<%
else
Do While not objrs.Eof
%>
<option value="<%=objrs("errorcode")%>"><%=objrs("errormessage")%></option>
<%
Objrs.MoveNext
Loop
%>
</select>
<%
end if
objRS.close()
Set objRS = Nothing
%>
</td>
</tr>
<tr align="left" valign="top">
<td> <strong>
Export Results</td>
<td> <input type=radio name=export value="yes" />Yes <br />
<input type=radio name=export value="no" checked />No
</strong></td>
</tr>
<tr align="right" valign="top">
<td colspan=2> <strong>
<input type=submit value="Search" class="formitem">
</strong></td>
</tr>
</table>
</FORM>
<p><br>
<%
'If strSearch <> "" Then
'Display all the details passed
' for i=1 to request.querystring.count
' response.write Trim(request.querystring.key(i))&"; "& Trim(request.querystring.item(i)) &"<br>"
' Next
'Response.End
Set cnnSearch = Server.CreateObject("ADODB.Connection")
' This line is for the Access sample database:
cnnSearch.Open DB_CON
' Build our query based on the input.
startdate = Request("startdate")
enddate = request("enddate")
if what = "reports" then
startdate = date()
enddate = date()
else
startdate = Request("startdate")
enddate = request("enddate")
End if
if startdate = "" then
startdate = date()
end if
if enddate = "" then
enddate = date()
end if
number = request.querystring ("number")
if number = "any" then
number2find = "%"
else
number2find = trim(cstr(number))
end if
messagestatus = request.QueryString ("statusreport")
if messagestatus = "No Report" then
messagestatus = NULL
else
messagestatus =request.QueryString ("statusreport")
end if
orderby = Request.QueryString ("orderby")
if orderby = "" then
orderby = "DateSent Desc"
neworder= "DateSent"
end if
if Request.QueryString ("orderby") = "DateSent" then
orderby = "DateSent"
neworder= "DateSent Desc"
end if
if Request.QueryString ("orderby") = "DateSent Desc" then
orderby = "DateSent Desc"
neworder= "DateSent"
end if
'Response.Write orderby
'Build the SQL Statement
if request.querystring ("what") = "" then
'Search for all messages sent today
select Case number2find
Case Else
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND MessageStatus LIKE '%"& messagestatus &"%' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"ORDER BY " & orderby &" ;"
End Select
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
else
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select Case number2find
Case "%"
'Find any number
if ISNULL (messagestatus) then
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"AND MessageStatus IS NULL "&_
" ORDER BY " & orderby &" ;"
Else if messagestatus = "all" then
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
" ORDER BY " & orderby &" ;"
else
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"AND MessageStatus LIKE '%"& messagestatus &"%' "&_
" ORDER BY " & orderby &" ;"
End if
end if
Case Else
'Find a specific number
strSQL = "SELECT PhoneNumber, Message, DateSent,TimeSent, UserName, Originator, MessageStatus "&_
"FROM messagelog "&_
"WHERE DateSent >= '"& startdate & "' "&_
"AND DateSent <='"& enddate &"' "&_
"AND Phonenumber LIKE '%"& number2find &"%' "&_
"AND UserName = '"& Request.Cookies ("web2sms")("username") &"' "&_
"ORDER BY " & orderby &" ;"
End Select
end if
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE
' Open our recordset
'Response.Write "<br>"& StrSQL & "<br>"
'Response.End
rstSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText
' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount
%>
<%
if request.querystring ("what") = "" then
'Do Nothing
%>
<%else%>
<h4>A search for messages sent between <%=MyDateFormat(startdate)%> and <%=MyDateFormat(enddate)%> has the following results.</h4>
<%
If iRecordCount = 0 Then
' Display no records error.
%>
<p>
<font color="red" size=3>No records found. Please try again.</font>
</p>
<%
Else
if Request.QueryString ("export")="yes" then
Function dbDate(dt)
dbDate = year(dt) & left("00",2-len(month(dt))) &_
month(dt) & left("00",2-len(day(dt))) & day(dt) '&_
'" " & formatdatetime(dt,4)
End Function
' Name for the ouput document
filename = "ReportResults-" & dbDate(now) & Left(Request.Cookies("web2sms")("lastname"),4)
file_being_created="./export/" & filename & ".xls"
' create a file system object
set fso = createobject("scripting.filesystemobject")
' create the text file - true will overwrite any previous files
' Writes the db output to a .xls file in the same directory
Set act = fso.CreateTextFile(server.mappath(file_being_created), true)
' All non repetitive html on top goes here
act.WriteLine("<html><body>")
act.WriteLine("<table border=""1"">")
act.WriteLine("<tr>")
act.WriteLine("<th nowrap>Originating Time</th>")
act.WriteLine("<th nowrap>Destination Number</th>")
act.WriteLine("<th nowrap>Status</th>")
act.WriteLine("<th nowrap>SMS Code</th>")
act.WriteLine("<th nowrap>Message</th>")
act.WriteLine("</tr>")
'Create the data and then write it to an xls file and display the link for the file.
Do While Not rstSearch.EOF
Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open DB_CON
MySQL="Select errormessage,errorcode from tbl_errormessages "&_
"where errorcode ='"& rstSearch.Fields("MessageStatus").Value &"' ;"
Set ObjRs=objDB.Execute(MySQL)
if objrs.eof then
if ISNULL(rstSearch.Fields("MessageStatus").Value) then
reporttrans = " Awaiting Report "
else
reporttrans = "Error"
end if
else
if ISNULL(rstSearch.Fields("MessageStatus").Value) then
reporttrans = " Awaiting Report "
else
reporttrans = objrs("errormessage")
End if
end if
objRS.close()
Set objRS = Nothing
Act.WriteLine("<tr>")
act.WriteLine("<td align=""right"">" & MyDateFormat(rstSearch.Fields("DateSent").Value) & " <br>"&_
"@<br> " & Right(rstSearch.Fields("timesent").Value,10) & "</td>" )
act.WriteLine("<td align=""right"">'" & rstSearch.Fields("phonenumber").Value & "'</td>" )
act.WriteLine("<td align=""right"">" & reporttrans & "</td>")
act.WriteLine("<td align=""right""> " & rstSearch.Fields("MessageStatus").Value & "</td>")
act.WriteLine("<td align=""right"">"& rstSearch.Fields("Message").Value & "</td>")
act.WriteLine("</tr>")
' move to the next record
rstSearch.movenext
' return to the top of the for - next loop
' change this to "loop" to output all the records
' and the corresponding for statement above should be changed also
loop
' All non repetitive html on top goes here
act.WriteLine("</table></body></html>")
' close the object (excel)
act.close
' Writes a link to the newly created excel in the browser
resultcode = "<hr width=50% color=black><span align=center><h4>Excel Document Created Successfully</h4> The file "& "<a target='_blank' href='" & file_being_created &"'>"& filename &"</a> has been created <br><br>Click the file name to download</span><hr width=50% color=black>"
response.Write resultcode
'End of Export!
else
' Move to the page we need to show.
rstSearch.AbsolutePage = iPageCurrent
' Show a quick status line letting people know where they are:
%>
<hr>
<center>
<p><b><u>
<%= iRecordCount %> Records Found.
Displaying page <%= iPageCurrent %>
of <%= iPageCount %>:
</u></b></p></center>
<%
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="0" align=left width=100% ID="Table1" cellpadding=2 cellspacing=2>
<tr>
<td align=center class="tdheading"><a href="<%= strURL %>?what=<%=Request("what")%>&orderby=<%=neworder%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&status=<%=messagestatus%>">Originating Time</a></td>
<td align=center class="tdheading"> Destination Number</td>
<td align=center class="tdheading">Status</td>
<td align=center class="tdheading">Message</td>
</tr>
<%
Recordcount = 0
Do While Not rstSearch.EOF And recordcount < PAGE_SIZE
'this code alternates the color of the table rows...
iCount = iCount + 1
If iCount Mod 2 = 0 Then
sRowColor = "#8F9AF7"
Else
sRowColor = "#FFFFFF"
End If
%>
<tr align=center valign=top bgcolor="<%=sRowColor%>" >
<td align=center valign=top class=inputbox><%= MyDateFormat(rstSearch.Fields("DateSent").Value) %> <br>
@<br>
<%= Right(rstSearch.Fields("timesent").Value,10) %></td>
<td align=center valign=top class=inputbox><%= rstSearch.Fields("phonenumber").Value %></td>
<td align=center valign=top class=inputbox><!--#include file="report_status.asp"--></td>
<td align=left valign=top class=inputbox width=30%><%=rstSearch.Fields("Message").Value%></td>
</tr>
<%
recordcount = recordcount + 1
rstSearch.MoveNext
Loop
%>
<tr>
<td colspan=4 align=center valign=top> <TABLE WIDTH="100%" BORDER="0" CELLSPACING="2" CELLPADDING="2" ID="Table3" align=center>
<TR>
<TD align=center valign=middle> <%
' Now we need to show our navigation links:
' Show "previous" and "next" page links which pass the page to
' view our search parameter. You could also use form buttons
' but I find this looks better.
If iPageCurrent > 1 Then
%> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= iPageCurrent - 1 %>">[<<
Prev]</a> <%
End If
' You can also show page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
%> <%= I %> <%
Else
%> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= I %>"><%= I %></a> <%
End If
Next 'I
If iPageCurrent < iPageCount Then
%> <a href="<%= strURL %>?what=<%=Request("what")%>&startdate=<%=startdate%>&enddate=<%=enddate%>&number=<%=number%>&statusreport=<%=messagestatus%>&page=<%= iPageCurrent + 1 %>">[Next
>>]</a> <%
End If
%> </p>
<%
End if
End If
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
'End If
%>
<%end if%>
</TD>
</TR>
</TABLE></td>
</tr>
</table>
ASKER
Search parameters on this database
CREATE TABLE [dbo].[messagelog_archive]
[autoid] [int] IDENTITY(1,1) NOT NULL,
[SN] [int] NULL,
[TransID] [nvarchar](50) NULL,
[PhoneNumber] [nvarchar](50) NULL,
[Message] [ntext] NULL,
[MessageID] [nvarchar](50) NULL,
[MessageStatus] [nvarchar](50) NULL,
[Originator] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL,
[Pass] [nvarchar](50) NULL,
[Credit] [nvarchar](10) NULL,
[DateSent] [smalldatetime] NULL,
[TimeSent] [smalldatetime] NULL,
[Status] [nvarchar](1) NULL,
[PC2SMSID] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
are [PhoneNumber], [DateSent]
so do I just make these indexes?
I thought they had to be unique like [autoid]
CREATE TABLE [dbo].[messagelog_archive]
[autoid] [int] IDENTITY(1,1) NOT NULL,
[SN] [int] NULL,
[TransID] [nvarchar](50) NULL,
[PhoneNumber] [nvarchar](50) NULL,
[Message] [ntext] NULL,
[MessageID] [nvarchar](50) NULL,
[MessageStatus] [nvarchar](50) NULL,
[Originator] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL,
[Pass] [nvarchar](50) NULL,
[Credit] [nvarchar](10) NULL,
[DateSent] [smalldatetime] NULL,
[TimeSent] [smalldatetime] NULL,
[Status] [nvarchar](1) NULL,
[PC2SMSID] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
are [PhoneNumber], [DateSent]
so do I just make these indexes?
I thought they had to be unique like [autoid]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And while you are at it, change the deprecated ntext data type for Message to nvarchar(MAX)
ASKER
But wouldn;t changing that delete some data if the number of characters exceed the max for nvarchar(max)?
There is no difference in the capacity between ntext and nvarchar(MAX). They both allow up to 2GB of data. Do you think you may need more than that?
ASKER
Thank you so much. As usual, with EE, I not only have the best answer I can get but learn from it as well!
The users should only be looking at subset of the data for each requested result set - getting the indexes right will make a huge difference.