473,701 Members | 2,542 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MSSQL Slow Queries on One Database only all others fine.

3 New Member
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
2 2497
iburyak
1,017 Recognized Expert Top Contributor
Is it possible that you just have more data then before?
Do you have indexes on your tables?
Nov 13 '07 #2
gnomee2
3 New Member
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

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

Similar topics

2
3699
by: Arni Snorri Eggertsson | last post by:
Hi I am trying to design an IO subsystem for my SQL Server and for that I need to try and predict IO activity on each table in my MSSQL Database. My idea is to move the hottest tables into special disk subsystem away from the less hotter tables. So far I have gathered that we have three tables more hot than the others but I have no feeling on ratio on how hot each is and how much activity is on the less hotter tables. I need to...
5
3182
by: Bradley.. .. . . .. .. | last post by:
Requirement: managers can login and only see employee data for the department they manage and any sub-ordinate departments. The departments are in a table that defines a simple tree structure with DepartmentID and ParentDepartmentID. The Employee table holds the DepartmentID that the employee belongs to. First issue is how to tie an Access or MSSQL login to an employee record. In Access this is fairly simple as you just make it part of...
29
2122
by: Geoff Jones | last post by:
Hi All I hope you'll forgive me for posting this here (I've also posted to ado site but with no response so far) as I'm urgently after a solution. Can anybody help me? I'm updating a table on a database i.e. I've modified the table in a DataSet and I want to update it to the SQL database which the table originally came from. I'm using a data adaptor and the update command and it works BUT it is soooooooooo slow!!! Can anybody tell me...
16
1758
by: davemateer | last post by:
Hi We have a current system: Linux / Apache / PHP4.x talking to Microsoft SQL 2000 Thinking about going to: Windows 2003 / PHP4.x talking to the same Microsoft SQL 2000 box...
12
3939
by: grace | last post by:
i am wondering why my database retrieval becomes too slow...we set up a new server (ubuntu, breezy badger) machine where we transferred all our files from the old server.. Our new server uses Asus p5pe-vm motherboard and an Intel Pentium D 3.0Ghz processor, compared to the old one where we uses asrock motherboard and AMD Duron. Both has the same version of mysql installed... To summarized, both machine has the same configuration except...
4
2343
by: ThePhenix | last post by:
Hi everybody, I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend. I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified...
1
2660
by: mkepick | last post by:
migrated databases from sybase to mssql, migration exported sybase written queries written with the application build in query tool and imported to mssql databases. all db and tables migrated without errors. Regular queries without outer joins work fine but the outer join queries are failing with the following message: Microsoft ODBC SQL Server Driver SQL Server Query contain an outer-join request that is not permitted. I think the MSSQL...
4
2679
by: bfoo75 | last post by:
Hi there, I'm new to this forum, but I've come here quite a bit to find solutions to problems other people have encountered... I'm currently trying to query a database up to 5000 times as fast as possible to retrieve certain pricing data for a list of companies. I've run into a few queries that seem to be taking much longer then others and ultimately slow down my function call to a point where it is no longer useful. For instance... I have...
2
9839
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view and datasheet view. From these facts, I conclude that Access is evaluating the query when I go to...
0
8736
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9229
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7824
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6571
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4410
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4662
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3102
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2398
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2035
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.