Jump to content

creating a new table to avoid timeout


manhattes

Recommended Posts

Seems your issue is a timeout though. If that's true are trying to do too much work..or have to increase memory or timeout limits on the server, that's if the server will not puke doing the work.

 

That's why I suggested a different approach and baby steps, query just the data you need with limited results and store it, then do the next set.

 

Do you have proper indexing on the columns? , that could speed it up.

Link to comment
Share on other sites

to fix your debatable design, you don't need to run the query that doesn't work/times out.

 

you need to select all the rows from the badly designed table, that has the multiple string values stored in the single column, split up those values, get or assign an auto-increment integer id key for each unique string value, then insert row(s), one for each existing row and each original split values, into a new table, with the id corresponding to the string and the id of whatever you are relating that information too.

 

the resulting table should only have integer id values in it.

 

for example, if you have a row in your existing table with only one of these string values stored in the offending column, you would end up inserting one row in the new table. if you have a row in your existing table with three of these string values stored in the single column, you would end up inserting three rows in the new table.

 

once you get finished with this deconstruction/reconstruction process, you would use this new table in JOINed queries to relate, using exact value, integer, matches, the source data in the parent tables. then, by using correct indexes on the tables, you can easily and quickly query tables that contain several millions of rows.

Edited by mac_gyver
Link to comment
Share on other sites

I dont know what data i need though, I just know where it might be. I am extracting data from a source that is raw/uncleaned and not meant for this purpose. 

I can re-upload the data  and create an index but I would still need to use the like command or maybe preg_match to compare the two tables and create a table that is by company name with the calendar data.

 

This is what I want it to look like give or take a few fields from the calendar:

AAPL - Apple Inc - Product - Date Release

AAPL - Apple Inc - Product 2- Date Release

AAPL - Apple Inc - Product 3- Date Release

 

MSFT- Microsoft - Product - DateRelease

MSFT- Microsoft - Product2 - DateRelease

MSFT- Microsoft - Product 3- DateRelease

MSFT- Microsoft - Product 4- DateRelease

 

The calendar data looks like this: The Column SName is where it says University of California

Rank NCT Number Title Recruitment Study Results Conditions Interventions SName Gender Age Groups Phases Enrollment Funded Bys Study Types Study Designs Other IDs 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00 Results First Received Acronym 0000-00-00 Outcome Measures URL 129003 NCT00820781 Endoscopic Sclerotherapy and/or Ligation Versus Po... Completed No Results Available Gastric Bleeding|Cirrhosis Procedure: Portacaval shunt|Procedure: Sclerothera... University of California, San Diego Both Child|Adult|Senior   518 Other Interventional Allocation: Randomized|Intervention Model: Paralle... ESTVPCSBGV 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00 No Study Results Posted null 0000-00-00 Survival|Control of bleeding and quality of life  
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.