Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    Slow RecordSet Retrieval -- Likely Result of Poorly Written Query

    Hi All,



    Thank you in advance for your assistance. The attached query runs, but retrieval into Excel via QueryTables.Add is very slow. I am not sure if it is a problem with the query or the retrieval method or both. About 4,800 records are returned from this query, but it takes 7+ min to import into Excel.

    Any help would very much be appreciated!

    Code:
        Set db = workspaces(0).OpenDatabase("C:\FCI.mdb")
        
        'With ActiveSheet.QueryTables.Add(Connection:= _
            "FINDER;C:\FCI_Detail_Query_v4.dqy", _
            Destination:=Range("A1"))
        '    .Name = "Test"
        '    .FieldNames = True
        '    .RowNumbers = False
        '    .FillAdjacentFormulas = False
        '    .PreserveFormatting = True
        '    .RefreshOnFileOpen = False
        '    .BackgroundQuery = False
        '    .RefreshStyle = xlInsertDeleteCells
        '    .SavePassword = True
        '    .SaveData = True
        '    .AdjustColumnWidth = True
        '    .RefreshPeriod = 0
        '    .PreserveColumnInfo = True
        '    .Refresh BackgroundQuery:=False
        'End With
    FCI_Detail_Query.txt

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not much to go on - I use copyfromrecordset which takes about 10 secs to load 15k rows of around 20 columns from a recordset created from query in Access

    with regards your query being slow - how long does it take to open and be fully populated in Access (i.e. the recordcount at the bottom of the form is populated?

    this is air code but to use copyfromrecordset would be something like this
    Code:
    
    dim db as dao.database 'requires installing of Access objects in library
    dim rs as dao.recordset
    dim i as integer
    
    set db=application.opendatabase("path to db")
    set rs=db.openrecordset("SELECT * FROM myQuery")
    
    For i = 0 to rs.Fields.Count - 1 ws.Cells(1, i + 1).Value = rs.Fields(i).Name 'copy header Next ws.Range("A2").CopyFromRecordset rs 'copy data set rs=nothing db.close set db=nothing

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is the PC fast normally?
    pc cpu speed,
    connection speed,
    Memory,
    etc.

  4. #4
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by ranman256 View Post
    Is the PC fast normally?
    pc cpu speed,
    connection speed,
    Memory,
    etc.
    ranman256,

    The computer that I am using for testing isn't going to be the final production computer, but it is moderately fast -- 4th gen i5, 8GB ram.

    Thanks for your response.

  5. #5
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by Ajax View Post
    not much to go on - I use copyfromrecordset which takes about 10 secs to load 15k rows of around 20 columns from a recordset created from query in Access

    with regards your query being slow - how long does it take to open and be fully populated in Access (i.e. the recordcount at the bottom of the form is populated?

    this is air code but to use copyfromrecordset would be something like this
    Code:
    
    dim db as dao.database 'requires installing of Access objects in library
    dim rs as dao.recordset
    dim i as integer
    
    set db=application.opendatabase("path to db")
    set rs=db.openrecordset("SELECT * FROM myQuery")
    
    For i = 0 to rs.Fields.Count - 1 ws.Cells(1, i + 1).Value = rs.Fields(i).Name 'copy header Next ws.Range("A2").CopyFromRecordset rs 'copy data set rs=nothing db.close set db=nothing
    Ajax,

    Thanks for the response. 10 sec for 15k rows would be incredible, if I got that kind of performance. I am not sure if that is possible with the complexity of this query (attached in the original post), but I would be happy with 2 minutes.

    When I run the query in Access, I believe it returns a snapshot -- and that is relatively quick ~5 sec., but when I click >| to show the last record, it took over so long, that at 18 min, I killed the query. That seems odd, because that is about 3x longer than it takes to retrieve the data in Excel.

    Thank you for your time

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sorry, didn't see the attachment but from the sounds of it, that is where your issue is.

    I believe it returns a snapshot -- and that is relatively quick ~5 sec., but when I click >| to show the last record, it took over so long, that at 18 min

    Not sure what you mean by opening as snapshot. the 5sec is just to display the first screenful of records, it has not run its course until the record count is populated at the bottom. using the >| probably slows it down - I would have expected 7 mins if that is how long it takes to populate excel.

    So you main issue on time is with the query. I regret to say it is not designed with efficiency in mind.

    your subqueries to determine service will slow things up - just join the table. Similarly you are using subqueries to sum charges, put them in a separate query. Something like

    Code:
    SELECT xxx.tracking_number, SUM(xxx.net_amount) 
            FROM   shipment_details AS xxx 
            WHERE xxx.charge_classification_code IN ( 'ACC', 'MSC' ) 
                   AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )
            GROUP BY xxx.tracking_number
    and join on tracking number

    You are also using a cartesian query which will be slower - move the criteria to joins where you can, perhaps using left joins since you have an OR requirement.

    And I presume all fields used for joins or criteria are indexed?

    Done properly, I would think you should be able to reduce the time by around 70-80% if not more.

  7. #7
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Quote Originally Posted by Ajax View Post
    sorry, didn't see the attachment but from the sounds of it, that is where your issue is.

    I believe it returns a snapshot -- and that is relatively quick ~5 sec., but when I click >| to show the last record, it took over so long, that at 18 min

    Not sure what you mean by opening as snapshot. the 5sec is just to display the first screenful of records, it has not run its course until the record count is populated at the bottom. using the >| probably slows it down - I would have expected 7 mins if that is how long it takes to populate excel.

    So you main issue on time is with the query. I regret to say it is not designed with efficiency in mind.

    your subqueries to determine service will slow things up - just join the table. Similarly you are using subqueries to sum charges, put them in a separate query. Something like

    Code:
    SELECT xxx.tracking_number, SUM(xxx.net_amount) 
            FROM   shipment_details AS xxx 
            WHERE xxx.charge_classification_code IN ( 'ACC', 'MSC' ) 
                   AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )
            GROUP BY xxx.tracking_number
    and join on tracking number

    You are also using a cartesian query which will be slower - move the criteria to joins where you can, perhaps using left joins since you have an OR requirement.

    And I presume all fields used for joins or criteria are indexed?

    Done properly, I would think you should be able to reduce the time by around 70-80% if not more.
    Thank you Ajax for the response.

    To answer your question about indexing, the tables are not indexed. I am unsure how to do that. Similarly I am not sure how to do the joins, but I will be researching all of this. 70-80% would be incredible.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You index tables in design view by editing the field properties or by using the Indexes button on the table ribbon.
    I've done a number of speed tests of various different conditions, one of which was the effect of indexing on search and sort fields.
    Typically indexing made searches up to 250 times faster.
    The effect is most noticeable on very large recordset and complex queries

    However indexing increases file size and not all fields should be indexed.
    Also action queries will be much slower as the indexes need to be updated as well as the fields themselves.

    For more info, see http://www.mendipdatasystems.co.uk/s...sts/4594424200.
    Tests 1 & 8 may be particularly relevant to you.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I am not sure how to do the joins
    simple, open your query in design view, mouse down on one field in the join, then drag across to the other field of the join and release. double click on the join line to see the join options. Once done, I would expect your sql to look something like

    Code:
    SELECT
    …
    ...
    FROM  (shipment_details AS sd1 INNER JOIN  carrier_client_accounts AS cca ON cca.account_number  = [sd1].[account_number]) INNER JOIN carriers AS ca ON cca.carrier_code = [ca].[carrier_code]
    
    WHERE  sd1.charge_classification_code  IN ( 'MSC', 'FRT' ) OR sd1.tracking_number IS NULL
    and of course you need to add those other aggregate queries in plus indexing

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    further on the aggregate queries, you can combine some - for example

    (SELECT SUM(xxx.net_amount) + SUM(incentive_amount)
    FROM shipment_details AS xxx
    WHERE xxx.tracking_number = sd1.tracking_number
    AND xxx.charge_classification_code = 'FRT'
    AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )) AS Freight,

    (SELECT SUM(xxx.net_amount) + SUM(incentive_amount)
    FROM shipment_details AS xxx
    WHERE xxx.tracking_number = sd1.tracking_number
    AND xxx.charge_classification_code = 'FSC'
    AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )) AS Fuel,
    can be combined as

    Code:
    SELECT tracking_number, SUM(iif(charge_classification_code = 'FRT',net_amount+incentive_amount)) as Freight, SUM(iif(charge_classification_code = 'FSC',net_amount+incentive_amount)) as Fuel
            FROM   shipment_details AS xxx 
            WHERE charge_category_code NOT IN ( 'ADJ', 'MIS' )
            GROUP BY tracking_number
    I haven't looked in detail at your query so perhaps there are others

  11. #11
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Ajax,

    I am still struggling with these join statements.

    Using one of the sub-selects (from my original query) as an example:

    Code:
    (SELECT SUM(xxx.net_amount) + SUM(incentive_amount) 
            FROM   shipment_details AS xxx 
            WHERE  xxx.tracking_number = sd1.tracking_number 
                   AND xxx.charge_classification_code = 'FRT' 
                   AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )) AS Freight,
    Would the alternative Left Inner Join look like this?


    Code:
    SELECT Freight FROM
    (SELECT xxx.tracking_number, SUM(xxx.net_amount) + SUM(incentive_amount) as Freight
            FROM   shipment_details AS xxx 
                   WHERE xxx.charge_classification_code = 'FRT' 
                   AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )
            GROUP BY xxx.tracking_number) as Freight_Sub
    LEFT JOIN Freight_Sub.Tracking_Number = sd1.Tracking_Number,

    Then use the same concept for Fuel......duty..tax, etc

    Code:
    SELECT Fuel FROM
    (SELECT xxx.tracking_number, SUM(xxx.net_amount) + SUM(incentive_amount) as Fuel
            FROM   shipment_details AS xxx 
                   WHERE xxx.charge_classification_code = 'FSC' 
                   AND xxx.charge_category_code NOT IN ( 'ADJ', 'MIS' )
            GROUP BY xxx.tracking_number) as Fuel_Sub
    LEFT JOIN Fuel_Sub.Tracking_Number = sd1.Tracking_Number,
    You mentioned combining, but I am trying to understand one step at a time.

    Thank you for your help.

  12. #12
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Hold up! I think I'm making some progress

    I have this piece down --- all charge_category_codes NOT IN ('ADJ','MIS')

    Code:
    SELECTtracking_number,
    Sum(Iif(charge_classification_code='FRT',net_amount+incentive_amount
    ))AS
    Freight,
    Sum(Iif(charge_classification_code='FSC',net_amount+incentive_amount
    ))AS
    Fuel,
    Sum(Iif(charge_classification_code='FRT',incentive_amount*-1))
    ASDiscount,
    Sum(Iif(charge_classification_code='FSC',incentive_amount*-1))
    AS
    Fuel_Discount,
    Sum(Iif(charge_classification_code='TAX',net_amount))
    ASTax,
    Sum(Iif(charge_classification_codeIN('GOV','BRK'),net_amount))
    ASDuty,
    Sum(Iif(charge_classification_codeIN('ACC','MSC'),net_amount))
    AS
    Miscellaneous
    FROMshipment_detailsASxxx
    WHEREcharge_category_codeNOTIN('ADJ','MIS')
    GROUPBYtracking_number
    Now it's just the joins....

  13. #13
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    I have made it this far

    Code:
    SELECT sd1.tracking_number, charges.freight as Freight, charges.Fuel as Fuel, charges.Discount as Discount, charges.Fuel_Discount as Fuel_Discount, charges.Tax as Tax, charges.Duty as Duty, charges.Miscellaneous as Miscellaneous
    FROM shipment_details AS sd1 
    LEFT JOIN (SELECT tracking_number, 
                             Sum(Iif(charge_classification_code = 'FRT', 
                                 net_amount + incentive_amount)) AS 
                             Freight, 
                             Sum(Iif(charge_classification_code = 'FSC', 
                                 net_amount + incentive_amount)) AS 
                             Fuel, 
                             Sum(Iif(charge_classification_code = 'FRT', 
                                 incentive_amount * -1)) 
                                                                      AS Discount, 
                             Sum(Iif(charge_classification_code = 'FSC', 
                                 incentive_amount * -1)) 
                                                                      AS 
                             Fuel_Discount, 
                             Sum(Iif(charge_classification_code = 'TAX', net_amount) 
                             ) 
                                                                      AS Tax, 
                             Sum(Iif(charge_classification_code IN ( 'GOV', 'BRK' ), 
                                 net_amount)) 
                                                                      AS Duty, 
                             Sum(Iif(charge_classification_code IN ( 'ACC', 'MSC' ), 
                                 net_amount)) 
                                                                      AS 
                             Miscellaneous 
                      FROM   shipment_details AS xxx 
                      WHERE  charge_category_code NOT IN ( 'ADJ', 'MIS' ) 
                      GROUP  BY tracking_number)  AS charges ON sd1.tracking_number = charges.tracking_number
    but I am not sure how to add in an additional join. I tried the query below, but I get the following error: Syntax error in FROM clause

    Code:
    SELECT sd1.tracking_number, charges.freight as Freight, charges.Fuel as Fuel, charges.Discount as Discount, charges.Fuel_Discount as Fuel_Discount, charges.Tax as Tax, charges.Duty as Duty, charges.Miscellaneous as Miscellaneous, adj.*
    FROM shipment_details AS sd1 
    LEFT JOIN (SELECT tracking_number, 
                             Sum(Iif(charge_classification_code = 'FRT', 
                                 net_amount + incentive_amount)) AS 
                             Freight, 
                             Sum(Iif(charge_classification_code = 'FSC', 
                                 net_amount + incentive_amount)) AS 
                             Fuel, 
                             Sum(Iif(charge_classification_code = 'FRT', 
                                 incentive_amount * -1)) 
                                                                      AS Discount, 
                             Sum(Iif(charge_classification_code = 'FSC', 
                                 incentive_amount * -1)) 
                                                                      AS 
                             Fuel_Discount, 
                             Sum(Iif(charge_classification_code = 'TAX', net_amount) 
                             ) 
                                                                      AS Tax, 
                             Sum(Iif(charge_classification_code IN ( 'GOV', 'BRK' ), 
                                 net_amount)) 
                                                                      AS Duty, 
                             Sum(Iif(charge_classification_code IN ( 'ACC', 'MSC' ), 
                                 net_amount)) 
                                                                      AS 
                             Miscellaneous 
                      FROM   shipment_details AS xxx 
                      WHERE  charge_category_code NOT IN ( 'ADJ', 'MIS' ) 
                      GROUP  BY tracking_number)  AS charges ON sd1.tracking_number = charges.tracking_number,
    
    
    LEFT JOIN (SELECT tracking_number, 
                             Sum(net_amount) AS Adjustments 
                      FROM   shipment_details AS xxx 
                      WHERE  charge_category_code IN ( 'ADJ', 'MIS' ) 
                      GROUP  BY tracking_number)  AS adj ON sd1.tracking_number = adj.tracking_number
    Thanks for any assistance

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    there is no need for a left join to your aggregate queries since tracking_number will be in both because they come from the same source

    Suggest you use the query builder to create your sql - you are missing brackets in the FROM part - the brackets are used something like this

    FROM ((tbl1 INNER JOIN tbl2 ON ……) INNER JOIN tb3 ON …..) INNER JOIN tbl4 ON....

    And don't forget the indexing

    Only other thing to be aware of is nulls

    where your are summing net_amount + incentive_amount, if either are null, the calc will return null. If there is any chance this could be the case then use the nz function around each value - nz(net_amount,0) + nz(incentive_amount,0)

  15. #15
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Thank you very much Ajax! I have the query performing a billion times faster. I really appreciate you taking the time to spell things out.

    For indexing, I have a question. How do I know which Fields to Index. I would assume it would be Account_Number, Tracking_Number, but want to make sure.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Market Data Retrieval
    By Grefcon901 in forum Access
    Replies: 16
    Last Post: 04-07-2016, 01:59 PM
  2. Replies: 2
    Last Post: 08-11-2015, 01:47 AM
  3. feedback on query written
    By Compufreak in forum Access
    Replies: 1
    Last Post: 08-29-2012, 06:16 PM
  4. Replies: 7
    Last Post: 06-16-2010, 09:19 AM
  5. Replies: 2
    Last Post: 10-01-2009, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums