Results 1 to 6 of 6
  1. #1
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463

    SQL Indexes

    Any SQL index experts? If you have multiple fields in a table used with criteria to pull data, do you do individual indexes on each field or do 1 index and include all the criteria fields? Or what is maybe general rules on adding indexes to SQL tables?

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    general rule: put a non-clustered index on the foreign key fields.
    For the rest: you can put an index on much used criteria, but don't exagerate: when updateing the record there is a cost: not only the data need updating but also every index .
    Generally speeking only the first two index fields are used to determine the query plan. Best practice: activate the query plan in the query menu. Best is of course looking at the actual plan, but the estimated plan can give you already a good indication. Execute the query before you add the index, look at the plan and check for indications if adding an index could help. Add the index and look if the new query plan improved the query plan.
    From SQL 2016 on you can activate the query store to keep an eye on your query performance.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    When you say activate the query plan, is that in MS Access or SQL Management Stuidio? I'm using MS Access 2007/2010.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I see the options in SQL Management Studio. But how does it work if all your queries, etc are in Access. Do you copy the query code(and adjust as needed) to a Query in SQL? Some queries use other queries in MS Access so makes it kinda tough to do that.

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I always use the sql backend, if the queries are Access queries they are parsed and executed from access, so you don't have any saying in how they will be passed to SQL server. In many cases the data are pulled completely in access and filtered/ordered by in access instead of on the server. To view how the queries are passed on:
    * activate the query store
    * create an extended event : you'll find those in the management studio under the server node - management - extended events. There you can filter for the events you're interested in
    * run a trace for a period of time with Tools -> SQL server profiler. Take care because this options consumes quite some resources

    To have more control over the queries:
    * for the base queries : create views on SQL server
    * create the queries as pass-through queries in access. These queries are written in T-SQL and passed as-is to the server. The server evaluates them, creates the query plan and sends the resulting dataset back to the front end
    * create procedures on SQL server to get/alter the data and call those from access

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Ok I see, have used pass thru queries but been awhile, might look at doing that again. Thanks for the help.

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

Similar Threads

  1. Help with indexes
    By PubCrawl in forum Access
    Replies: 8
    Last Post: 12-19-2020, 01:17 AM
  2. Indexes in a Table
    By FB93 in forum Access
    Replies: 2
    Last Post: 03-18-2014, 07:27 PM
  3. Too Many Indexes
    By cbrsix in forum Database Design
    Replies: 22
    Last Post: 11-07-2011, 11:12 AM
  4. Indexes limited
    By DanW in forum Access
    Replies: 6
    Last Post: 11-12-2009, 03:12 AM
  5. Table Indexes
    By AnthonyT in forum Access
    Replies: 3
    Last Post: 06-04-2009, 06:16 AM

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