MSSQL Slow Queries on One Database only all others fine.

[gnomee2]

Hello Everyone,

I have a strange problem that I cannot seem to solve. I have two server running Windows 2003 MSSQL on one IIS on the other.

Out of the blue I have slow queries that cause asp script timeouts and I cannot figure it out. I have moved some test code to other sites on the server and still have these long delays.

I have tried dropping the tables, then the database entirely and restoring and still slow. I run the same code on my test server at home running both iis and sql on the same server and it is a little slow but not too bad and the test server is a duron 1.6.

Here it is:

<!-- #include file="adovbs.inc" -->
 
<%
    response.write("<table width=100% border=0>")
    response.write("<tr><td align=left>")
    Set dbconn = Server.CreateObject("ADODB.Connection")
        dbconn.Open database_three
        dbconn.CommandTimeout = 0
    Set group_master = Server.CreateObject("ADODB.Recordset")
    Set section_master = Server.CreateObject("ADODB.Recordset")
    Set document_master = Server.CreateObject("ADODB.Recordset")
        group_master.open "SELECT * FROM group_master",dbconn,adOpenForwardOnly,adLockReadOnly
        Do while not group_master.EOF
            group_number = clng(group_master("group_number"))
            group_title = group_master("group_title")
            group_description = group_master("group_description")
            group_stat = group_master("group_status")
            response.write("<table width=100% border=0>")
            If group_stat = "A" Then response.write("<tr><td align=left colspan=3 CLASS=activegroup>" & group_title & "</td></tr>")
            If group_stat = "O" Then response.write("<tr><td align=left colspan=3 CLASS=obsoletegroup>" & group_title & "</td></tr>")
            response.write("<tr><td align=justify colspan=3 CLASS=groupdescription>" & group_description & "</td></tr>")
            section_master.open "SELECT * FROM section_master where group_number=" & group_number,dbconn,adOpenForwardOnly,adLockReadOnly
            If not section_master.EOF Then
                Do while not section_master.EOF                        
 
                    sec_no = clng(section_master("section_number"))
                    If section_master("section_status") = "A" Then response.write("<tr><td></td><td align=left colspan=2 class=activesection><font color=black><strong>" & x & ".</strong></font> " & section_master("section_title") & "</td></tr>")                    
                    If section_master("section_status") = "O" Then response.write("<tr><td></td><td align=left colspan=2 class=obsoletesection><font color=black><strong>" & x & ".</strong></font> " & section_master("section_title") & "</td></tr>")                    
                    response.write("<tr><td></td><td align=justify colspan=2 class=sectiondescription><font color=white><strong>" & x & ".</strong></font> " & section_master("section_description") & "</td></tr>")
                    document_master.open "SELECT * FROM document_master where group_number=" & group_number & " and section_number=" & sec_no & " ORDER BY document_seq",dbconn,adOpenForwardOnly,adLockReadOnly
 
                    If not document_master.EOF Then
                        Do while not document_master.EOF
                            If document_master("document_status") = "A" Then response.write("<tr><td></td><td></td><td align=left CLASS=activedocument><font color=black><strong>" & formatnumber(document_master("document_seq"),2) & "</strong></font> " & document_master("document_title") & "</td></tr>")
                            If document_master("document_status") = "O" Then response.write("<tr><td></td><td></td><td align=left CLASS=obsoletedocument><font color=black><strong>" & formatnumber(document_master("document_seq"),2) & "</strong></font> " & document_master("document_title") & "</td></tr>")
                            response.write("<tr><td></td><td></td><td align=left CLASS=documentdescirption><font color=white><strong>" & formatnumber(document_master("document_seq"),2) & "</strong></font> " & document_master("document_details") & "</td></tr>")
                            document_master.MoveNext
                        Loop
                        document_master.Close
                    Else
                        document_master.close
                    End If ' Make sure there are documents to loop thru.
                    section_master.MoveNext
                Loop    
                section_master.Close
            Else
                section_master.Close
            End If ' Make sure there was sections to look at.
 
            group_master.MoveNext
        Loop
response.write("</td></tr></table></td></tr></table>")
        dbconn.Close
    Set dbconn = Nothing
    Set group_master = Nothing
    Set section_master = Nothing
    Set document_master = Nothing
 
%>
 

Keep in mind nothing about my code has changed so it must be something on the server like maybe a security setting or routing issue, I just can't figure out what could have MAGICALLY changed. I am not the only admin on the servers but everyone says they changed nothing. Any help would be GREATLY appreciated.

[iburyak]

Is it possible that you just have more data then before?
Do you have indexes on your tables?

[gnomee2]

Yeah I do have index's. I also ran the optimizer.

The data has not changed, it is only updated as we add documents and none have been added for a couple of months.

Its not really that much data.