Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2002
    Posts
    4

    Unanswered: Query faster in Access than SQL Server

    I have an VB application which i recently change from using an Access database to SQL Server. I upsized the Access Database to SQL. The query takes about 1 second to run when run from access. It takes about 1 minute to run from SQL server using query analyser or through my application. I would be greatful for suggestions. The query is:

    SELECT *
    FROM (((((((WorkLog AS W LEFT JOIN Grp AS G ON w.wrkgrpsysid = g.GrpSysId)
    LEFT JOIN Register AS R ON w.wrkregSysId = r.regSysId)
    LEFT JOIN Location AS L ON w.wrklocSysId = l.locSysId)
    LEFT JOIN CostCenter AS C ON w.wrkcstSysId = c.cstSysId)
    LEFT JOIN SubAssembly AS S ON w.wrksubsysid = s.subsysid)
    LEFT JOIN Priority AS Pr ON w.wrkpriority = pr.prisysid)
    LEFT JOIN Plann AS P ON w.wrkplnsysid = p.plnsysid)
    Left join Route RT on w.wrkrtesysid=rt.rtesysid
    Where (WrkType = 'PM' or (WrkType = 'PL') or (WrkType = 'UN'))

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What indexes do you have defined for these tables? One way to get a handle on what's going on is to use Query Analyzer to view the execution plan. Paste your query in to QA and on the menu select Query->Show Execution Plan. or hit Ctrl + K. When your query runs you will see what indexes are being ussed OR if a table scan is being performed.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Posts
    4
    The indexes that are defined in these tables are the ones that have been upsized fromthe Access db. They are all primary indexes. There were no indexes for WrkType. I created some and the time did not improve.

    Looking at the execution plan it appears that there are a lot of table scans going on and then results are joined, I cant see any reference to the indexes here. Should this be happening when i have indexes?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if you have usable indexes, yes.

    you could use the index wizard. In QU have your query ready to go, select Query -> Index Tunning Wizard. Just follow the steps and select thorough for the tuning mode. This will look at your query, tables, indexes and statistics. If more optimal indexes or statistics are needed you have an option of creating them or scripting them for later execution.

    Try this and post back with questions.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2002
    Posts
    4
    Paul,

    I did as you said and it reported that no indexes are used in executing the query. It also reported that no changes were needed.

    I cant think of anything other than the indexes that would be causing such a dramatic difference in the execution times. Would clustering some of the indexes help?

    It seems strange considering that the DB has been upsized with all the indexes etc.. I presume that this is a common problem?
    Last edited by ravig1; 09-09-03 at 13:12.

  6. #6
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    I just converted my access database to SQL server (converting most queries to Stored Procedures) and have found that my queries run somewhat slower on my standalone system, but apparently execute much faster than access originally in our companies production system.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Run UPDATE STATISTICS on your tables. I'm guessing your query plan was originally compiled when there was little or no data in your tables, hence the optimizer chose not to use the indexes.

    blindman

  8. #8
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    I can't imagine access handling anything better than SQL Server. I'd also say make sure your statistics are updated, and are set to automatically update. Also, make sure you have indexed fields in the most efficient ways. AND make sure your server is using all the processors it has available, etc. If there are table scans going on, then I would think that the indexes aren't set up properly.

    I've tried to upsize db's from access before, and had nothing but problems. If I were you, I'd build the db like I wanted in in SQL Server, then export the Access data to a text file or something and Import it into SQL Server. That's proven the best way for me.
    Last edited by AnSQLQuery; 09-09-03 at 23:20.

  9. #9
    Join Date
    Aug 2002
    Posts
    4
    Thanks everyone for your help so far.

    Ive tried to see where delay is coming from. When i run the following query:

    select WrkSysid
    from worklog
    where (wrktype = 'PM')

    it does an index seek on the field 'wrktype' and an index scan on the field 'Wrksysid'. But when i run the following query:

    select WrkSysid, WrkGrpSysid
    from worklog
    where (wrktype = 'PM')

    it does a table scan. This is despite having indexes on all 3 fields. Ive run UPDATE STATISTICS on all the tables. Is this normal?

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I came across such an amount of problems, that I wrote and used my own conversion program.

    Your query has a lot of joins; do you have a referential constraint between WorkLog.wrkgrpsysid and Grp.GrpSysID, for example ? If not, make sure that you have an index (simply with duplicates) on wrkgrpsysid and an index (preferabelly UNIQUE) on GrpSysID.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    Is the index a clustered index? Or do you have a seperate index defined for each field? It looks like you have seperate indexes on each field. If so, try using a clustered index containing both of those fields.

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by AnSQLQuery
    Is the index a clustered index? Or do you have a seperate index defined for each field? It looks like you have seperate indexes on each field. If so, try using a clustered index containing both of those fields.
    There is plenty of noise in your remark, Query.

    1) I guess you mean with "clustered index" one index on several fields, instead of two indices?! Such an indx does not have a special name.
    2) You can't create an index on fields in two tables!
    3) With a clustered index, you store the whole table according to the sort order of the index. The question, however, is to join related data to a WorkLog table, which shall be scanned fully without any ordering.

    Depending on the selectivity, you may increase the speed by defining an index on the criteria field WrkType.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    1) I guess you mean with "clustered index" one index on several fields, instead of two indices?! Such an indx does not have a special name.


    Doc,
    This would be called a composite index.

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Thanks Rocket, do you also have a name for an index on just one field?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do you have a composite index on wrktype and wrkSysid? If so, your first query would execute solely on the index and never touch the actual table, making it very fast. Your second query would have to reference the table to pull in the WrkGrpSysid value.

    Also, a lot depends upon the cardinality of your data, which describes how many unique values are in a given column or combination of columns. If the cardinality of wrktype is low (say there are only two values; "AM" and "PM") then the optimizer gains little or nothing from using an index on that column because it does not significantly reduce the number of pages it has to search through. It would thus ignore the index.

    blindman

    PS: Indexes on single columns are just called indexes.

Posting Permissions

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