By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

MSSQL Slow Queries on One Database only all others fine.

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. <!-- #include file="adovbs.inc" -->
  2.  
  3. <%
  4.     response.write("<table width=100% border=0>")
  5.     response.write("<tr><td align=left>")
  6.     Set dbconn = Server.CreateObject("ADODB.Connection")
  7.         dbconn.Open database_three
  8.         dbconn.CommandTimeout = 0
  9.     Set group_master = Server.CreateObject("ADODB.Recordset")
  10.     Set section_master = Server.CreateObject("ADODB.Recordset")
  11.     Set document_master = Server.CreateObject("ADODB.Recordset")
  12.         group_master.open "SELECT * FROM group_master",dbconn,adOpenForwardOnly,adLockReadOnly
  13.         Do while not group_master.EOF
  14.             group_number = clng(group_master("group_number"))
  15.             group_title = group_master("group_title")
  16.             group_description = group_master("group_description")
  17.             group_stat = group_master("group_status")
  18.             response.write("<table width=100% border=0>")
  19.             If group_stat = "A" Then response.write("<tr><td align=left colspan=3 CLASS=activegroup>" & group_title & "</td></tr>")
  20.             If group_stat = "O" Then response.write("<tr><td align=left colspan=3 CLASS=obsoletegroup>" & group_title & "</td></tr>")
  21.             response.write("<tr><td align=justify colspan=3 CLASS=groupdescription>" & group_description & "</td></tr>")
  22.             section_master.open "SELECT * FROM section_master where group_number=" & group_number,dbconn,adOpenForwardOnly,adLockReadOnly
  23.             If not section_master.EOF Then
  24.                 Do while not section_master.EOF                        
  25.  
  26.                     sec_no = clng(section_master("section_number"))
  27.                     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>")                    
  28.                     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>")                    
  29.                     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>")
  30.                     document_master.open "SELECT * FROM document_master where group_number=" & group_number & " and section_number=" & sec_no & " ORDER BY document_seq",dbconn,adOpenForwardOnly,adLockReadOnly
  31.  
  32.                     If not document_master.EOF Then
  33.                         Do while not document_master.EOF
  34.                             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>")
  35.                             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>")
  36.                             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>")
  37.                             document_master.MoveNext
  38.                         Loop
  39.                         document_master.Close
  40.                     Else
  41.                         document_master.close
  42.                     End If ' Make sure there are documents to loop thru.
  43.                     section_master.MoveNext
  44.                 Loop    
  45.                 section_master.Close
  46.             Else
  47.                 section_master.Close
  48.             End If ' Make sure there was sections to look at.
  49.  
  50.             group_master.MoveNext
  51.         Loop
  52. response.write("</td></tr></table></td></tr></table>")
  53.         dbconn.Close
  54.     Set dbconn = Nothing
  55.     Set group_master = Nothing
  56.     Set section_master = Nothing
  57.     Set document_master = Nothing
  58.  
  59. %>
  60.  
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.
Nov 12 '07 #1
Share this Question
Share on Google+
2 Replies


iburyak
Expert 100+
P: 1,017
Is it possible that you just have more data then before?
Do you have indexes on your tables?
Nov 13 '07 #2

P: 3
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.
Nov 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.