SitePoint Sponsor

User Tag List

Results 1 to 21 of 21

Threaded View

  1. #1
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Loops Producing Slow Script Execution

    I'm trying to get my head around the best database design from a speed perspective.

    I've got 3 tables.

    Table A - Stations
    Table B - Songs
    Table C - Station Plays

    Right now I'm procedurally walking through the steps. PS I

    PHP Code:
    // LOOP THROUGH MY STATIONS
    $sql mysql_query("SELECT * FROM stations") or die(mysql_error());
        while(
    $row=mysql_fetch_array($sql)) {
            
            
    // LOOP THROUGH EACH OF THE SONGS TO ENSURE THERE IS AN ENTRY
            
    $sql2 mysql_query("SELECT * FROM songs") or die(mysql_error());
            while(
    $row2=mysql_fetch_array($sql2)) {
                
                
    $sql3 mysql_query("SELECT * FROM tracking_entry WHERE song_id = '".mysql_real_escape_string($row2['id'])."' AND station_id = '".mysql_real_escape_string($row['id'])."' AND chart_week = '".mysql_real_escape_string($currentWeek)."' AND week_string = '".mysql_real_escape_string($week_string)."' LIMIT 1") or die(mysql_error());
                if(
    mysql_num_rows($sql3) != 1) {
                    
                    
    // CREATE AN ENTRY
                    
    mysql_query("INSERT INTO tracking_entry (id, song_id, station_id, chart_week, activity_log, chart_year, week_string, notes, notes_updated) VALUES ('', '".mysql_real_escape_string($row2['id'])."', '".mysql_real_escape_string($row['id'])."', '".mysql_real_escape_string($currentWeek)."', '".mysql_real_escape_string("Added Record On $date\n")."', '".mysql_real_escape_string(date('Y'))."', '".mysql_real_escape_string($week_string)."', '".mysql_real_escape_string($row3['notes'])."', '".mysql_real_escape_string($row3['notes_updated'])."')") or die(mysql_error());
                    
                }
                
            }
            
        } 
    This script is run nightly because it is currently taking about 25 minutes to complete and pretty much bogs down the server. I know there's a better way. One issue is that when I create a new record I have to bring in some data from the last weeks record.

    This may be a really obscure cry for help but perhaps someone can help.

    PS: There are about 14,000 records being produced.

    Thanks
    Last edited by hifigrafix; Dec 4, 2012 at 08:50. Reason: PS


Bookmarks

Posting Permissions

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