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'))

My first step would be to check that all the tables have the correct indexes created, and that they cover the columns you're joining on.

HTH

Chris


There are two secrets to success in this world:
1. Never tell everything you know

The indexes that are defined in these tables are the ones that have been upsized from the 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?

Basically, any time you see a tablescan, you should start looking to create an index on the column being scanned. This is of course a very simplistic, and broad-brush approach, but indices and index tuning fills entire books, so we'll start simple :)

chris


There are two secrets to success in this world:
1. Never tell everything you know

Nonono! Table Scans mean that SQL Server is scanning through the entire table to select the subset of rows that match the results of your query. Your indexes are clearly NOT being utilized.

Typically, you'll have two types of indexed access in SQL Server -- Index Scan and Index Seek.

Index Scan is a quick scan across the values in an index column to extract the subset of rows that match your query. An Index Scan is necessary when selecting nonsequential rows over an indexed column, whether it's the primary key column or not.

Non-primary/unique/sorted indexes are stored by SQL Server in it's own internal tables, which is why they're called "non-integrated" indexes. The storage for the index table is not "built-in" to the actual table. These index tables keep a sorted copy of the index column with row pointers to the corresponding rows in your original table. When SQL Server performs index scans, it uses these internal index tables to quickly return the subset of row pointers that match your query.

Index Seek occurs when SQL Server needs to select a single, continuous set of rows using an "integrated index" (typically just the primary key column, integrated indexes can be over several columns as long as the values are sorted and unique). WHERE clauses over a sorted, unique, set of columns only require one or two seeks for the whole table. These seeks locate "bookmark" rows that form the endpoints of a query.

If your where clause specifies x > Y or X < Y, you'll only need one bookmark to specify the first or last rows, respectively. In the X > Y case, the last row in the table is the end point of your result rowset. Conversely, in the X < Y case, the first row in the table is the start point of your result rowset. This is guaranteed because column X is already sorted.

If you have X = Y, your query will use two index seeks, one for the first row and one for the last. If your WHERE clause covers the entire index, and therefore each row is unique, SQL Server might only issue a single index seek to find the unique row.


Sorry if this is too much information, but I thought you might like to know what all the different scans and seeks mean.

As always, you can search the net for more info. MSDN actually has a LOT of documentation (some of it is even good!) on SQL Server and the SQL language.


Take care,

Nik
http://www.bigaction.org/

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?