Solved

insert a list of values into a mysql db

Posted on 2011-02-21
26
659 Views
Last Modified: 2012-05-11
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
Comment
Question by:jtheriau
  • 8
  • 7
  • 6
  • +3
26 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 34944277
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34944445
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
 

Author Comment

by:jtheriau
ID: 34944451
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34944459
<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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34944571
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34944588
@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
 

Author Comment

by:jtheriau
ID: 34944857
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
 

Author Comment

by:jtheriau
ID: 34944921
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34944922

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
 

Author Comment

by:jtheriau
ID: 34944939
unfortunately I have to run it every 4-8 hours to keep the realty database up to date  :(
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34944948
>> 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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34944953
Sorry.. I was slow in typing.. !!!!..
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34945002
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jtheriau
ID: 34945188
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
 
LVL 3

Accepted Solution

by:
dagaz_de earned 250 total points
ID: 34945199
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34945299
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34945392

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
 

Author Comment

by:jtheriau
ID: 34945484
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
 

Author Closing Comment

by:jtheriau
ID: 34945624
The timeout setting was what I needed in the end.  Thank you
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34945662
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34945673
I suggested extending the request timeout long before that post, why didn't you split points?
0
 
LVL 27

Expert Comment

by:yodercm
ID: 34946620
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34946654
true, yodercm suggested it in the very first post
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34948437
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
 

Author Comment

by:jtheriau
ID: 34952166
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34954010
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now