Subquery performance in SQL view

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 2008 Forums

Transact-SQL (2008)

Subquery performance in SQL view

Author

Topic

truchuynh
Starting Member

4 Posts

Posted - 2011-12-16 : 16:04:14

Hi all,

I 'm having some SQL view performance as follow:

create View1 as (fast)
SELECT *
FROM Sale
WHERE year='2002'

create View2 as (very slow)
SELECT *
FROM Sale
WHERE year >=
(select min(RefYear) from DateTable where Survey='MMS')

Note:
Sale table has 2 millions records, DateTable has about 100 rows
The subquery is supposed to return '2002' only.

Question:
Does the subquery run everytime for each record in the Main query ?
If yes, is there a way to modify this syntax to make the subquery only run ONCE ?

In my project, I'm constraint to use a VIEW.
In a view, is there a way to create a variable (@Year) and store subquery's outcome.
Thanks for all your inputs.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 16:40:28

It first runs the subquery and then applies it to the main select. If you are having performance problems, then you need to look at indexes. What indexes do you have on these tables? Or the view is returning too many rows.

No you can't add variables to views. You could use functions, but that brings in more performance issues.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-12-16 : 22:35:51

View2 returns more rows than View1?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-12-16 : 23:37:43

Why are you using a view at all?

try this

declare @dt as datetime
set @dt = select min(RefYear) from DateTable where Survey='MMS'
SELECT *
FROM Sale
WHERE year >=@dt

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

truchuynh
Starting Member

4 Posts

Posted - 2011-12-18 : 21:33:07

There is a typo in View1, it should be
SELECT *
FROM Sale
WHERE year >='2002'

So view1 and view2 are returning the same number of rows.
Without the constrainte of the view, we could have used variable as Vinnie881 suggested.
I will try some indexes to see if it make the view faster.
Thanks for your inputs .

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-12-19 : 13:00:37

Although I still don't think you should be using a view in this scenario, I suppose you can do this to increase performance.


CREATE VIEW VIEW2
AS
select b.*
from
(select min(RefYear)as refyear from DateTable where Survey='MMS') a
cross apply
(Select *
FROM Sale
WHERE year >=a.refyear) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources