Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2021
    Location
    Shrewsbury
    Posts
    4

    ODBC Log Analysis

    Hi All,



    been trying to find out if someone has asked this before, and googling whereabouts i can find out what these numbers mean.

    I have an access front end, with linked tables to an sql server on azure.

    I turned on the logging as at times the database seems incredibly slow.

    But I don't know what these numbers mean!ik sql stats log.zip - i have found out what they 'mean' but not whether i should be worried about the size of some of the numbers.

    Does anyone know anywhere I can do to find out please?

    Many thanks

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    There are reports in Azure for various query consumption stats (the below is from SSMS)
    Click image for larger version. 

Name:	Azure_QueryStore.png 
Views:	12 
Size:	10.7 KB 
ID:	46000
    These will show you "expensive queries" and suggest index improvements if necessary.
    The log file doesn't mean much without some context unfortunately.

    Are there specific forms or actions that the database slows down on?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Aug 2021
    Location
    Shrewsbury
    Posts
    4
    wow thanks Minty, never seen these before as i don't have much to do with the sql server. will take a while to digest all that... but are these reports related to the use of our databases and the sql server?

    it is a bit random about when it slows down, and of course made worse with people working from home, in rural areas, with slower broadband!

    thanks for your steer.

    Ian

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Slow connections will hurt performance.
    Make sure that all you editing forms only load either a single record or empty.
    If you have a list of data (continuous form or datasheet) that you use to select a record to edit from, use a SQL View to populate it so that any joins etc. are done on the server.
    This will avoid using combo's etc to display lookup values, and dragging all that data across the network.

    This will allow you to also load the form as a snapshot rather than a dynaset, which will be quicker.
    If you make updates in the editing form that need to be displayed, refresh the data when you close the editing form.

    There is a lot you can do to mitigate moving the data around, and improve performance.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Aug 2021
    Location
    Shrewsbury
    Posts
    4
    Thanks again Minty, good advice there.

    Those logs on the sql server do seem to be the ones used by the website, which is driven by the server rather than my access database, as far as i can see anyway - although there are very pretty graph, I have no idea what they mean.

    I am still at a loss as to what my original log means, so if anyone does know, do let me know.

    ian

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    95% of the Queries in your Access database will be passed via ODBC to the SQL server to carry out.
    SQL server will have an execution plan for those queries. You will see the worst performing as the highest values in the graphs that are presented.

    So below is an example where it is showing me a access query (that is actually based a view) and suggesting I add an index to it, to improve the performance.
    Click image for larger version. 

Name:	Azure_QueryPlan_Top_Waits.png 
Views:	11 
Size:	118.6 KB 
ID:	46002
    The little Purple dot on the RH side is the plan execution number and time of running.
    The green text is the recommended index creation.
    The Black text is the query that is being run.
    The Histogram type thing is a visual representation of the plan execution. It's all very clever.

    You don't need to understand all of it, but a bit of googling and digging will reap serious rewards with these tools.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Join Date
    Aug 2021
    Location
    Shrewsbury
    Posts
    4
    You're a star Minty, that is enough info to get me started, and i'll have a good look tomorrow, and do some googling as suggest.

    Thanks so much for taking the time and trouble.

    Ian

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are welcome.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ODBC Data Source (ODBC or SQl Native Client)
    By DLTaylor in forum SQL Server
    Replies: 4
    Last Post: 11-07-2018, 06:36 AM
  2. Replies: 3
    Last Post: 04-25-2018, 10:32 AM
  3. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  4. Replies: 5
    Last Post: 10-25-2011, 08:01 AM
  5. ODBC -- call failed. | Oracle ODBC
    By drdexter33 in forum Access
    Replies: 1
    Last Post: 04-03-2010, 09:32 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