May 11, 2012 at 1:46 pm
Hi All,
I have a query running very wired:
Query:"
Select *
from view1
where score1 > 3.5 and score2 > 6
"
The view1 is a big view which has some sub views to join, score1 is one of the columns from one of the sub views in the view1, and score2 is just from one of the tables in the view1.
One I have this two columns in the where clause, the query runs super slow say 1 minute. If I have only one of any of them, the query runs like a second.
My initial thoughts would be both score1 and score2 need to do compare, so something like order from the view, and it might messed up the big view. But from the estimated query plan, the where clause is only applied after the view is generated. So I don't get it, if we have a simple table with multi where clause with some greater and lesser operators in, it won't return slow?! So might be the sub views messed up the whole actual query plan??
Is there any way to improve? or something really cool behind the scene which I don't know. HELP!!!
May 11, 2012 at 1:56 pm
Can you post the Actual Execution Plan ?
May 11, 2012 at 1:58 pm
Post the view definition please
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2012 at 2:02 pm
Smacks of comparing values within a view that are derived from another view. But as mentioned previously, definitions will help; and please make sure you include definitions of the views within the view.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 11, 2012 at 2:31 pm
Definition of view1:
"SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view1]
AS
Select *
From table1 t1
Left hash Join view v on t1.id = v.id"
Definition of "view":
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create VIEW dbo.view2
AS
SELECT t2.id, v3.score1, t2.score2
FROM table2 t2
inner join table 3 t3 on t2.id = t3.id
inner join view3 v3 on t2.id = v3.id
Definition of "view3":
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create View dbo.view3
AS
Select * From (
SELECT id, Row_Number() OVER (PARTITION BY id ORDER BY score1 DESC) RowNum
FROM table4
) a
Where RowNum = 1
May 11, 2012 at 2:45 pm
hhmmmm I suspect this is not really what your view definition looks like. I have a feeling this is pseudocode for what they are doing. If you really want help with why this stuff is slow you are going to need to provide complete information. This means ddl for all the objects involved. Some sample data would be a big help too as well as actual execution plans.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 2:49 pm
princa (5/11/2012)
Definition of view1:CREATE VIEW [dbo].[view1]
AS
Select *
From table1 t1
Left hash Join view v on t1.id = v.id
Don't force join types unless you're VERY sure of why you've chosen to obliterate 99% of the optimizer for your special case. If you can't describe precisely why this is better in 10 words or less for this query, it's not better.
create VIEW dbo.view2
AS
SELECT t2.id, v3.score1, t2.score2
FROM table2 t2
inner join table 3 t3 on t2.id = t3.id
inner join view3 v3 on t2.id = v3.id
Wide view but nothing of siginificant concern here.
create View dbo.view3
AS
Select * From (
SELECT id, Row_Number() OVER (PARTITION BY id ORDER BY score1 DESC) RowNum
FROM table4
) a
Where RowNum = 1
Ow. How big is Table4?
I realize you're trying to vague everything here for privacy, but honestly without the accurate execution plan there's not much we can do.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 11, 2012 at 2:51 pm
Well the first thing you can do is take the join hint out.
CREATE VIEW [dbo].[view1]
AS
Select *
From table1 t1
Left hash Join view v on t1.id = v.id"
The chance of the hash being better than what the optimiser would pick for every situation is small.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2012 at 10:27 pm
Great thanks everyone!!!
I know it's hard to determine the actual problem without actual definition, even I have them on my hand but still can't tell why.
Yes, I just pasted the pesudo definition here, one is for privacy issue, one is the actual definition have lots of columns selected and table joined, really redundant for reading, so I simplified the definition to the pseudo.
However, I have a simple question:
#1 - the where clause would be applied after result set generated from all tables or view join? Or it will actually apply the where clause to the relative table or view during the join?
As I said, my initial thought about it is score1 and score2 are two different sort order if I choose any none equal operator, so it will actually do twice sort.
May 11, 2012 at 10:28 pm
the join hint, I do know exactly the situation, since table1 is a small size, and view is a really big size, I tried in real-time query and had this conclusion to use join hint. But I could remove it anytime, but the current problem is not join hint related. 😀
May 12, 2012 at 2:03 am
princa (5/11/2012)
#1 - the where clause would be applied after result set generated from all tables or view join?
No. The optimiser will try to push the filters as far down the query tree as possible
As I said, my initial thought about it is score1 and score2 are two different sort order if I choose any none equal operator, so it will actually do twice sort.
What sort? They're inequality predicates and not on any a row_number function. SQL's not going to sort without a good reason (an ORDER BY, GROUP BY or the like)
the join hint, I do know exactly the situation, since table1 is a small size, and view is a really big size,
That doesn't sound like a good candidate for a hash join. If table1 is small, a loop join may be better. Depends on how the view is used and filtered, which is one reason why it's a very bad idea to put join hints in views, or anywhere at all.
but the current problem is not join hint related. 😀
How do you know that? Removed the hint and tested without?
We really, really, really need to see the execution plan and the table and index definitions for the base tables. Without those, it's a wild guess at best and there's nothing (other than the hint) that looks terribly wrong with the view definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2012 at 2:10 am
GilaMonster (5/12/2012)
As I said, my initial thought about it is score1 and score2 are two different sort order if I choose any none equal operator, so it will actually do twice sort.
What sort? They're inequality predicates and not on any a row_number function. SQL's not going to sort without a good reason (an ORDER BY, GROUP BY or the like) be better. Depends on how the view is used and filtered, which is one reason why it's a very bad idea to put join hints in views, or anywhere at all.
Hang on, by "sort order" are you actually meaning the columns have different collation settings?
Seriously, the real definitions, and table definitions, are going to allow us to help you, without them you're unlikely to get a proper solution.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 12, 2012 at 9:30 am
princa (5/11/2012)
Hi All,I have a query running very wired:
Query:"
Select *
from view1
where score1 > 3.5 and score2 > 6
"
The view1 is a big view which has some sub views to join, score1 is one of the columns from one of the sub views in the view1, and score2 is just from one of the tables in the view1.
One I have this two columns in the where clause, the query runs super slow say 1 minute. If I have only one of any of them, the query runs like a second.
My initial thoughts would be both score1 and score2 need to do compare, so something like order from the view, and it might messed up the big view. But from the estimated query plan, the where clause is only applied after the view is generated. So I don't get it, if we have a simple table with multi where clause with some greater and lesser operators in, it won't return slow?! So might be the sub views messed up the whole actual query plan??
Is there any way to improve? or something really cool behind the scene which I don't know. HELP!!!
If Score1 and Score2 are calculated columns, the whole view will need to resolve first before the filtering of the WHERE clause can be applied.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply