22 Replies Latest reply on Oct 12, 2010 11:19 AM by Adam Cameron.

    Urgent - Please advice...

    cf2010user

      Hi,

       

      We got a million records which we need to upload somewhere - excel, PDF, CSV as part of a report. We tried excel, PDF, CSV - nothing is pulling out and taking lot of memory.

       

      Please advice, what is the best solution to upload a million records?

       

      Thx,

       

      Eric

        • 1. Re: Urgent - Please advice...
          Joshua Cyr Level 3

          You already have replies in the other forum post you made.  Basically use a database tool to extract the data.  LOTS of options exist that can do a nice job.  I have you one specific tool in the other post that will work just fine.

          • 2. Re: Urgent - Please advice...
            Joshua Cyr Level 3

            Actually this may be just a similar post.  So appologies if it is.

             

            The tool I mentioned is EMS Sql manager, though aqua data studio and others work just as well.  Basically those tools are made for tasks like this.  You can have it export the data as sql file, then just run that file on the new db.  Or export the raw data if that makes more sense for your needs.

             

            This is for MSSQL though they have tools for other db's as well.

            http://www.sqlmanager.net/en/products/studio/mssql

            • 3. Re: Urgent - Please advice...
              cf2010user Level 1

              I am looking for something to handle the million records upload with Coldfusion. User need to select certain criteria from the screen to pull the report....

              • 4. Re: Urgent - Please advice...
                Joshua Cyr Level 3

                Can you describe your need in a bit more detail.  What steps exactly?  It sounds like a user in some admin setting is running a report based on various filters, which then causes you to need to download or upload a large amount of data.  Are you creating a report out of this data as a flat file they can then download?  The more detail the better.

                • 5. Re: Urgent - Please advice...
                  cf2010user Level 1

                  Yes, you are right. Admin need to pull this big report based on filters on the screen....User is looking for output in excel...but it just hangs with million records...we tried on pdf and csv as well...

                  • 6. Re: Urgent - Please advice...
                    Joshua Cyr Level 3

                    You can use cffile tag to write one line at a time to a file.  Is that what is hanging, or are you able to dump it, but the user can't open as it is too big?  I don't think excel can open a file that large.

                    • 7. Re: Urgent - Please advice...
                      ilssac Level 5

                      There is always a limit to how much data a server can build, the network can send and a browser and client computer can receive.  You have some control over the server side, you can configure ColdFusion and|or the web server to use larger amounts of memory to allow a larger limit, but there will always be limits.  And the client side, which you would have little to no control over,will almost always have more extreme limits then most servers.

                       

                      For example, Excel will never open a file with a million records in it, or it will not show them if it does open.  Excel has a hard limit at 65536 rows, IIRC.

                       

                      You really need to push back with this client and temper their expectation.  What does a user expect to do with a million record data file?  Nobody is going to read it, I hope they aren't printing it, what are they doing with it.

                       

                      With that information you can make more intelligent decisions about what type of web application will meet those needs, or whether the needs can even be met by a web application.

                       

                      But there is one piece of advice I can give for dealing with such a large, but relatively simple, text data file.  Compress it.  Since version 8, ColdFusion has the <cfzip...> tag that would compress a large file to make it, hopefully, more transportable over the "internets".  But a million records, especially records more complex then a few characters, is still a lot of data to handle.

                      • 8. Re: Urgent - Please advice...
                        Adam Cameron. Level 5

                        What does a user expect to do with a million record data file?  Nobody is going to read it, I hope they aren't printing it, what are they doing with it?

                         

                        This is the most sensibly direct question anyone's asked on this thread yet.

                         

                        What good is 1000000 rows of data to anyone, if it's not in a DB and queriable?

                         

                        I think the intent of the requirement here is either poorly defined, or poorly thought through.

                         

                        --

                        Adam

                        • 9. Re: Urgent - Please advice...
                          ilssac Level 5

                          Adam Cameron. wrote:

                          What good is 1000000 rows of data to anyone, if it's not in a DB and queriable?

                           

                          I feel that is the most likely truth behind this.

                           

                          This requirement is some type of data transportation to get information from database A to database B.

                           

                          But I'm unwilling to start discussing all the ins and outs and various concepts to create a better mechanism for that then a web page dump of a million records without more confirmation of the true requirement(s).

                          • 10. Re: Urgent - Please advice...
                            BKBK Adobe Community Professional & MVP

                            We got a million records ...

                            Please advice

                            Do what your immune system does when confronted with a million times more germs than its antibodies.  Three hundred Spartans also did it against a million enemies at the Battle of Thermopylae, giving the world democracy today. Divide and conquer!

                             

                            Split the million records into smaller jobs of, say, 5000. Then upload piecewise.

                            • 11. Re: Urgent - Please advice...
                              Owain North Level 4

                              OR just ignore everyone's advice, sign up as a third user and ask the same question again until someone comes out with the magic answer you want to hear?

                               

                              +1 to all of the above - you're taking your data from a usable format to a useless stream of bytes. Unless you can give us a valid reason for this you're going to struggle to get the right answer.

                              • 12. Re: Urgent - Please advice...
                                cf2010user Level 1

                                All,

                                 

                                Thanks for all your suggestion...

                                 

                                This is one time Company report where they want to keep in their server. we have this report by branches in Excel sheet, which works for them. we need a report for the whole company (say for eg, ATT). We want report of all the employees of multiple branches of ATT.  What is your best advice to give me a report for whole company employee report ?

                                 

                                Thanks,

                                 

                                Eric

                                • 13. Re: Urgent - Please advice...
                                  ilssac Level 5

                                  cf2010user wrote:

                                   

                                  What is your best advice to give me a report for whole company employee report ?

                                   

                                  First piece of advice "Whole Company Employee Report" is a very broad, rather worthless requirement.

                                   

                                  What does this report look like? What data goes into it?  How is it organized?  I can not imagine any report consisting of a million rows of data!  That is a report that will never be read by any human.  I suspect this report is some type summation.  But only you and|or your clients can define what it really is.

                                   

                                  Now maybe you want a report that collates all these individual branches into a single 'book'.  That might be pretty easy to do, you could just output each chapter (aka branch) separately and then stitch them together into a single report.

                                   

                                  But I am guessing wildly at what you are really trying to do here.

                                  • 14. Re: Urgent - Please advice...
                                    cf2010user Level 1

                                    They are using excel filter option to select the branch as well as department.

                                     

                                    it will show each employee name, salary, year joined.

                                    • 15. Re: Urgent - Please advice...
                                      Adam Cameron. Level 5

                                      cf2010user wrote:

                                       

                                      They are using excel filter option to select the branch as well as department.

                                       

                                      it will show each employee name, salary, year joined.

                                       

                                      When you say "dept" and "branch", do departments span multiple branches?  Or are they the departments within a branch, eg: the IT Dept in the London branch is a different dept from the IT Dept in the Auckland branch?

                                       

                                      It sounds to me like having one monolithic report is not really what they want.  Will they ever be looking at ALL of it, with no filters, no summaries, no groupings, but to look at EVERY ROW?  No.  Impossible.

                                       

                                      So what you actually seem to need to do is to find out what sub reports they actually want, and generate as many of those as they need.  And if you need higher level aggregation reports... generate those as well.

                                       

                                      Having all the data available to report on is different from reporting all the data.

                                       

                                      --
                                      Adam

                                      • 16. Re: Urgent - Please advice...
                                        ilssac Level 5

                                        Adam Cameron. wrote:

                                         

                                        It sounds to me like having one monolithic report is not really what they want.  Will they ever be looking at ALL of it, with no filters, no summaries, no groupings, but to look at EVERY ROW?  No.  Impossible.

                                         

                                        Or maybe the users want all the data, so the users can make their own ad hoc reports from it, but the only tool the users understand is Excel. That is a whole diferent problem.

                                        • 17. Re: Urgent - Please advice...
                                          Adam Cameron. Level 5

                                          Yep, fine.  But there's no point the users wanting something that is not possible.  They can want it all they like: they still won't be able to have it.

                                           

                                          And if they are asking for that - the fact it's not possible aside - it's not really what they should be asking for, because it's attempting to use a desktop application - Excel - to do the work of a relational database.

                                           

                                          But it's fair enough for the users to say "we want [this sort of thing]", and perhaps misunderstand how such a thing might work, and say "export it all to Excel".  But what doesn't happen after that is their IT dude to say "yeah, OK, I'll do that, because that's what you asked for".  It's for them to say "we can arrive at your desired result, but we'll need to implement it in a different way because I'm afraid we can't simply export all the data and give it to you because it's too much data.  That's why it's in the DB, not an Excel file, in the first place".

                                           

                                          So what one does then is to build some sort of reporting tool, which hooks into the DB and allows the user to select a report, or have some sort of QBE arrangement.  One doesn't need to export the data for that.  One just needs to build a front end onto it.  Kinda like in any CF application, really...

                                           

                                          --

                                          Adam

                                          • 18. Re: Urgent - Please advice...
                                            ilssac Level 5

                                            Adam Cameron. wrote:

                                             

                                            A lot of good points....    

                                             

                                            That are exactly what I was trying to lead the Original Poster to realize on his/her own.   :-)

                                            • 19. Re: Urgent - Please advice...
                                              Joshua Cyr Level 3

                                              Sounds like you should be doing something like an access db (or some equivalent, based on your tools available)  report, with linked tables to the real database.  So that the data only resides in one place, and the db tool is the one actually doing the filtering and downloading the report data your users require. 

                                              • 20. Re: Urgent - Please advice...
                                                Steve Sommers Level 4

                                                For this much data, I would look at two options:

                                                 

                                                1. Work with the client to determine exactly what they are looking for in the report. I have to imagine that they want some sort of analysis or totals because a human readable report with a million lines of detail is useless.
                                                2. Use the native capablilities in the database platform you are using to create a database backup and allow the client to download the backup.

                                                 

                                                Any other solution that I can think of at the moment would most likely eat up you web server, CF and SQL server CPU and network bandwidth. Good luck.

                                                • 21. Re: Urgent - Please advice...
                                                  ilssac Level 5

                                                  That might be one possible solution, IF, and this is a big IF, our guesses are somewhere near the actual requirements of this project.

                                                   

                                                  The main message most of the previous replies have been "The requirements are vague and hard to comprehend" and "The vague requirements, as currently expressed, are probably requesting unreasonable and|or impossible applications"

                                                  • 22. Re: Urgent - Please advice...
                                                    Adam Cameron. Level 5

                                                    The main message most of the previous replies have been "The requirements are vague and hard to comprehend" and "The vague requirements, as currently expressed, are probably requesting unreasonable and|or impossible applications"

                                                     

                                                    What are you trying to say, Ian?

                                                     

                                                    ;-)

                                                     

                                                    --

                                                    Adam