Reducing Load time

Results 1 to 7 of 7

Thread: Reducing Load time

  1. #1
    Join Date
    Dec 1969
    Posts
    406

    Default Reducing Load time

    Hi,<BR>My page is big and ugly and I want to increase it&#039;s loading speed.<BR><BR>I have a number of queries in the form (the one below is for ph):<BR><BR>strQ3 = "SELECT TOP 1 data.ph, data.Date, Siteinfo.SiteID "<BR>strQ3 = strQ3 & "FROM data INNER JOIN Siteinfo ON "<BR>strQ3 = strQ3 & "data.SiteID = Siteinfo.SiteID "<BR>strQ3 = strQ3 & "WHERE ("&seasonchoice&") AND Siteinfo.Sitename=(&#039;"&choice&"&#039;) AND data.ph IS NOT NULL "<BR>strQ3 = strQ3 & "ORDER BY data.Date DESC "<BR>Set objRS3 = objConn.Execute( strQ3 )<BR><BR>I already have a query that goes through and does a count of the available data for each parameter (in this case, how many times we have measurements of ph). Would it speed things up to add an If Then statement around the query that basically says "If there is no data for this parameter, don&#039;t do this query", or is that redundant since the query specifies only non-NULL data?<BR><BR>I think I should do this, but I&#039;d like a 2nd opinion before I put in the time. <BR><BR>Also, how much time is saved throwing numbers into queries versus strings (i.e. How much better response would you get from sending SiteID="985" versus SiteID="Bobs Barbeque Shack")?<BR><BR>Dealing with yesterdays mistakes...<BR>SlowMoFo

  2. #2
    Join Date
    Dec 1969
    Posts
    96,118

    Default Well, doing *ANY* query...

    ...takes time! And if you haven&#039;t indexed the Sitename and the ph and whatever field is used in your "seasonchoice" string, then the DB has to go through each and ever record checking to see if the criteria match. I dunno why you think that the IS NOT NULL would take no time to run.<BR><BR>But how will you know AHEAD OF THE QUERY that you *can* do the IF???<BR><BR>***********<BR><BR>How many of these are you doing? What is "a number of..."?<BR><BR>***********<BR><BR>The string vs. number comparison isn&#039;t huge; indexed vs. non-indexed will be a lot bigger. But, yes, numbers will be faster.<BR><BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    406

    Default RE: Well, doing *ANY* query...

    Hey Bill,<BR><BR>Truth be told -- I have about 100 such queries - and my db has 15000 records. I would say that &#062;90% of the records are null.<BR><BR>Do I need to know ahead of time if I can do the If Then? Since one of the first things I do is get a count of the number of records for EACH parameter, can&#039;t I just look at the count for each parameter and if it is &#060;0, then there&#039;s no point in selecting the most recent one (TOP 1 sorted by date), right??<BR><BR>"If (number of records for ph) &#062; 0 Then <BR>&#060;Run Query&#062;<BR>Else <BR>End If"<BR><BR>&#039;Tis a quagmire...<BR>Mike

  4. #4
    Join Date
    Dec 1969
    Posts
    6,476

    Default RE: Well, doing *ANY* query...

    Not really sure I understand the point<BR><BR>I have about 100 such queries - and my db has 15000 records. I would say that &#062;90% of the records are null<BR>Sounds like alot of queries, for a database with only 15000 records where 90% are null<BR><BR>Just seems excessive.<BR>But let me ask a question/statement<BR>Either way it would be a trip in to find out if you should go in... (If you can understand that...) LOL<BR><BR>Maybe theres a way to (Combine) alot of the queries into less queries which in turn would make load time a bit better...<BR><BR>When you talk measurements of PH<BR>this for water/soil testing or something along that line?<BR><BR>A couple years back I did a water application for testing the balancing agents to stabalize water.<BR><BR>Maybe a trick or 2 I did might help out.<BR>Can you explain a bit better what your doing, maybe show a bit of relevent data?<BR><BR>More currious than anything.<BR>;O)


  5. #5
    Join Date
    Dec 1969
    Posts
    96,118

    Default Oh, sure then!

    DEFINITELY skip the query if you know ahead of time there won&#039;t be any result! Truly silly to do otherwise.<BR><BR>But I&#039;d be sorely tempted to think about another methodology on this one!<BR><BR>Do you get 15000 records per month? Added to the db, that is?<BR><BR>Are they spread out *somewhat* throughout the month?<BR><BR>If so, I&#039;d be tempted to keep a "latest update" table separately! Maybe something like:<BR> TABLE: LatestUpdatesByMonth<BR> yearNum : integer<BR> monthNum : integer<BR> category : text/number (to indicate what this is you are tracking)<BR> siteID : FK to sites<BR> when : DateTime<BR><BR>So then, when you added a reading, you&#039;d do:<BR> SELECT * FROM LatestUpdatesByMonth<BR> WHERE yearNum = 2003<BR> AND monthNum = 9<BR> AND siteID = 712<BR> AND category = &#039;avgph&#039;<BR>if you get EOF from that, then you do<BR> INSERT INTO LatestUpdatesByMonth (yearNum, monthNum, sideID, category, when)<BR> VALUES( 2003, 9, 712, &#039;avgph&#039;, #2003/9/13 17:30:15# )<BR>but if you do *NOT* get EOF from that, then you do<BR> UPDATE LatestUpdatesByMonth SET when = #2003/9/13 17:30:15#<BR> WHERE yearNum = 2003<BR> AND monthNum = 9<BR> AND siteID = 712<BR> AND category = &#039;avgph&#039;<BR><BR>Or you can just try to do the UPDATE and if it fails do the INSERT instead.<BR><BR>Now, at the end of the month, you can just do<BR> SELECT * FROM LatestUpdatesByMonth<BR> WHERE yearNum = 2003<BR> AND monthNum = 9<BR> AND siteID = 712<BR>to get the *ALL* the available latest updates for that siteID.<BR><BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    406

    Default RE: Well, doing *ANY* query...

    Hi Russell,<BR>Sorry for the delay - I wanted to get the site up and going so that you could see what I was doing. It&#039;s hard to explain. Check out http://www.kbac-hi.org/kwqid/ -- pick one of the sites and you&#039;ll be taken to the page I was working with.<BR><BR>The reason for the 100 or so queries is that each query goes through the database and finds the most recent data taken for each parameter, and displays it "Recent Score". I&#039;d be happy to provide you with any code you might be interested in, but it would be better if you specified something in particular since the code is getting on for *gasp* 8000 lines.<BR><BR>Mike

  7. #7
    Join Date
    Dec 1969
    Posts
    6,476

    Default Still way to much.

    There is no reason to have anything close to the amount of queries your indicating on that page.<BR>(With a good db design that is.)<BR>Maybe you have it & just dont realize it....<BR><BR>What I see is a form where you pick the name you want.<BR>Then the data pertaining to that name come up for display.<BR><BR>You have parameters.<BR>These are defined by you, not by somone picking them on page right?<BR><BR>So each name only has some or all of these parameters<BR>(test might be needed or may not) That right?<BR><BR>Ok follow me on this(Will be basic as I can)....<BR>Table1 to hold all the names/facilities<BR>ID, FacName, AnyOtherInfo...<BR>10, Crystal Beach<BR>20, Fortune Beach<BR><BR>Table2 to hold all your parameters and readings<BR>ID, FacNameID, paramName, paramReading, intFlag, enDate<BR><BR>1, 10, PH, 7.4, 10/10/02<BR>2, 20, PH, 7.4, 09/09/02<BR>3, 20, PH, 7.4, 10/10/03<BR>4, 10, PH, 7.4, 10/10/03<BR><BR>5, 10, NTU, 97.4, 10/10/02<BR>6, 20, NTU, 70.4, 09/09/02<BR>7, 20, NTU, 80.4, 10/10/03<BR>8, 10, NTU, 70.4, 10/10/03 <BR><BR>SELECT Table1.FaqName, Table2.paramName, Count(Table2.ID) AS CountOfRecords, <BR>Avg(Table2.paramReading) AS AvgOfparamReading,<BR>Max(Table2.paramReading) AS MaxOfparamReading,<BR>Min(Table2.paramReading) AS MinOfparamReading,<BR>Max(Table2.enDate) AS MaxOfEnDate<BR>FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FacNameID<BR>GROUP BY Table1.FaqName, Table2.paramName<BR>HAVING (((Table1.FaqName)=&#039;Fortune Beach&#039;))<BR><BR>So that gives you the Average of the <BR>Count of the records<BR>max reading<BR>min Reading<BR>avg Reading<BR>max Date<BR><BR>For each reading that was entered.<BR>Whats left, get the last record to show the most current readings...<BR>Might be able to work that into the query...<BR>but lets say you need 1 more query.<BR>SELECT Table1.FaqName, Table2.paramReading, Table2.enDate<BR>FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.FacNameID<BR>GROUP BY Table1.FaqName<BR>Where (((Table1.FaqName)=&#039;Fortune Beach&#039;) AND intFlag = 1)<BR><BR>Remember intFlag?<BR>When you go to enter a new PH reading, run an update and set them all to 0<BR>add the new record and set it to 1...<BR>That way you can easily grab the latest records...<BR><BR>By using group by<BR>it will group the data by whats in there already<BR>that way you dont need to query each parameter 1 at a time<BR>there for many queries....<BR><BR>Put the Form selection into a varaible.<BR>varSomeSelect = Request.Form("Beaches")<BR><BR>Just pass that variable to the queries...<BR><BR>Now this was just off the cuff...<BR>But you can see where im getting at.<BR>IF you did poorly, no more than 4 or 5 queries on this page for that data.<BR><BR>Realistically can be done in 2.<BR>Maybe there are some other things Im not considering<BR>But the basic structure is there.<BR>Personally I would add the param name into another table<BR>And have a "Last Reading" and LastDate fields...<BR>Another table to hold relationships<BR>(Just ID&#039;s so you know what tests a facName must provide...<BR>Then you can get everything into 1 query....<BR><BR>Put some thought into it.<BR>It should be simpler than that.<BR><BR>Like I said, I did a water testing program where readings were enters in simular way.<BR>ph, Ta, Cal, Copper, Temp, Stab, Tds, etc....<BR>And then having to calculate what needs to be dont to correct Troubled water...<BR><BR>I know your not doing this<BR>But method is simular.<BR><BR>Let me know if any questions or want more details.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •