Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    ? Make or Append Qry so long Vs Select Query (2003)

    I have a Select query where I am creating 22 calculated fields (and 2 other fields). When I do this as a select query, it takes about 6 - 10 seconds for the entire query set of 330K records to be returned.

    I have a 2nd query that looks at the query above, sorts on 2 fields (non defined) and does a MAX on the 22 calculated fields. This query is taking about 5 minutes to run.

    So I decided to see if I can speed things up by changing the 1st query into a Make Table or Append query & have my 2nd query do the MAX off the table instead of the query.

    When I do this, my 6 second select query turns into a 4 - 5 minute running query. (the 2nd query does run in about 15 seconds against the table)


    I don't understand why it is taking so much longer for my select query to post the data into a table via either Append or Make table. Can someone shed some light on this for me?
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ? Make or Append Qry so long Vs Select Query (2003)

    When you run a select query, the results are stored in memory.
    When you run a make-table or append query, the results are stored in a table and hence on disk, and the query also has to update the table's index(es), which are also stored on disk.
    Writing to memory is *much* faster than writing to disk, especially if it is a network disk. Your anti-virus program may also contribute to the slowness, if it monitors all file operations.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Make or Append Qry so long Vs Select Query (2003)

    Thanks for the reply. I do understand about speed differences between memory & HD or a network drive (this is all on the local machine).

    Let me ask if you have any suggestions on how I can better speed up this set of 2 queries.

    My table I am reading from has has a Material_ID & Location_ID that are key fields. The last character of the Material_ID also specified the type of material this is. I have a indexed Material field that does not have this last character. I can not use this as part of my key is this field does not exist in the data I import, but is updated from a update query after the data is imported.

    My 1st query sorts on the Material and Location ID. The 1st calculated field looks at the last character of the Material_ID and creates a stock type indicator. The rest of the calculated fields look to this Stock Type indicator & creates the values I am looking for. I am using a IIF in each of the fields such as NewPln: IIF(StkType = "N",PlanCode,null) RepPln:IIF(StkType = "R",PlanCode,null)

    So my output may look like: (320K records)

    Material...Location_ID...NewPln...RepPln...DefPln
    123...............ABC.............B1
    123...............ABC............................. ...C4
    123...............ABC............................. ....................G9

    My 2nd query looks to the 1st query, sorts on the first 2 fields and does a MAX on the rest to give me the output I am looking for: (260K Records)

    Material...Location_ID...NewPln...RepPln...DefPln
    123...............ABC...............B1............ C4............G9


    If I do both queries as a select query, the 1st runs in about 10 seconds. Query 2 using Query 1 as the data source takes about 5 minutes.

    This is where I thought if I changed Query1 to a Make Table or Append, Query 2 will be much faster (which it is, 10 seconds or so). Either way my total time is about the same.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ? Make or Append Qry so long Vs Select Query (2003)

    Would the following work? Create a series of queries

    SELECT Material, Location_ID, PlanCode AS NewPIn FROM ... WHERE StkType = "N"

    SELECT Material, Location_ID, PlanCode AS RepPIn FROM ... WHERE StkType = "R"

    etc., then create a query based on all of the above, joined on Material and Location_ID that pulls in the NewPIn, RepPIn etc. fields.

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ? Make or Append Qry so long Vs Select Query (2003)

    By doing what you suggested now has my final output taking about 2 1/2 minutes. That is much improved.

    Thanks!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

Posting Permissions

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