Why sql query is fast with view then it is without view??

[fletchsod]

I'm trying to understand why does the sql query run faster with the view instead of without it.. The one with the view took 14 seconds while the one without the view took 4 hours.

SELECT TOP 10 'F', tblSold.Year, tblSold.MakeID, tblSold.ModelID 
FROM vwAvgSale 
WHERE	tblSold.SoldDate >= @SearchDate	
AND	tblSold.SaleType = 'R' 
AND	tblvZipCodes.City = @City
AND	tblvZipCodes.State = @State

The source code for the view "vwAvgSale" is

SELECT  tblSold.Year, tblSold.MakeID, tblSold.ModelID 
FROM dbo.tblSold INNER JOIN
dbo.tblvZipCodes ON dbo.tblSold.ZipCode = dbo.tblvZipCodes.ZIPCode AND dbo.tblSold.City = dbo.tblvZipCodes.City INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
ORDER BY dbo.tblSold.SoldID DESC

This one I used without the view is

SELECT TOP 10 'F', tblSold.Year, tblSold.MakeID, tblSold.ModelID 
[B]
FROM dbo.tblSold INNER JOIN
dbo.tblvZipCodes ON dbo.tblSold.ZipCode = dbo.tblvZipCodes.ZIPCode AND dbo.tblSold.City = dbo.tblvZipCodes.City INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
[/B]
WHERE	tblSold.SoldDate >= @SearchDate	
AND	tblSold.SaleType = 'R' 
AND	tblvZipCodes.City = @City
AND	tblvZipCodes.State = @State

I welcome some help and explaination on why there is disparity between these two (with and without view)... I don't really know what the problem is... I looked at the database index and it looked fine.

Thanks...

 

[dGrouse]

The ORDER BY clause is invalid in views, or at least in that one; you can only have ORDER BY present if TOP or FOR XML are also specified. So, that can't be the code for your view...

Jeff Prenevost
BI Consultant
Ann Arbor, MI

 

[pmegan]

Maybe it's because the query has to figure out an execution plan while the view already has one saved; especially if the zip code and city fields aren't indexed. I've had discrepancies like that before, but never by that much.


I'm able to create a view with an ORDER BY clause in SQL 2000.

Here's the code for the view

CREATE  view vTest
as
select top 10 p.pub_name, t.* 
from publishers p join titles t on
		p.pub_id = t.pub_id
order by
	t.pubdate desc
GO

Here's what I used to test it. The results matched, so I changed the ORDER BY in the view to asc to make sure it was actually changing the order.

select top 10 p.pub_name, t.* 
from publishers p join titles t on
		p.pub_id = t.pub_id
order by
	t.pubdate desc

select * from vTest

 

[fletchsod]

Just finished working on the problem a few hours ago. All I did was tweak the City & State in the where clause by making the State come first and the City come second. The end result is now 1 hour.

AND    tblvZipCodes.State = @State
AND    tblvZipCodes.City = @City

I just don't get it on why it works now. There are fewer cityies with the same name than it is with the state name in the zipcode table. Weird...

I don't think the sql query is the problem here. Maybe something on MS-SQL's backend (internal) isn't updating itself or something.

Oh well! That's life!

 

[SQLSister]

Since you can index views, is it possible your view uses indexes that the regular query can't access? What do the two execution plans look like?

You may have an index problem (not having indexes on the columns you need) or you may need to update your statistics or reindex.

"NOTHING is more important in a database than integrity." ESquared