locked
Progress Server taking forever to load my page and dies RRS feed

  • Question

  • User1986916315 posted

    I've built a Default.aspx page in asp.net (C#) and setup a connection through the web.config using an ODBC providerName. I had to build a graph to display a production report and I had to add several SqlDataSources (26) to pull in all the different production totals to display in the graph, but it takes about 10 minutes to load each time and crashes the server if left running. Is there something wrong with my coding?

    Here's an example of my web.config connectionstring section:

    	<connectionStrings>
    		<add name="MyConnectionString" connectionString="Driver=ODBCDriver; DSN=MAINDB;HOST=srv01;database=OdysseyMaindb;UID=pub;PWD=somepassword;PORT=2125;" providerName="System.Data.Odbc"/>
    	</connectionStrings>


    Here are 3 examples of the 26 SqlDataSources. The rest are very similar to these, since they determine the shift total, daily total, and monthly total for each product.

      <asp:SqlDataSource 
      ID="SqlDataSource1" 
      Runat="server" 
      SelectCommand="SELECT SUM(Qty) as Total From Productionhistory WHERE Operation='MOLD 1' AND (Shift = case when CURTIME() < '06:30' then 3 when CURTIME() < '15:00' then 1 when CURTIME() < '23:30' then 2 else 3 end) AND LastDate = SysDate()"
      ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
      ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" >
      </asp:SqlDataSource> 
      
      <asp:SqlDataSource 
      ID="SqlDataSource2" 
      Runat="server" 
      SelectCommand="SELECT SUM(Qty) as DailyTotal From Productionhistory WHERE Operation='MOLD 1' AND LastDate = SysDate()"
      ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
      ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" >
      </asp:SqlDataSource> 
      
      <asp:SqlDataSource 
      ID="SqlDataSource3" 
      Runat="server" 
      SelectCommand="SELECT SUM(Qty) as MonthlyTotal From Productionhistory WHERE Operation='MOLD 1' AND YEAR(LastDate) = YEAR(SYSDATE()) AND MONTH(LastDate) = MONTH(SYSDATE())"
      ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
      ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" >
      </asp:SqlDataSource>

    I have a script manager and timer set to refresh every minute (60000 milliseconds), which was working fine on my local system when I was testing everything on my local SQL Server (with connection strings set to work with my local SQL Server of course) and refreshed quickly...but when I go to the customer location and run this off their Progress Server, it takes 10 minutes to load.

            <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
            
            <asp:Timer ID="Timer1" runat="server">
            </asp:Timer>

    In the codebehind I used the following code to call the SqlDataSources and populate the Label's that are in my graph. Again, this is 3 of the 26.

            //Find Mold 1 Shift Actual
            DataView dv;
            dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
            DataRowView dr = dv[0];
            string shifttotal = dr["Total"].ToString();
    
            if (shifttotal != null && shifttotal != "")
            {
                ShiftTotalLabel.Text = shifttotal;
            }
            else
            {
                ShiftTotalLabel.Text = "0";
            }
    
            //Find Mold 1 Daily Actual
            DataView dv2;
            dv2 = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
            DataRowView dr2 = dv2[0];
            string dailytotal = dr2["DailyTotal"].ToString();
    
            if (dailytotal != null && dailytotal != "")
            {
                DailyTotalLabel.Text = dailytotal;
            }
            else
            {
                DailyTotalLabel.Text = "0";
            }
    
            //Find Mold 1 Monthly Total
            DataView dv3;
            dv3 = (DataView)SqlDataSource3.Select(DataSourceSelectArguments.Empty);
            DataRowView dr3 = dv3[0];
            string monthlytotal = dr3["MonthlyTotal"].ToString();
    
            if (monthlytotal != null && monthlytotal != "")
            {
                MonthlyTotalLabel.Text = monthlytotal;
            }
            else
            {
                MonthlyTotalLabel.Text = "0";
            }


    This is the first time I've worked with a progress database system, so is there some adjustments to my code I need to make to get it running quickly and smoothly, or does everything look ok (meaning the server must need upgraded)?

    Also, in case it matters, this is being displayed by running in a web browser on their local system using localhost:

    Wednesday, June 27, 2012 1:09 PM

Answers

  • User-287491473 posted

    Could you provide any error messages or system event logs that explain what is causing the failure?  It could be serveral things timeouts, OOM, etc...

    You could try running all of the queries seperatly to see which is the slowest and see if you can add some indexes to the DB or tweak the query to get it running faster.  Or even split the report into multiple pages so you are not stuck pulling them all at once.  You could also try running the SQL Server Profiler to see the queries that are running to see if you can find a query or two that seem to take longer than they do on your machine.

    Does thier server have less RAM or a slower processor than your machine?  This can affect the query plan the DB uses to pull your data.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 27, 2012 2:31 PM

All replies

  • User-287491473 posted

    Could you provide any error messages or system event logs that explain what is causing the failure?  It could be serveral things timeouts, OOM, etc...

    You could try running all of the queries seperatly to see which is the slowest and see if you can add some indexes to the DB or tweak the query to get it running faster.  Or even split the report into multiple pages so you are not stuck pulling them all at once.  You could also try running the SQL Server Profiler to see the queries that are running to see if you can find a query or two that seem to take longer than they do on your machine.

    Does thier server have less RAM or a slower processor than your machine?  This can affect the query plan the DB uses to pull your data.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 27, 2012 2:31 PM
  • User1986916315 posted

    I'll have to talk to the server administrator to see about some of this. One big difference was probably just that I only had a few records in my database, for testing purposes, but they have thousands in theirs, since a new record is added every time a part is produced.  I was just hoping I had done something wrong in my coding that was causing a hangup. I tried running a single query once and it seems like it took a while to load it even. I don't remember how long, but it wasn't just a few seconds...seems like it was closer to 30-60 seconds.

    Also, no errors that I saw. The browser just kept thinking and thinking for 10 minutes, then finally loaded...then would automatically refresh and start over. I believe with this occuring over and over it just froze up the server. All the administrator said was it killed it when he left it running and he was afraid to test it out again.

    Wednesday, June 27, 2012 2:44 PM
  • User1986916315 posted

    Just spoke with the administrator. He said the server isn't crashing...it's just getting to a point where it gets so busy thinking that no one can use it and he has to reboot it. He said there are hundreds of thousands of records in the db...so maybe it is just an issue with the server needing more RAM or something...

    Wednesday, June 27, 2012 3:00 PM
  • User-287491473 posted

    Your queries are not that complex looking that they should cause this.  I think the issue is with the indexing on the DB.  If you take your queries and put them in a recent version of SQL Server Management Studio, can you run an Estimated Execution Plan and see if it suggests that you add any missing indexes?  Basically, you should have indexes on any columns you are using for joins or in your where clause.  Indexes are kind of an art, but that is a decent rule of thumb to go by.

    http://weblogs.sqlteam.com/mladenp/archive/2008/12/29/SQL-Server-Management-Studio-2008-suggests-missing-indexes-with-actual.aspx

    Wednesday, June 27, 2012 3:45 PM
  • User269602965 posted

    You can also build datawarehouse type V IEWs or TABLEs to pre-aggregate data,

    and just get the stratified, calculated result sets back much faster.

    ++++++++++

    ODBC is SLOOOWWWWW.  Is there any othe connection drivers available to you that are faster than ODBC,

    like a .NET driver.

    Wednesday, June 27, 2012 7:09 PM
  • User1986916315 posted

    Your queries are not that complex looking that they should cause this.  I think the issue is with the indexing on the DB.  If you take your queries and put them in a recent version of SQL Server Management Studio, can you run an Estimated Execution Plan and see if it suggests that you add any missing indexes?  Basically, you should have indexes on any columns you are using for joins or in your where clause.  Indexes are kind of an art, but that is a decent rule of thumb to go by.

    http://weblogs.sqlteam.com/mladenp/archive/2008/12/29/SQL-Server-Management-Studio-2008-suggests-missing-indexes-with-actual.aspx

    I'll have to talk to the db admin and read up on this some more, since I've never had to do it before. Sounds like I need to learn about it anyway. The actual database we are using is a progress database though, so I wouldn't think we could use SQL Server Management Studio to check it can we?

    Wednesday, June 27, 2012 8:07 PM
  • User1986916315 posted

    You can also build datawarehouse type V IEWs or TABLEs to pre-aggregate data,

    and just get the stratified, calculated result sets back much faster.

    ++++++++++

    ODBC is SLOOOWWWWW.  Is there any othe connection drivers available to you that are faster than ODBC,

    like a .NET driver.

    I'll talk to the db admin about this too. I'm not sure if there are any other drivers besides the ODBC ones, since every example I found while learning how to hook .net up to a progress database was using ODBC, so I'll have to search around. I wish there had been one that would have let me just write all my SELECT strings in normal C#/.net format, instead of having to tweak on it and find the functions that Progress would understand!

    Wednesday, June 27, 2012 8:09 PM
  • User-287491473 posted

    Oh, I saw the SqlDataSources and assumed it was SQL :)  Every DB type should have an Index concept.  So you should still be able to run a similar process to run the queries manually and see which ones are slow.  You can then yourself, or with the help of your DBA, go through and find where adding Indexes would help speed it up.  Good luck!

    Thursday, June 28, 2012 9:56 AM
  • User269602965 posted

    You can also take some of the CASE-WHEN (SELECT CASE) logic out of the WHERE clause

    and pre-process that business logic in behind page/form code

    before making the SQL call.

    +++++++++++

    You can also limit result sets size (number of rows) to what you can reasonably show on the screen.

    Like 20 rows per page of result sets.

    If you are using AJAX, that is often can be managed in the data grid properties/configuration.

    Run one of your queries at the command line and see how many rows it returns,

    a handful, thousands, tens of thousands, millions???

     

    Thursday, June 28, 2012 1:30 PM
  • User1986916315 posted

    Update: The server admin checked into things more and said the server was using up most of its RAM (even without my chart page running), so he has ordered some and we'll see how much that helps first. I believe he said he was ordering around 128 gig.

    Sunday, July 1, 2012 12:25 AM
  • User1381869656 posted

    ODBC is SLOOOWWWWW.  Is there any othe connection drivers available to you that are faster than ODBC,

    like a .NET driver.

    I work with Progress all the time, so my first guess is there's a query<->index mismatch, and the db has to read a lot of records to resolve your query.

    On the "is there a better / faster solution *DBC solution" question - take a look here and see if this helps you out:

    http://www.datadirect.com/resource/adonet/index.html

    Friday, July 6, 2012 10:29 AM
  • User1986916315 posted

    FYI: I finally got this loading fairly quickly. While the server did need RAM, it didn't solve this issue, so I checked out a select string from another program that was accessing similar data and found a query in there that I wasn't using. Once I added it to my select string, it apparently reduced the records down to a reasonable size, because it started loading within about 10-15 seconds now (compared to 10 minutes before)! It is loading fine now!

    Monday, July 23, 2012 1:16 AM