Problems with cursor selecting from a view
-
- This is from BOL 7.0
...select_statement
Is the SELECT statement that defines the view. It can use more than one table and other views. To select from the objects referenced in the SELECT clause of a view being created, it is necessary to have the appropriate permissions.
A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created using more than one table or other views with a SELECT clause of any complexity.
There are, however, a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement cannot:
- Include ORDER BY, COMPUTE, or COMPUTE BY clauses.
- Include the INTO keyword.
- Reference a temporary table.
...<end snip>
I have run into this "bug" in sql 2000. It allowed me to create a view with an Order BY and worked fine until one of the service packs was applied and then it failed.
-
PW, thanks for the explanation, that makes sense.
Noel, thanks for your help in pointing out that I should RTFM. Yeah, I saw that further info about optimizations.
Remi, thanks for.... barking at me, I guess.
Yes, I am aware that a set-based solution would take much less time.
Regardless of the debate about cursors and their usefulness in most situations, I was more interested in why the ORDER BY clause in the underlying view would make such a huge difference.
Vik
-
You need to add Select top 100 percent ...
in 2000, maybe that's the workaround you need in this case ron k
-
Ron,
We've used ORDER BY in views for a while, but never really saw any noticeable problems or performance issues. The other fellows' responses indicated that ORDER BY was bad news in views, which is the first I've heard of it. I'll reconsider it's usage now, especially if others such as yourself have run into problems with it.
SQL2K requires TOP 100 PERCENT in views using an ORDER BY clause, I think.
Vik
-
The real problems comes from this.
Create view vwa
as
Select top 100 percent * from dbo.BigFatTable Order by Col1, Col2
Create Sp b
AS
Select *, (Select SomCol from dbo.vwa A where B.id = A.id) as X from TableB B
The view must redo the sort everytime it is called which is absolutely useless (in this case). That's why the order by should be done outside the view when absolutely required.
I've seen execution plans where the sort in the subquery like this was 99% of the work.. Just take it out and the whole thing runs 100 times faster.
BTW I wasn't barking at you. I was just stating the obvious louder as you had seemed to ignore the question the first time around. I agree that it won't cost a lot of resources on the server since it's only gonna be used a few times a year. But a best pratice is a best pratice... and it's called like that for a reason. I'v converted all my cursor to set based a while back and the system is only faster and mostly easier to debug, but that's just a matter of preferences and necessity I guess.
-
Thanks to Remi and vhanda for pointing out the use of TOP 100 Percent as a fix.
As I recall the "bug" was the fact that it states "Order By" in a view (sql 7.0) is not valid but gives no warning or compile error. It must have been the upgrade to sql 2000 when the view stopped working and I have long since fixed that problem.
-
Remi,
I had checked the execution plan for the view and nothing leapt out at me in terms of resources for the sort, so didn't pursue it further.
In any case, I understand the downfalls of both cursors and (now) ORDER BY in views. I just didn't think it would make that big a difference.
Yeah, you're right, they're called best practices for a reason. But sometimes the business and political realities of the situation, combined with time pressures, available resources, and personnel conspire against us. None of these should be any reason to circumvent best practices, however, but so it goes.
Usage of a set-based solution would have circumvented this issue by not having it rear it's ugly head in the first place. Who would've thunk that such a thing -- ORDER BY in views -- added as a feature in SQL2K would have such an effect. Certainly not the first thing I would've looked for, and I had never read anything about it being a no-no as far as best practices are concerned until you fellows mentioned it today.
Vik
-
That's exactly why an experienced dba is invaluable... it's just one of the 100+? gotchas that you'll find in sql server. You just got to learn them and try to deal with it. There's just no workaround inexperience
. -
declare @Table table (dtDate datetime, ID int)
declare @ID int
select @ID = 0
While @ID < 1000
BEGIN
--Replace with table lookup (preferably on PK)
--Select top 1 @ID = ID
--From Table
--Where ID > @ID int
--Order by ID
select @ID = @ID + 1
Insert @Table values (cast(@ID as datetime), @ID)
END
select * From @Table
Signature is NULL
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply