Results 1 to 8 of 8

VBA SQL Strings causes bloat?

  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    383

    VBA SQL Strings causes bloat?

    I noticed that the Front End of my app keeps bloating from 1.8 mb to 20 mb, nothing a C&R can't fix. But I wanted to research reasons why and I came across the following OLD quote. They state essentially that each time VBA executes SQL from a coded string rather than a saved querydef that the DB grows. Any thoughts on this as it pertains to Access in 2017? My VBA is LOADED with "SQL Strings"! I *had* preferred hard coding strings vs saved queries to avoid potential of users messing with a query and breaking the app... Thoughts?



    The most common causes of db bloat are over-use of temporary tables and over-use of non-querydef SQL. Whenever you code an SQL string outside of a querydef, Access must "bind" that statement EVERY time it is run. This process takes a small amount of time and uses a large amount of workspace that is not recovered until you compact the db. Querydef's are "bound" when they are saved and when the db is compacted. Access saves its execution plan at that time and uses the saved plan at run time.

    Post #6
    https://access-programmers.co.uk/for...ad.php?t=48759

    When I get a chance I plan to run some experiments on this.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,952
    Why would anyone use SQL when queries are available?

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    383
    Quote Originally Posted by ranman256 View Post
    Why would anyone use SQL when queries are available?
    I'm confused by your question. Please elaborate.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    383
    Interestingly I've done a quick little experiment and have found THE OPPOSITE of post #1 to be true. Hard coding sql statements were both QUICKER and caused zero bloat, while utilizing QueryDef both took longer and caused lots of bloat. Can anyone explain why QueryDef is causing bloat?

    I set up a simple table with 4 columns
    ID
    Category (byte)
    Quantity (double)
    UnitPrice (currency)

    I then populated the table with 10,000 records of random data. The db was then split.

    In the front end I made a paramatized Select query to sum(qty * unitprice) and group by category. The paramter asks for a category to return the sum of the prices for one category.

    I have two vba functions, each one returns the same thing: the Sum of a price given a category. One function uses QueryDef and the paramterized query, the other function uses a string to build the sql statement on the fly. I used a loop to execute both of the functions 1000 times. The one that uses querydef bloats the front end and is slower, while the one that uses the string has zero effect on the front end's file size.

    String version (zero bloat, executed 1000 times in about 30 seconds):
    Code:
    Public Function TestOne(cat As Byte) As Currency
        Dim rs As DAO.Recordset
        Dim sql As String
        sql = "SELECT ShoppingList.Category, Sum([Quantity]*[UnitPrice]) AS Price, Count(ShoppingList.Category) AS Cnt" & _
              " FROM ShoppingList" & _
              " WHERE ShoppingList.Category=" & cat & _
              " GROUP BY ShoppingList.Category;"
        Set rs = CurrentDb.OpenRecordset(sql)
        TestOne = rs!Price
        rs.Close
        Set rs = Nothing
    End Function
    QueryDef version (417kb to 4.3mb, executed 1000 times in about 46 seconds):
    Code:
    Public Function TestTwo(cat As Byte) As Currency
        Dim qd As DAO.QueryDef
        Dim rs As DAO.Recordset
        
        Set qd = CurrentDb.QueryDefs("qrySums")
        qd.Parameters("cat") = cat
        
        Set rs = qd.OpenRecordset
        
        TestTwo = rs!Price
        
        qd.Close
        rs.Close
        
        Set qd = Nothing
        Set rs = Nothing
        
    End Function
    The referenced parameter query:
    Code:
    PARAMETERS cat Byte;
    SELECT ShoppingList.Category, Sum([Quantity]*[UnitPrice]) AS Price, Count(ShoppingList.Category) AS Cnt
    FROM ShoppingList
    WHERE (((ShoppingList.Category)=[cat]))
    GROUP BY ShoppingList.Category;

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,474
    I'd suggest retesting without any parameters if you're really curious, and here's why (see accepted answer)
    https://social.msdn.microsoft.com/Fo...orum=accessdev

    For me, the take away (aside from what affect that parameters would have) is that the time to optimize a sql (vba) query used to have more impact than it does now.
    So the answer as to why anyone would want to use them? How about because the query takes a number of search criteria of which many could be Null, and/or incorporates variable sorting operations? Or maybe just because (according to that article) it might just be plain faster in spite of what we were told in the past. I'd be curious to know how your test would perform without parametizing the queries involved.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start a sentence with, like, "so"?

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    383
    Thanks for that link.

    I've re-tested as you've suggest by removing the parameter from the query, it's now a static query with 12 in the where clause instead of [cat] and parameter definition removed. This was still slower than the string method and still bloated the front end up to 4.3 mb.

    Note that the link you've posted focuses on the speed whereas I'm more focused on the bloating.

    As far as development time wasted hand coding sql strings: I just build my sql in the query builder and then copy and paste into vba. Allen Browne has a quick solution for this: http://allenbrowne.com/ser-71.html

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,474
    Note that the link you've posted focuses on the speed whereas I'm more focused on the bloating.
    True, I focused on the 16 second differential. Since none of the usual suspects seem to be in play here, the only other test I can suggest is to check your record locking setting (Client Settings) and if it's set to Row level locking, uncheck the option.

    OK, I lied. There is one other thing I can think of. Move/copy these test objects into a new db and test. If the result is not the same, it's something else in the established db you have been developing. At least I get the impression that you are testing in an established db, and Access could be affected by something that's not obvious but is still coming into play.

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    383
    Nope, no record locking is on on my client.

    These tests were performed on a new DB just to test the statement I had read that I posted in OP and try and eliminate other variables. I'm now confident that running query objects themselves, even just select queries, bloat a front end. I've made yet another db for anyone to look at if they are interested. Bloater.accdb I added an index to the table and slimmed down the queries to speed things up from execution times posted earlier, but the tests still show string executes faster and querydef bloates.

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

Similar Threads

  1. Database Bloat on DAO CommitTrans by 400 kb/commit
    By angeleumbra in forum Programming
    Replies: 6
    Last Post: 03-31-2017, 07:25 AM
  2. Back end database bloat
    By vicsaccess in forum Access
    Replies: 6
    Last Post: 06-14-2016, 11:22 AM
  3. DB in development BLOAT
    By libraccess in forum Database Design
    Replies: 4
    Last Post: 12-05-2013, 09:58 PM
  4. MS Updates causing bloat???
    By JasonMann1979 in forum Access
    Replies: 7
    Last Post: 11-02-2013, 11:13 AM
  5. Replies: 8
    Last Post: 11-01-2013, 12:59 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
  •  
Tech Forums: Microsoft Office Forums