• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

insert a list of values into a mysql db

I have a comma sep  list of approx 36k values.  I am using what I think is an ineffective and unreliable method to get them into my system. (see attached code)
This code worked great last week and this week it times out at approx 2-3 k records and 30 seconds.  The SHARED host says it is my problem and I need to do it manually.

Is there a simpler more effective way to put my list into my table ?  Something along the lines of
the second code set (7976625)

thanks much and God Bless
<cfloop list="#tmpList#" index="i">
<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#i#">)			
</cfquery>

</cfloop>

Open in new window

<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#tmpList#"> list ="yes")			
</cfquery>

Open in new window

0
jtheriau
Asked:
jtheriau
  • 8
  • 7
  • 6
  • +3
1 Solution
 
Cornelia YoderArtistCommented:
you could try the php function to override the timeout limit, might work :)

set_time_limit(360);

http://us2.php.net/manual/en/function.set-time-limit.php
0
 
Brijesh ChauhanStaff IT EngineerCommented:
Your second code should work..
<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#tmpList#" list ="yes")			
</cfquery> 

Open in new window


Example ... inserting list of email values

INSERT INTO mail (email) VALUES ('email@example.foo'),('test@example.foo'),....
0
 
jtheriauAuthor Commented:
I want to add a little to this in case there is another way .. I am creating hte list from another query on another dsn,

So the actual steps are these:
1. cfquery name='qry1' dsn = 'Microsoft SQL Server DSN" - SELECT ID from tbl1
2. cfset keepers = valuelist(qry1.id)
3. loop over list and insert in to tbl2 on MySQL dsn2
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Brijesh ChauhanStaff IT EngineerCommented:
<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
         INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#tmpList#" list ="yes">)		
</cfquery>  

Open in new window

0
 
gdemariaCommented:
Yes, you should combine the two queries into a INSERT... SELECT statement.   So you select from the table and insert them directly without a loop !

<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
  INSERT INTO tabl2  (id)
  SELECT ID from tabl1
  WHERE ... any where clause on table 1 ? .....
</cfquery>

That's it !

 
0
 
gdemariaCommented:
@brij   this won't work

  INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#tmpList#" list ="yes">)      


That would translate to

 INSERT INTO tbl_keeplist (id) values (1,2,3,4,5)

 which will cause an error

0
 
jtheriauAuthor Commented:
brijeshchauhan - I tried that before posting - unfortunately it just runs and runs and never times out.  I have to stop the page loading and then there are 0 records in the table :(  
0
 
jtheriauAuthor Commented:
gdemaria - the trouble with insert select is this  - the first query is from a completely different datasource - so I would have to say something like


<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
  INSERT INTO tabl2  (id)
  <cfquery datasource=#appliucation.dsn2#">SELECT ID from tabl1</cfquery>
</cfquery>

BUT - that doesn't work
0
 
gdemariaCommented:

oh sorry, I missed the fact that they are too different data sources.   One MySQL and One SQL Server.  

Is this a one-time job or do you have to do this periodically?

If one time, you can do the loop, perhaps extend the request timeout or you can break it up into sections doing 500 records at a time.

0
 
jtheriauAuthor Commented:
unfortunately I have to run it every 4-8 hours to keep the realty database up to date  :(
0
 
Brijesh ChauhanStaff IT EngineerCommented:
>> INSERT INTO tbl_keeplist (id) values (1,2,3,4,5)

yes, that would be error, was actually looking for

INSERT INTO tbl_keeplist (id) values (1),(2),(3),(4),(5)....


@gdemaria, your solution would work, but I just have one doubt, the user is selecting from MSSQL DSN and inserting into MySQL.. would that cause some issue ?? if NOT then, let #dsn1# is MSSQL DSN..  (Just adding the identifier in there to your solution)

<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
  INSERT INTO tabl2  (id)
  SELECT ID from #dsn1#.tabl1
  WHERE ... any where clause on table 1 ? .....
</cfquery>



0
 
Brijesh ChauhanStaff IT EngineerCommented:
Sorry.. I was slow in typing.. !!!!..
0
 
gdemariaCommented:
Do you have a way to detect which records need to be included?   For example, only those changed today to perhaps only those added after the last update?   Something like that could help reduce the time and impact.

If you are doing the entire table every 4 hours, then perhaps setting up an Import routine.. a batch load from file could work quickly, if MySQL has this capability?

One more question - if you are loading the entire table, are you deleting all contents before adding new records? Or are you appending?  

0
 
jtheriauAuthor Commented:
to answer  few questions -
1. Yes I am deleting all records first
2. There is no identifier in this particular data set - simply a table full of mls numbers
3. I realyl don't know too much about batch loading from a file - but to be honest - just cflooping over the list and then individually inserting 36k rows - worked last week in milliseconds.  So I honestly think the shared server is the trouble - but they won't admit anything has changed.
4.  There is no WHERE needed in the select (just an FYI) I ma simply importing every record so I can work with it in the same dsn.

Here is the way the structure works with this connection...(all on an outside system that I only have access to via SQL views)
tbl_keepers - > List of the mls numbers that we need to work with
tbl_res -> Information on every residential residence wether it is a keeper or not
tbl_land, tbl_mlt,tbl_commercial - same as tbl_res except for other property types

Since I only want a fraction of the listings (say 2 counties worth - or 5k listings) I need to first get all the listings that fit in my towns, then I import all the keepers, then I mark the keepers in my local table - then I delete the rest and process as needed.I hope that made sense.
0
 
dagaz_deCommented:
If i have a script which runs long, i use these to prevent a timeout:

<cfsetting requesttimeout="3600" >

and i would use the query like this:

<cfsetting requesttimeout="3600" >
<cfset valuelist = "">

<!-- First Query to build list -->
<cfquery datasource=#application.dsn2#" name="qlist">
SELECT ID from tabl1
</cfquery>

<!-- CFoutput (or Loop) over qlist -->
<cfoutput query="qlist">
      <cfset valuelist = valuelist & "(#qlist.id#),">
</cfoutput>

<!-- Cleanup qlist -->
      <cfset list_len = len(valuelist)-1>
      <cfset valuelist = left(valuelist,list_len)>

<!-- Second Query with list -->
<cfquery name="insertKeepers" datasource="#application.dsn#" result="tmp">
  INSERT INTO tabl2  (id) #valuelist#
</cfquery>

<cfsetting requesttimeout="60" >
0
 
Brijesh ChauhanStaff IT EngineerCommented:
Yes, basically doing something like comment 34944948 above

INSERT INTO tbl_keeplist (id) values (1),(2),(3),(4),(5)....

would be faster, so do some preprocessing to create a list in the format  (1),(2),(3),(4),(5).... and then do the insert

This is what @dagaz_de has suggested...
0
 
gdemariaCommented:

So, you want to just add the requesttimeout as I suggested above to keep it from timing out.

I don't know mySQL that well, but I know this format won't work in SQL Server or Oracle.
  > INSERT INTO tbl_keeplist (id) values (1),(2),(3),(4),(5)....

Therefore, the simple loop should work with the longer timeout period

Note that you can collect up all values using valueList() from the first query, you don't need to do any looping to collect up the IDs into a list.

In fact, you can just loop over the values instead
<cfset requesttimeout = "60000">

<cfquery name="getData" datasource="#request.datasource#">
  select ID from table1
</cfquery>

<cfloop list="#valueList(getData.ID)#" index="i">
  <cfquery name="insertKeepers" datasource="#application.dsn#">
   INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#i#">)			
  </cfquery>
</cfif>



OR ---------------------

<cfset requesttimeout = "60000">

<cfquery name="getData" datasource="#request.datasource#">
  select ID from table1
</cfquery>

<cfloop guery="getData">
  <cfquery name="insertKeepers" datasource="#application.dsn#">
   INSERT INTO tbl_keeplist (id) values (<cfqueryparam cfsqltype="cf_sql_integer" value="#getData.ID#">)
  </cfquery>
</cfif>

Open in new window

0
 
jtheriauAuthor Commented:
So far I have tried all of the things above in one form or another to no avail.  I started with a the valuelist, then tried looping over the query itself - all timed out.

I upped the requesttimeout and got to approx 20k records before It timed out again.  So I will up it a little more and see where that gets me.  

I am now rethinking the process however.  Using a left join I can query the records I want based off the original keep list from the MSSQL tables.  So I may have saved myself some time / energy in this .  I will get back either way.

Thanks for all the help guys.  If the inner join solves my problem and ONLY pulls the ones I want - then I will close up this ticket.  Otherwise I will be back with some more questions on this same line.
0
 
jtheriauAuthor Commented:
The timeout setting was what I needed in the end.  Thank you
0
 
_agx_Commented:
individually inserting 36k rows - worked last week in milliseconds.

36K inserts is a lot of processing. If possible - I'd be looking at ways of having the 2 communicate directly. For example in MS SQL  you can set up a "linked server" which let's you communicate directly with an external server from within MS SQL.  Then you could do a direct insert like gdemaria mentioned in comment 34944571.  You'd have to find out if your host allows them. But if available, that's probably the approach I'd use.  

http://msdn.microsoft.com/en-us/library/ms188427%28v=sql.90%29.aspx
http://forums.mysql.com/read.php?60,123221,123221
0
 
gdemariaCommented:
I suggested extending the request timeout long before that post, why didn't you split points?
0
 
Cornelia YoderArtistCommented:
A LOT of people suggested setting the timeout limit, including me as the very first post in the answers.  Don't you think if that was the answer, you should share the points among those who helped??
0
 
gdemariaCommented:
true, yodercm suggested it in the very first post
0
 
Brijesh ChauhanStaff IT EngineerCommented:
I don't know mySQL that well, but I know this format won't work in SQL Server or Oracle.
  > INSERT INTO tbl_keeplist (id) values (1),(2),(3),(4),(5)....

Yes, it works correctly with MySQL..

http://dev.mysql.com/doc/refman/5.0/en/enum.html
http://www.webmasterworld.com/forum88/8824.htm
0
 
jtheriauAuthor Commented:
Wow - who knew.  Looking back at the posts a few of you did in fact mention the timeout.  My apologies to all of you.  In the end - what I saw was this line

<cfsetting requesttimeout="3600" >

it was
1. the answer
2. in coldfusion not in php
3. the exact string needed - not 'hey try upping the timeout'

I don't mind looking for information.  If someone points me on the right track - I will follow it.  But I have spent days struggling with this issue and I was looking for the answer, not a path to go down in case it was right.  

So No excuses for not giving you all credit - just saying - I was overjoyed at the EXACT answer with no more research involved and time rabbit chasing.  Please accept my apologies for messing this one up.  You all deserve the credit.
0
 
_agx_Commented:
I am now rethinking the process however

That's a good idea.  I'm glad upping the timeout works for now.  But keep in mind hosts can install s/w that automatically kills threads that take longer than X amount of time. To prevent one application from dragging down the entire server. So ultimately you may want to find a more scalable solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 8
  • 7
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now