Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: Normalization process is HORRIBLY inefficient, ideas?

    Interestingly enough, I have a question similar to that found here.

    The generic concept behind the database is to track codes representing categories of work being performed at a specific point in time. Currently the database is a nightmare. No concept of keys, relation desi-wha? what's that?

    In any event, I have tables with 24 fields to represent all of these "codes". To provide flexibility for codes to change (which they already have a few years ago, making the database even more interesting), I recommended a normalized structure referring to a code table.

    I've already written the code to make the transformation, but it's painfully slow due to all of the lookups it has to perform to get the right code_id for a given category. I'm probably having a brainfart here, but I'm wondering if you guys have an idea on how to avoid performing 24 dlookups/subqueries/what-have-you for EACH record.

    Sampe data might look like this:

    Code:
    tblWhoops:
    
    11 | 12 | 13 | 24 | 69 |   ---- these are the current column headings
     2  |  0 |  0  | 5   | 3  |   ---- these are quantities of a given code
    So I take that data and squirt it into a set of tables that looks like this:

    Code:
    tblCode
    -------
    code_id    --- Internal primary key, WHAT A CONCEPT
    code        --- "meaningful" code number (see 11, 12, 13, 24, 69 above)
    
    tblMain
    ------
    main_id
    other_pertinent_stuff
    
    tblMainDetail
    ------
    main_detail_id
    main_id
    code_id      --- this is where the proverbial dung hits the air facilitator
    To do this, I have to lookup the code_id for each column in tblWhoops. Currently I'm using DLookups on every column. I hate this. I would rather do anything but this method, but I'm stuck. I'm considering pulling another recordset or creating an array to refer to, but I'm not sure if this is better performance wise. I get the feeling that searching through memory-resident data will be much quicker than searching through physical data... I'm open to ideas though.

    On the plus side, I did reduce processing time by 42 minutes (from 46 minutes) by switching from DAO to batchupdating ADO...
    Last edited by Teddy; 11-08-05 at 10:23.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not sur eif I have understood the question, but....

    if you have 'just' got 24 codes then I suppose you could do a quick and dirty user written function to return the revised code. depends if you are using JET or a server as to where you place the code.

    - assuming of course that the translation process is aone off it shouldn't be a problem - not a workaround that would work on a live system though.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I've already got the dirty code written, not so much on the quick aspect...

    to give you an idea, here's the actual code I'm currently using to lookup the appropriate code_id for a given code:

    Code:
    FormatNumeric = _
        "SELECT [" & source & "].Counter, [date], [time], [Cluster ], dlookup(""code_id"", ""tblCode"", ""code = '"" &  " & source & ".C01 & ""'"") AS C01, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C02 & ""'"") AS C02, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C03 & ""'"") AS C03, " & _
        "dlookup( ""code_id"",""tblCode"", ""code = '"" &  " & source & ".C04 & ""'"") AS C04, " & _
        "dlookup(""code_id"", ""tblCode"", ""code = '"" &  " & source & ".C05 & ""'"") AS C05, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C06 & ""'"") AS C06, " & _
        "dlookup( ""code_id"",""tblCode"", ""code = '"" &  " & source & ".C07 & ""'"") AS C07, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C08 & ""'"") AS C08, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C09 & ""'"") AS C09, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C10 & ""'"") AS C10, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C11 & ""'"") AS C11, " & _
        "dlookup(""code_id"",""tblCode"", ""code = '"" &  " & source & ".C12 & ""'"") AS C12 " & _
        "FROM " & source & ";"
    From there I iterate through each line and append however many records I need to my target table. It works... but it makes me cringe...

    Edit: This isn't something the user is going to need access to. However, it is something that I'll have to be doing on a somewhat regular basis over the life of the project.
    Last edited by Teddy; 11-08-05 at 10:59.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Teddy

    I think this is due to me flagging at the end of the day but your last post has thrown me. Have you normalised the db or not? *confused*

    Anyhoo - to avoid the DLookups why didn't you just do the below:
    Code:
     SELECT [SourceT].Counter, [date], [time], [Cluster], C01T.Code_ID AS C01ID
    FROM Source LEFT OUTER JOIN tblCode AS C01T ON Source.C01 = C01T.Code
    Obviously that's the output not the VB to contruct the string. And I stopped at C01 - obviously it would need extending for each one but... isn't that much nicer than DLookups?

    I imagine I've missed something coz I doubt you would have missed that....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure I understand how that would return the right code for each column? Doing it for one column is easy enough, 24 is a little odd.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    I'm not sure I understand how that would return the right code for each column? Doing it for one column is easy enough, 24 is a little odd.
    Why odder than 24 DLookups?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I mean I can't return a unique code_id for each column, unless I'm missing something
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah - DLookup returns first value it finds. Then?

    Code:
     
    SELECT Counter, [date], [time], [Cluster], FIRST(C01T.Code_ID) AS C01ID], FIRST(C02T.Code_ID) AS C02ID
    
    FROM (Source LEFT OUTER JOIN tblCode AS C01T ON Source.C01 = C01T.Code)
    LEFT OUTER JOIN tblCode AS C02T ON Source.C02 = C02T.Code
    GROUP BY Counter, [date], [time], [Cluster]
    I'll admit that I don't totally follow your thread and db structure however so I am probably taking you somewhere you don't really want to go.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    ahhh... I didn't think to use the aliasing like that. That might work, I'm going to try it out.



    Edit: SUCCESS!!!

    *sigh*

    My "owe-a-free-beer-to" list is getting FAR to long these days...
    Last edited by Teddy; 11-08-05 at 13:53.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ooh 24 dlookups per row - yuck - I'm amazed ithe query only takes 452 minutes to run


    what I was thinking of was a UDF if suing JET or a case if using TSQL

    eg
    function GetNewCode(oldcode as string) as string

    select case oldcode
    case blaha: getnewcode="blah1"
    case blahb: getnewcode="blah2"
    und so weiter....

    PS I've always regarded dlookups as the spawn of the devil - fine for rare usasge but as your example shows a no hoper for intensive use. The overhead of the call seems to preclude its use in queries.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I generally try to stay away from hardcoding case statements that are actually based on a dynamic table, but for this particular application that probably would have worked. In any event, pootle flump's suggestion proved remarkably fast.

    I didn't realize you could alias the same table more than once, then use that alias as a prefix in the SELECT clause. That was the super-neato-fun-trick-of-the-day®. I suspect I'll be using that one again in the near future.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    I didn't realize you could alias the same table more than once, then use that alias as a prefix in the SELECT clause. That was the super-neato-fun-trick-of-the-day®. I suspect I'll be using that one again in the near future.
    LOL - glad it was helpful.
    I think we are even on the beers for today at least - you can have mine if I can have yours
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •