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'))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.|||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?|||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,

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?|||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.|||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|||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.|||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?|||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.|||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.|||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.|||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.|||Thanks Rocket, do you also have a name for an index on just one field?|||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.|||wow, next time i won't drink so much before posting...sql