1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Add Selected Fields from different table using sql

Discussion in 'Software Development' started by astrella, Dec 24, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    hey guys i have a strange problem
    the program that i need to code needs to have a value from the next record in the current record.

    Pls see the attached pics to get a rough idea of what i need

    data.JPG

    required Result.JPG

    i have tried to export the current table into temp table using
    SELECT * INTO temp FROM table1

    then delete first row of the temp table
    then trying to append back
    UPDATE table1, temp SET table1.nextn = temp.num;

    what i get is just the last record value(see attached pic actualresult.jpg)

    actualresult.JPG

    What can be done is to use manual coding to read betwwen records but the db i have is HUGE and going back and forth to get next records is going to kill my app.
    pls help
     
  2. jdean

    jdean

    Joined:
    Jan 20, 2002
    Messages:
    433
    Tell us more about the problem you're trying to solve.

    From what you've said so far, you just need a column where nextn is (num+1) which is easy to do.
     
  3. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    no it is not num+1
    nextnum is just that the 1st field(num) from the next record

    the tables i have posted is just an example as actual client data cannot be posted.

    so instead of 1,2,3 in 1st column had it been something like
    1
    2
    2.5
    3.4
    7
    .
    .
    850
    900.54

    what i need as output is

    1 2
    2 2.5
    2.5 3.4
    3.4 7
    .
    .
    .
    850 900.54
    900.54 EOF


    i hope you get a more clear idea now
     
  4. jdean

    jdean

    Joined:
    Jan 20, 2002
    Messages:
    433
    Ok, you've provided a specification of the output. Now can you please tell us why you're trying to do this? Someone is going to be using this output, what are they going to be doing with it? Of course you can't post the actual client data, but perhaps you can tell us what the data means.

    Regarding the concept of "next", relational tables are not ordered. I assume you mean "next" after you apply ORDER BY NUM ASCENDING.

    Regarding "manual coding", I'd think this can be done quite efficiently with one pass over the dataset. As you iterate over the records, you hold on to two records at a time so that you can assign the NUM of the second to the NEXTN of the first. Am I missing something here?
     
  5. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    ok here it is
    i have a database created by recording different input values via an automatic weighing machine that weighs tablets very accurately and fast in the production line.

    the software i need to code is for very stringent quality control.
    the machine dll generates a data dump that I have converted in database.

    Just to give you an idea a commercial tablet punching machine produces 500-1000 tablets/min with 10% of them weighed PER MINUTE...a typical batch is of 50,000 to 200,000 tabs with 4-5 batches taken daily(on various tablet machines)

    so the amount of data is very high even for a single pass over using external tools.

    the data is in format of
    date/timestamp,wt

    now for complex calculations of tolerances etc... i need something like
    timestamp, wt , wt of next tab , difference......

    u get the point?

    so i have a HUGE database that gets generated daily and needs to be parsed daily

    currently what is being done is that i get the entire recordset then clone it then use a movenext on clone then iterate over it.
    the entire operation takes 30 mins to complete on a 2.64 GHz Dual Core!

    So can any one give a elegant solution???
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    A Query is usually considerably faster than VBA Recordsets.
    It will depend on how complex the calculations are as to how difficult it will be.
    I am surprised the code takes that long to run, I have used code to Transpose string data for a million records in about 58 minutes, perhaps it can be speeded up a bit.
     
  7. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    i have found that >72% of the processing time taken is due to the vba recordset that i have to use to get the data in that specific format the queries comparatively are lightning quick
    the other calculations that though complex can be done using queries directly but the 1st pass over the generated data is taking ages

    so back to my original question...how can this be done using sql queries.

    also would it be possible to say create a wrapper around the Analog/Digital Converter dll using c/c++ or something so that instead of creating a dump immediately it can be streamed directly then i can maby delay update immediately and add the next value into current record directly??
     
  8. jdean

    jdean

    Joined:
    Jan 20, 2002
    Messages:
    433
    Starting to get the picture but still unclear on a few things.

    Do you really need to put the data into a database? In other words, are you going to search or sort the data? If you're just doing one or two passes over the data, you might not need to use a database. Just read it the data one line at a time, perform the tolerance calculations, etc.

    If you do need a database, not sure why you'd clone a recordset. Can't you do this with just one pass over the recordset? When you process each record, do you just read it or are you performing an update?

    If you are processing 10% of 50K to 200K records, that is 5K to 20K records, and that doesn't seem like a lot of data. Even 50K to 200K records 4-5 times a day doesn't sound like a lot of data to me, and so I don't quite understand your comment that this would be slow even with one pass using external tools. Is it that the record size is very large?

    In the Unix world (among other places), people routinely use external tools/languages (such as python or perl) for processing flat data files (data dumps) on large quantities of data without a problem. Given that your usage is batch, rather than realtime, something seems odd here. Either something is very inefficient, or could it be that your calculations are computationally complex?
     
  9. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    the local laws and policies related to manufacture for this class of drugs is very strict(no i will not mention what drugs) and each batch record needs to be preserved for 5 years and should be provided to Regulators on request anytime they want it..so i cannot keep it in a text dump..it needs to be a database or i can have the dumps get processed as and when they ask but to be honest i dont think the client would like to have the govt guys wait for hour or so till past records get processed again per batch..so i save it back into a dump or db.. and i think db is a wiser choice!

    also each db after all things are said and done is of the size approx. 1.1 to 1.4 GB x 4-5 for each batch.
    they compress it then and burn it on recordable media that goes into filing dept.

    i will not go into details but calculations individually are very simple but taken as a whole it is another thing altogether!

    also i expect to shave off some time once development is fully over since all optimizations that may be applied are not implemented yet to allow for easy debugging but still i am on a constant lookout for any thing that can help me.

    to be honest i think realtime processing whould have been easier to handle in this case since the end user would have the completed file at the end of the day instantaneously and not bug me to make it go ultra fast.

    also do you honestly think that i should reccomend a migration from Vista to Unix on this client machine to help quicker execution?
     
  10. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    ah about the cloned recordset you mentioned..that is what i am using currently..one pass involves going back and forth using movenext and moveprevious and then update to write the next value into current record.

    using clone.. i use a movenext on the clone so it automatically points to next record always and then have a single pass forward with both recordset pointers moving in tandem followed by a batch update at the end
     
  11. jdean

    jdean

    Joined:
    Jan 20, 2002
    Messages:
    433
    I was not suggesting Unix, I was just pointing out a widely used and proven methodology for problems like this one. In case you're interested, the Unix tools are available under Windows. The issue would not be switching operating systems, it would be the time it would take for you to come up to speed with these tools. Obviously switching over to a different set of tools is not something done easily and would require careful evaluation.

    Regarding database vs. flat file, you should perform your calculations in whatever method is most efficient. If flat file is better, use that and then convert to database format later.

    I do find it strange that regulators would want the data in database format. Database format is subject to change; the formats and tools are usually proprietary, and the regulators might have a difficult time trying to load up a 2008 database in 2013 (not to mention the issues that might occur should a single sector on the recordable media go bad). A flat file is the best universal/archival format.

    Getting back to speeding up your application, one of the fundamental principles of computer science is that the way to gain a significant performance improvement is often not just optimization of your algorithm but to rethink the algorithm completely. The classic example is sorting. You can use a bubble sort, but when you get to millions of records, there's no way that tuning the algorithm is going to be efficient enough. The solution is a completely different type of algorithm, one that is logarithmic rather than linear, such as heapsort.

    I encourage you to rethink your problem and try to find a different way to do the same thing. There isn't anything in your description that makes this sound like a hard or expensive problem, and that means there's a better solution, a better path, than the one you're using.

    Good luck!
     
  12. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    well to be honest i am taking up this work after a previous coder who was doing this passed away in an accident and thus was not involved in the project during the design time.
    now as far as remaking logic of the entire code well the company wants it fully operational asap.
    regarding db storage i agree with you, also the fact that import of processed data in a flat file from storage would not be much slower than the db load itself but the company ppl insisted on using the db format inspite of my reservations and thus the justification in the previous post was as explained to me by them.

    so well the best i can hope for now is to convince them to hire me again to make a ver2 of the system that would be hopefully be more efficient.

    also please answer about the Dll Wrapping possibility..that would possibly allow me to interact directly with the device in real time and thus convince the company about ver2 in Real Time....lol....
     
  13. jdean

    jdean

    Joined:
    Jan 20, 2002
    Messages:
    433
    Not enough info to answer the question about the "dll wrapping" (i.e., processing the data directly rather than reading the data from flat files). Even if there wasn't a way to do this, you could still read the data from the flat file incrementally by polling the file to see if it's grown (again, Unix has an easy way to do this, it's called 'tail').

    However, it's hard to say if it would be worth the effort to do this. It's certainly more complicated, and if there's a mess-up (for whatever reason, from a bug to a crash to a power outage), it's easier to redo a batch. If the data gathering is working reliably, I wouldn't want to mess with it.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you link to the SQL Database using Access, if you can I can help you with Access SQL Queries or VBA recordsets or a Combination of the 2
    If you can provide a small sample of how the data looks I may be able to tell you the best way to process it in Access.
     
  15. astrella

    astrella Thread Starter

    Joined:
    Jun 27, 2008
    Messages:
    25
    hmmm...ya now that you say it not messing with what works reliably is good..

    the 'tail' you mention is also a nice point but to use it i will have to learn how to do it then maybe ask the company to have a standalone machine with unix ...but as you said earlier it is not worth the effort in retraining personell and myself o course;)

    What i was thinking about the Dll wrapping was that if i could possibly hook onto it, then it would so its thing undisturbed but also at the same time notify my code in real time and at the end of the day data caould be compared easily using some checksum algo to verify the final integrity of the real time data.

    Looking on the net about such hooking i found that it is possible but i want you guys opinion about it..
    eg i hook on the function that the dll uses to append to the dump..and read the data there itself while the dll also writes the file undisturbed by the hook.

    Comments?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/782757

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice