CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Key value columns to table format

 



sam001
Novice

May 20, 2011, 7:44 AM

Post #1 of 2 (469 views)
Key value columns to table format Can't Post

Hi All,

I have a very large dataset with key value pairs

Attached is example of the data for the results with the value fields being much longer memo fields from access db

The student file would contain a unique number for each student.Attached is an example

I would like to create a results table in normalised form

SN ID1 ID2 Math English Geography Science

I have written a perl program that reads each line into a hash and then inserts the data into an access table.

However, this takes really long to run as I have over 500,000 records.

Does anyone know a better way of doing this.

Perl file attached.

Many Thanks

Sam
Attachments: example.pl (1.97 KB)
  Data formats.xlsx (9.96 KB)
  Data formats.xls (22.5 KB)


miller
User

May 23, 2011, 2:12 PM

Post #2 of 2 (431 views)
Re: [sam001] Key value columns to table format [In reply to] Can't Post

Have you bothered to do any benchmarking using Time::HiRes->gettimeofday to determine what's exactly taking up all the time. I suspect that it's probably the inserting into the new table.

If this is the case, then maybe it's an issue of a database index having to be updated for every insert. if THIS is the case too, then you can either group your insert statements into one huge operation, or you can even drop whatever index is causing the problems and recreate it after you do all of your inserts.

Either way, you need to do some profiling before you can know how to improve your queries.

Also note, it would be possible to increase your original selection queries by using a left join instead of doing an individual query for each SN. However, I actually doubt that the selecting is a significant draw on the time.

- Miller

PS,
I've attached a slightly cleaned up version of your perl code in case you need further assistance.


(This post was edited by miller on May 23, 2011, 2:13 PM)
Attachments: example.pl (1.97 KB)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives