How do view execute?

Question

How do view execute?

auxcom

Say Hey Kid

Points: 701
More actions
April 24, 2004 at 11:42 pm

#161811

Do a view execute everytime it is query by another query? Example, I have a query like this:
select ...
(select ... from MyView where ...) as field1
(select ... from MyView where ...) as field2
(select ... from MyView where ...) as field3
....
from MyView
The subquery will query from the view. The subquery is about 10 times repeated based on condition. That select statement of mine takes about 15 seconds to execute eventhough the MyView will just query and filter about 100 records only from one of its table. MyView will join 5 tables and 1 view.
Is the view is the making my query slow or the subqueries?
Thanks!
AUXilliary COMmunication 1

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Worse than that, it's been my experience (in SQL Server 7) that a SELECT such as yours will create a separate instance of the view, in memory, for each named instance in the SELECT. If the views have a lot of rows, that could quickly drive your server into a "swap file" run. Perhaps they fixed this "little" problem in SQL Server 2000?

One way to get around this problem is to use CASE statments instead of SELECTs to create each field.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

auxcom Say Hey Kid Points: 701 More actions · Answer 2

The select subqueries contains agregrate functions like:

(select count(*) from myview where ...) as field1

(select sum(field) from my where ...) as field2

How to use case for sql? Can you show some code?

Thanks!

AUXilliary COMmunication 1

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

>(select count(*) from myview where ...) as field1

>(select sum(field) from my where ...) as field2

Your last post said that you used aggregate functions for each field. Looking at the code you posted, are the WHERE clauses all different or are they the same?

For a CASE statement example (very simple example, will get an argument from others)...

SELECT
Field1,
Field2,
CASE
WHEN Field3 > 0 THEN 'Positive Number'
WHEN Field3 < 0 THEN 'Negative Number
ELSE 'Zero'
END AS NewFieldName
FROM yada,yada,yada

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

philcart SSC-Forever Points: 47794 More actions · Answer 4

Easiest way to check out what's happening is to paste the full statement into Query Analyzer and execute with "Show Execution Plan" selected, or use "SET SHOWPLAN_ALL ON" at the start of the statement.

"Show Execution Plan" will present a graphical view of the steps taken by the query processor as it determines how to execute the statement.

The "SET SHOWPLAN_ALL ON" statement returns the same information but as a series of rows that form a hierarchical tree.

Check Books Online for more detail on each.

Once you can see the execution plan you'll be able to determine what part of your query is taking the most time/resources.

--------------------
Colt 45 - the original point and click interface

auxcom Say Hey Kid Points: 701 More actions · Answer 5

The subquery is a correlated subquery. All the where clause are different. Example:

select ...,

(select count(*) from myview where myview.id = m.id and myview.field1 = 1 and ....) as fieldname,

(select count(*) from myview where myview.id = m.id and myview.field2 > 10 and ....) as fieldname,

...

from myview as m

where ...

group by ...

AUXilliary COMmunication 1

philcart SSC-Forever Points: 47794 More actions · Answer 6

Can you paste the full query?

--------------------
Colt 45 - the original point and click interface

juliekenny SSCommitted Points: 1558 More actions · Answer 7

If possible I've found that it speeds things up if you can split queries like this this down, perhaps using intermediate table to hold the summary figures and then linking to them, or variables if appropriate. If you run the query and take a look at the locking do you see screenfuls of locks against tempdb? I have found that the locking sometimes uses all the memory available and by not making the query do everything at once this is averted.

J Kenny