Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2012
    Posts
    10

    Unanswered: Very slow looping

    First of all, I apologize for being a very poor db person, but that is why I'm here with a problem!

    I have to pull values from a mysql table, then loop through the result set using the value in an mssql query as shown below. I also have an array ($all_lobs[]) of about 100 values that must be looped through for each value pulled from the mysql table:


    $tod=date("n/j/Y",time());

    //this is pulling the data from the mysql table
    $query2="select CustId from prospect_CustId ";
    $result2=mysql_query($query2,$link_id_mysql);

    while($custs=mysql_fetch_row($result2))
    {

    //now cycle through LOBS
    for($i=0;$i<$xx;$i++)
    {
    $queryx="SELECT
    bpi.PId,
    bpi.RRF,
    bpi.CustId,
    bpi.PED,
    bpi.PST,
    lob.PId,
    lob.LOB
    FROM Basic bpi, Business lob where bpi.RRF in ('I','R','W' ,'A' ) and bpi.CustId='$custs[0]' and bpi.PED > '$tod' and bpi.PST ='P' and bpi.PId=lob.PId and lob.LOB='$all_lobs[$i]' ";


    $resultx=mssql_query($queryx,$link_id_mssql);
    }
    }


    If I pull only 100 records from the mysql table, this takes about 1 second to run.
    However, if I pull 200 records, it takes about 60 secs to run.
    And, if I pull 300 records, it takes about 200 secs to run.
    After about 500 records, it takes almost a second per record to run!

    Since I have 20,000+ records to pull, this takes hours...

    Unfortunately, we are not allowed to modify the mssql tables at all, only query them.
    ANY help on how to speed this up would be appreciated.

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Except for the all_LOBs array, this should go a bit faster...
    Code:
    SELECT
    	bpi.PId,
    	bpi.RRF,
    	bpi.CustId,
    	bpi.PED,
    	bpi.PST,
    	lob.PId,
    	lob.LOB
    FROM Basic bpi join
    	Business lob on bpi.PId=lob.PId join
    	prospect_CustId pro on pro.CustID = bpi.CustId
    where bpi.RRF in ('I','R','W' ,'A' ) 
      and bpi.PED > '$tod' 
      and bpi.PST ='P' 
      and lob.LOB='$all_lobs[$i]' ";
    Remove the for loop, use this as the $query2, and use the while loop to cycle through the records.

  3. #3
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by MCrowley View Post
    Except for the all_LOBs array, this should go a bit faster...
    Code:
    SELECT
    	bpi.PId,
    	bpi.RRF,
    	bpi.CustId,
    	bpi.PED,
    	bpi.PST,
    	lob.PId,
    	lob.LOB
    FROM Basic bpi join
    	Business lob on bpi.PId=lob.PId join
    	prospect_CustId pro on pro.CustID = bpi.CustId
    where bpi.RRF in ('I','R','W' ,'A' ) 
      and bpi.PED > '$tod' 
      and bpi.PST ='P' 
      and lob.LOB='$all_lobs[$i]' ";
    Remove the for loop, use this as the $query2, and use the while loop to cycle through the records.
    Thanks; except that prospect_CustId is a mysql table and all the other are mssql tables so I can't connect them in a single query...right?

  4. #4
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by MCrowley View Post
    Except for the all_LOBs array, this should go a bit faster...
    Code:
    SELECT
    	bpi.PId,
    	bpi.RRF,
    	bpi.CustId,
    	bpi.PED,
    	bpi.PST,
    	lob.PId,
    	lob.LOB
    FROM Basic bpi join
    	Business lob on bpi.PId=lob.PId join
    	prospect_CustId pro on pro.CustID = bpi.CustId
    where bpi.RRF in ('I','R','W' ,'A' ) 
      and bpi.PED > '$tod' 
      and bpi.PST ='P' 
      and lob.LOB='$all_lobs[$i]' ";
    Remove the for loop, use this as the $query2, and use the while loop to cycle through the records.
    Also, my main question is to why would it slow down per loop just because I am running more loops? Does something queue up, or do I have to flush something?

    I would have thought that the per loop time would stay about the same, just take longer because more loops...

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by jaimedog View Post
    Also, my main question is to why would it slow down per loop just because I am running more loops? Does something queue up, or do I have to flush something?

    I would have thought that the per loop time would stay about the same, just take longer because more loops...
    Looks like a cursor to me. Fetch one row at a time can be very very slow and it's a performance issue.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You should be able to define a linked server in the MSSQL Server. You may need to install parts of the MySQL client on the SQL Server to do this, though.

  7. #7
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by MCrowley View Post
    You should be able to define a linked server in the MSSQL Server. You may need to install parts of the MySQL client on the SQL Server to do this, though.
    I have looked into this before; might be a good time to look into it again.

    Thanks

  8. #8
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by corncrowe View Post
    Looks like a cursor to me. Fetch one row at a time can be very very slow and it's a performance issue.
    My task is to see if I can find one record that meets the query requirements. If yes, I do one thing; if no, I do another. So, this is the only way I can think of to run the query

    Thanks

  9. #9
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by corncrowe View Post
    Looks like a cursor to me. Fetch one row at a time can be very very slow and it's a performance issue.
    One more related question:

    If I run the script 3 times consecutively, it still takes about 1 sec each time for a total of about 3 secs to run 300 records.

    However, if I run the script once for 300 records, it takes about 200 secs (instead of 3 secs).

    And, if I run the 100-record loop 3 times within the same script (with a 5 sec programmed pause between loops), it still takes about 210 secs to run.

    So, why would 3 new launches of the script be different than one launch with 3 loops?
    Different connections to the database?
    Is something waiting for something to complete?

    I would have thought the 5 sec pause would let things catch up.

  10. #10
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by jaimedog View Post
    One more related question:

    If I run the script 3 times consecutively, it still takes about 1 sec each time for a total of about 3 secs to run 300 records.

    However, if I run the script once for 300 records, it takes about 200 secs (instead of 3 secs).

    And, if I run the 100-record loop 3 times within the same script (with a 5 sec programmed pause between loops), it still takes about 210 secs to run.

    So, why would 3 new launches of the script be different than one launch with 3 loops?
    Different connections to the database?
    Is something waiting for something to complete?

    I would have thought the 5 sec pause would let things catch up.
    Did you drop (disconnect) each time or opening a new connection and leaving the previous connection open? I see this behavior in VB.NET when too many connections are opened at a time. But for the solution MCrowley provided is possibly best for this situation.

  11. #11
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by corncrowe View Post
    Did you drop (disconnect) each time or opening a new connection and leaving the previous connection open? I see this behavior in VB.NET when too many connections are opened at a time. But for the solution MCrowley provided is possibly best for this situation.
    We've tried all combos:
    - drop then create new connections in each loop
    - use one persistent connection
    - use one non-persistent connection
    - use multiple connections and cycle through them
    etc

    I'm thinking maybe create a temp table or view on the mssql server using the mysql data?

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by jaimedog View Post
    We've tried all combos:
    - drop then create new connections in each loop
    - use one persistent connection
    - use one non-persistent connection
    - use multiple connections and cycle through them
    etc

    I'm thinking maybe create a temp table or view on the mssql server using the mysql data?
    Did you use profiler to check events? How about query plan? Is there a release resources associate with mysql_fetch_row? I don't see a transaction block so there is no commit to the database? Of course you can check for uncommitted transactions, but I doubt that's the issue.

    Good that you are questioning this issue and not just accepting a solution. I think this will help you in the long run.

  13. #13
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by corncrowe View Post
    Did you use profiler to check events? How about query plan? Is there a release resources associate with mysql_fetch_row? I don't see a transaction block so there is no commit to the database? Of course you can check for uncommitted transactions, but I doubt that's the issue.

    Good that you are questioning this issue and not just accepting a solution. I think this will help you in the long run.
    These are all good questions for me to look into. As I mentioned, I am a real novice on db's so this will get my nose to the grindstone.

    Thanks!

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by jaimedog View Post
    These are all good questions for me to look into. As I mentioned, I am a real novice on db's so this will get my nose to the grindstone.

    Thanks!
    I would think that executing the script 1->m times would not have any difference in the time to complete unless some resource constraint occurred. But again, someone else may have a better explanation?

    Oh, and maybe it's best to put the script into a stored procedure so the query plan is created...cached.

  15. #15
    Join Date
    Mar 2012
    Posts
    10
    Quote Originally Posted by corncrowe View Post
    I would think that executing the script 1->m times would not have any difference in the time to complete unless some resource constraint occurred. But again, someone else may have a better explanation?

    Oh, and maybe it's best to put the script into a stored procedure so the query plan is created...cached.
    Will look into stored procedures.
    As for resources, memory is fine and only one processor out of 4 loads up, and then just for a few seconds (even when the script takes minutes to finish...)

    Thanks!

Posting Permissions

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