Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 9th, 2003, 05:41 AM
Registered User
 
Join Date: Sep 2003
Location: , , Ireland.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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'))
Reply With Quote
  #2 (permalink)  
Old September 9th, 2003, 05:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old September 9th, 2003, 10:47 AM
Registered User
 
Join Date: Sep 2003
Location: , , Ireland.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
  #4 (permalink)  
Old September 9th, 2003, 11:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old September 9th, 2003, 11:21 AM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #6 (permalink)  
Old September 10th, 2003, 04:52 AM
Registered User
 
Join Date: Sep 2003
Location: , , Ireland.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Crosstab Query elygp SQL Server 2000 2 May 5th, 2007 11:57 AM
ASP- SQL Server..Query help.... cancer10 Classic ASP Databases 5 February 1st, 2007 06:26 AM
SQL SERVER Query pavanpareta SQL Server ASP 2 December 18th, 2006 04:51 PM
append to a query - from access to SQL server p2ptolu SQL Server 2000 0 June 15th, 2005 08:23 AM
Crosstab query in SQL Server Dhodie SQL Server 2000 3 June 17th, 2004 08:53 PM



All times are GMT -4. The time now is 06:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.