Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add index to SQL 7 (2000)

    I have been asked to improve the performance of a number of queries that make Access tables from data on a SQL Sever 7.0 database. Many of the queries were grindingly slow and I have made good progress in speeding them up after following links from an answer to a previous post (thanks again). However I'm left with a few that I have pared down to the bone but which still either run slowly or give ODBC timeouts. I think the cause of this is that one of the main selection criteria for all of these queries (which query an order header / order detail pair of tables) is the delivery date on the order line, and this is not indexed.

    Can I add an index while the server is in normal use? Do I do it via Query analyser and will the syntax that I use with Access work?

    Ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Add index to SQL 7 (2000)

    Unfortunately the syntax is different from that in Access when you add an index. It can be done from Query Analyzer, but it is easier from the Enterprise Manager. If you are not allowed to use Enterprise Manager, then you will need to look at the examples in Books Online. There are numerous options, and different kinds of indexes that can be added.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add index to SQL 7 (2000)

    I found the syntax in the T-SQL help and have created the indexes. One question though:

    The SQL tables that I am indexing are linked to various Access reporting databases. I experimented with one of them and looked at the indexes from inside Access. The new ones weren't there so I deleted the then relinked the tables. The new indexes can now be seen from inside Access. Is this just a cosmetic thing or does it make a difference whether Access 'knows' what fields are indexed?

    Ian

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Add index to SQL 7 (2000)

    <hr>Is this just a cosmetic thing or does it make a difference whether Access 'knows' what fields are indexed?<hr>
    It depends on how the table is being used in Access. If you need an index to make a query updateable, then it's important to relink when using ODBC linked SQL Server tables. Otherwise, if you want to do updates in Access, they will fail because there isn't a unique key. It's also useful to know where indexes reside so you can judge performance issues without going to SQL Server.
    Wendell

Posting Permissions

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