different execution plan sql2000/Express 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

different execution plan sql2000/Express 2005

Author

Topic

flchico
Starting Member

46 Posts

Posted - 2007-05-17 : 13:17:32

I have the following query:


SELECT a.iAvail_id,b.cLocation_id
FROM ACC_Available a 	
	LEFT JOIN ACC_Available_Pickup b 	
		ON a.iAvail_id = b.iAvail_id AND b.cLocation_id = (SELECT TOP 1 cLocation_id FROM ACC_Available_Pickup WHERE iAvail_id = b.iAvail_id) 
WHERE  a.cStatus_id = 'PREV'  AND a.iAvail_id = a.iAvailGroup_id

Indexes:
ACC_Available
- cStatus_id [CLUSTERED]
- iAvailGroup_id
- iAvail_id
ACC_Available_Pickup
- iAvail_id,cLocation_id [CLUSTERED]
- cLocation_id
- iPickup_id

I have the same structured databases, one under SQL2000 and the other under Express 2005.

When I run it under SQL2000 I get:
- One Clustered Index Seek for ACC_Available
- 2 Clustered Index Seeks for ACC_Available_Pickup

When I run it under Express 2005 I get:
- One Clustered Seek for ACC_Available
- 2 Index Scans for ACC_Available_Pickup

I've verified and compared both table and made sure the indexes are the same.

Are the engines supposed to behave differently? One other difference is that the database under EXPRESS has about 11,000 records while the SQL2000 has about 900 records for ACC_Available and 8000 for EXPRESS and 190 for SQL2000 for ACC_Available_Pickup, maybe this is affecting the choosing of the indexes?

Any ideas?

Thanks in advance.

flchico
Starting Member

46 Posts

Posted - 2007-05-17 : 13:47:19

I found an even simpler example:


SELECT a.iAvail_id,b.cName
FROM ACC_Available a
	LEFT JOIN ACC_Division b 	
		ON a.cFrom_id = b.cDivision_id 	
WHERE a.cStatus_id = 'PREV' AND a.iAvail_id = a.iAvailGroup_id

Indexes:
ACC_Available
- cStatus_id [CLUSTERED]
- iAvailGroup_id
- iAvail_id
ACC_Division
- cDivision_id [CLUSTERED]
- cName

When I run it under SQL2000 I get:
- One Clustered Index Seek for ACC_Available
- One Clustered Index Seek for ACC_Division
When I run it under Express 2005 I get:
- One Clustered Seek for ACC_Available
- One Index Scan for ACC_Division

ACC_Divison has the same amount of records for both SQL2000 and EXPRESS (680)

Kristen
Test

22859 Posts

Posted - 2007-05-17 : 14:03:34

"difference is that the database under EXPRESS has about 11,000 records while the SQL2000 has about 900 records "

Are the Statistics updated on both databases regularly?

Kristen

flchico
Starting Member

46 Posts

Posted - 2007-05-17 : 14:23:09

Hummm... not sure, I thought that was automatic.

I ran this on both databases (SQL2000 and Express)

UPDATE STATISTICS ACC_Available
UPDATE STATISTICS ACC_Division

But got the same Execution Plans again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-17 : 14:32:54

Yes the engines are different between versions. And yes the number of records will impact the execution plan.

Are both queries efficient, regardless of what the plan says? How fast do they run?

You should do a fullscan with your update stats.

And no update stats isn't automatic.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

flchico
Starting Member

46 Posts

Posted - 2007-05-17 : 16:55:34

I reran with:

UPDATE STATISTICS ACC_Available WITH FULLSCAN
UPDATE STATISTICS ACC_Division WITH FULLSCAN

The entire query, which is composed of the 2 examples above plus a few more joins, when run on SQL2000 (on my laptop) runs very fast (under a second) but there's only 197 records on the ACC_Available Table as opposed to the 11,000 records which run on the server (fast dual core) which I just ran again right now and it took over 25 seconds the first time and then under a second the second time, figure that out, now that it seems to be going faster, I'm been fiddling with so many things I don't know if maybe the UPDATE STATISTICS is speeding this up or something else that I did, but I'm gonna cross my finger that this runs under one second and doesn't take 25 seconds.

Something of a clue, when working on my laptop (SQL2000) I join ACC_Available to a huge table 'X' it goes fast and does index seeks, then when I do the same join from another table that has 46,000 records into table 'X' then 'X' does index scans, very strange. This 46,000 table is not exactly the same as ACC_Available but the fields in the join are the same.

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 07:40:16

"then under a second the second time, figure that out"

I expect that's because the second time the query plan is cached.

Posting the Query Plan from each might enable someone here to put their finger on it. Either way, there is a very good chance that a 200 row and and 10,000 row table will use different query plans. You may have an index that is highly Selective at 200 rows, but not Selective enough when the table has 10,000 rows.

Posting the DDL for ACC_Available and ACC_Available_Pickup would also help

You can get the Query Plan with this:


-- SET SHOWPLAN_TEXT ON
GO
-- ... put query here - e.g.:
SELECT * FROM Northwind.dbo.Products
SET SET SHOWPLAN_TEXT OFF
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources