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
<%


' 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&nbsp;</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) & "&nbsp;<br>"&_
                                        "@<br>&nbsp;" & 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) %>&nbsp;<br>
                              @<br>
                              &nbsp;<%= 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>

Open in new window

LVL 1
souldjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kenwagersCommented:
You'll need to add indexes to the fields most commonly used as selection criteria in the queries.  

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.
0
souldjAuthor Commented:
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]
0
kenwagersCommented:
No, they don't need to be unique.  Here's the SQL to create the two indexes:

CREATE NONCLUSTERED INDEX [IX__messagelog_archive__PhoneNumber] ON [dbo].[messagelog_archive]
([PhoneNumber] ASC)
GO
CREATE NONCLUSTERED INDEX [IX__messagelog_archive__DateSent] ON [dbo].[messagelog_archive]
([DateSent] ASC)
GO


Naming convention is just my preference.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Anthony PerkinsCommented:
And while you are at it, change the deprecated ntext data type for Message to nvarchar(MAX)
0
souldjAuthor Commented:
But wouldn;t changing that delete some data if the number of characters exceed the max for nvarchar(max)?
0
Anthony PerkinsCommented:
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?
0
souldjAuthor Commented:
Thank you so much. As usual, with EE, I not only have the best answer I can get but learn from it as well!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.