SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face HELP: speed of inserting LOTS of records(10,000,000) into mysql using php

    This is the table structure in mysql:
    ---------------------------------------------------
    create table test(
    a1 char,
    a2 char,
    a3 char,
    a4 char,
    a5 char,
    a6 char,
    a7 char,
    id unsigned int primary auto_increment);
    ---------------------------------------------------

    This is the php code to insert LOTS of records into the table:
    ---------------------------------------------------
    PHP Code:
    for($a1=0;$a1<=9;$a++){
      for(
    $a2=0;$a2<=9;$a++){
        for(
    $a3=0;$a3<=9;$a++){
          for(
    $a4=0;$a4<=9;$a++){
            for(
    $a5=0;$a5<=9;$a++){
              for(
    $a6=0;$a6<=9;$a++){
                for(
    $a7=0;$a7<=9;$a++){
                  
    mysql_query("insert into test set a1=$a1,a2=$a2,a3=$a3,a4=$a4,a5=$a5,a6=$a6,a7=$a7")
                 }
               }
             }
          }
        }
      }

    ---------------------------------------------------
    this code would take a very long time to finish inserting records and IIS gives a CGI Timeout Error. I'd like to know how I could solve this problem. I'll be thankful for your reply.
    Last edited by zjcboy; May 17, 2003 at 19:23.

  2. #2
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    I don't know if this would work the same because you are using the CGI executable but try adding this line at the start of your script.
    PHP Code:
    set_time_limit(0); 

  3. #3
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have already set PHP time out limit to 0. the problem is that IIS gave out an timeout error message.help......

  4. #4
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I doubt that you will have much luck using php for this. If you can run java here is a simple class which creates a file of comma separated values which can later be used with the mysql command to load data from a file.

    Code:
    public class FileThing {
    	public static void main(String[] args) {
    		int a1, a2, a3, a4, a5, a6, a7;
    		
    		for(a1=0;a1<=9;a1++){
    		  for(a2=0;a2<=9;a2++){
    			for(a3=0;a3<=9;a3++){
    			  for(a4=0;a4<=9;a4++){
    				for(a5=0;a5<=9;a5++){
    				  for(a6=0;a6<=9;a6++){
    					for(a7=0;a7<=9;a7++){
    					  System.out.println(
    					  	a1 + ", " +
    					  	a2 + ", " +
    						a3 + ", " + 
    						a4 + ", " + 
    						a5 + ", " + 
    						a6 + ", " +
    						a7);
    					 }
    				   }
    				 }
    			  }
    			}
    		  }
    		}   
    		
    	}
    }
    Usage:

    - javac *.java
    - java FileThing >temp

    This took about 5-10 minutes on my PC and created a 200 MB large file.

  5. #5
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Chris82
    I doubt that you will have much luck using php for this. If you can run java here is a simple class which creates a file of comma separated values which can later be used with the mysql command to load data from a file.

    Code:
    public class FileThing {
    	public static void main(String[] args) {
    		int a1, a2, a3, a4, a5, a6, a7;
    		
    		for(a1=0;a1<=9;a1++){
    		  for(a2=0;a2<=9;a2++){
    			for(a3=0;a3<=9;a3++){
    			  for(a4=0;a4<=9;a4++){
    				for(a5=0;a5<=9;a5++){
    				  for(a6=0;a6<=9;a6++){
    					for(a7=0;a7<=9;a7++){
    					  System.out.println(
    					  	a1 + ", " +
    					  	a2 + ", " +
    						a3 + ", " + 
    						a4 + ", " + 
    						a5 + ", " + 
    						a6 + ", " +
    						a7);
    					 }
    				   }
    				 }
    			  }
    			}
    		  }
    		}   
    		
    	}
    }
    Usage:

    - javac *.java
    - java FileThing >temp

    This took about 5-10 minutes on my PC and created a 200 MB large file.
    I've just downloaded Java and tried. Wow! It's really cool~~~ it took 10 minutes in total instead of 2 hours using PHP~~~
    Thank you very much!!!!

  6. #6
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There is no Java on my PC. Could I use php instead?

  7. #7
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zjcboy
    There is no Java on my PC. Could I use php instead?
    You have Perl? Perl could pull it off in a jiffy.

    Quote Originally Posted by kylemaxwell.com
    Also, I hope that is sample code, cause I see no good use for inserting numbers over and over in a predictable pattern.
    I'd be inclined to agree.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  8. #8
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samsm
    You have Perl? Perl could pull it off in a jiffy.
    No Perl, either

  9. #9
    SitePoint Wizard
    Join Date
    Nov 2000
    Location
    Chico, Ca
    Posts
    1,125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zjcboy
    There is no Java on my PC. Could I use php instead?
    Why not download it?
    Java
    "Happiness doesn't find you, you find happiness" -- Unknown
    www.chuckknows.com

  10. #10
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry

    Quote Originally Posted by Chuckie
    Why not download it?
    Java
    Because I don't know how to program with Java

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Location
    San Diego
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not insert a thousand (or a hundred thousand), refresh the page with the header function, and insert another thousand. You could keep track of where you are by passing a variable through the querystring.

    Also, I hope that is sample code, cause I see no good use for inserting numbers over and over in a predictable pattern.
    Kyle Maxwell
    www.kylemaxwell.com

  12. #12
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by zjcboy
    Because I don't know how to program with Java
    Chris82 did all the programming, you could just download Java and follow his instructions.

    Anyway, just occured to me, you don't have to use the webserver, you can execute the database altering script using the php executable. That shouldn't be affected by web server timeout.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  13. #13
    SitePoint Zealot
    Join Date
    May 2003
    Location
    somewhere on the Earth
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samsm
    Chris82 did all the programming, you could just download Java and follow his instructions.

    Anyway, just occured to me, you don't have to use the webserver, you can execute the database altering script using the php executable. That shouldn't be affected by web server timeout.

    I tried the method Chris82 had offered and found that is a very fast way to insert these records. I also tried php executable. It took 2 hours to finish the job while Java took only a few minutes. Thanks for your guide!

  14. #14
    SitePoint Zealot poLka's Avatar
    Join Date
    Apr 2003
    Location
    GF
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •