Performance issue with view

Question

Performance issue with view

sqlnaive

SSCoach

Points: 17437
More actions
May 28, 2014 at 3:05 am

#396824

I have a view which has joins with multiple tables and have some complex logic. I am facing weird performance issue. We use this view to retirve information based on business date. Now for one particular date in one particular environment it is running infinitely. For other dates it's working fine. Also the date for which it is giving issues, it's working fine in different environment. I've updated statistics and defragmented all the underlying tables as well but no benefit. One more thing - While running the body of the view for the problem date, it works fine. It's just that view is running infinitely for so much time. Any guesses where to look out for ?

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

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

DouglasH SSCommitted Points: 1669 More actions · Answer 1

try sp_refreshview as a start.

I'm assuming when you say 'infinite' you mean a long time. Does it actually return with the data you need or does the query finish and not return anything?

I would be interested to see the 2 actual queries you run from ssms.

1 - the query with the view

2 - the query with the body of the sql but not in a view (the one you say works)

sqlnaive SSCoach Points: 17437 More actions · Answer 2

I tried refreshing view as well. One more thing i noticed now that it's working fine when running without where clause but issue remains when run with where condition. Where condition contains just one condition to check business date. Our infrastructure is such that though without where condition it will give records for same business date but to be on the safer side we have to keep it.

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

The addition of a WHERE clause frequently changes the execution plan even if the same data is returned. Compare the two execution plans (one with and one without the WHERE clause) and see what's up.

If you can't suss the problem from there, then post what you have IAW the second link under "Helpful Links" in my signature line below so that folks have a better chance of helping you figure this out.

--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)

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Thanks Jeff. I compared the two execution plans and saw that with where clause it was considering clustered index scan for one of the table while without where clause OR taking parameter into consideration for where clause, it's using clustered index seek.

I guess i forgot to mention that we are executing view with the help of dynamic query which shows the runtime query as follows:

select * from view_impartant where business_date = 20131231

-- This is causing issue

Now when we run it like this:

declare @business_date int

select * from view_impartant where business_date = @business_date

-- This runs perfectly

Now i have made the change and execution looks good. But not i see lots of sort operations in the execution plan. I am not using any order by clause but using many UNION ALL and UNION conditions. Can they be the reason ?

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

sqlnaive (5/28/2014)
Thanks Jeff. I compared the two execution plans and saw that with where clause it was considering clustered index scan for one of the table while without where clause OR taking parameter into consideration for where clause, it's using clustered index seek.
I guess i forgot to mention that we are executing view with the help of dynamic query which shows the runtime query as follows:
select * from view_impartant where business_date = 20131231
-- This is causing issue
Now when we run it like this:
declare @business_date int
select * from view_impartant where business_date = @business_date
-- This runs perfectly
Now i have made the change and execution looks good. But not i see lots of sort operations in the execution plan. I am not using any order by clause but using many UNION ALL and UNION conditions. Can they be the reason ?

This isn't part of the performance issue but why do you store date information as an int? You should use the date datatype. It provides instant validation and you don't have to fight sargability and all sorts of other issues because of incorrect datatypes.

_______________________________________________________________

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/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

sqlnaive (5/28/2014)
Thanks Jeff. I compared the two execution plans and saw that with where clause it was considering clustered index scan for one of the table while without where clause OR taking parameter into consideration for where clause, it's using clustered index seek.
I guess i forgot to mention that we are executing view with the help of dynamic query which shows the runtime query as follows:
select * from view_impartant where business_date = 20131231
-- This is causing issue
Now when we run it like this:
declare @business_date int
select * from view_impartant where business_date = @business_date
-- This runs perfectly
Now i have made the change and execution looks good. But not i see lots of sort operations in the execution plan. I am not using any order by clause but using many UNION ALL and UNION conditions. Can they be the reason ?

From what I've seen, UNION will almost certainly cause a sort because it also does an implicit DISTINCT. In order to more quickly figure out the distict values, it's better to sort the data first and simply scan the data for changes than it is to "memorize" each value and see if it already exists in the "memorized values".

I also agree about the INT dates. It makes it impossible to do certain date calculations easily without either an implicit or explicit conversion.

--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)

Ankush Parab SSC Veteran Points: 240 More actions · Answer 7

The similar issue occurs due to parameter sniffing. You may find many explanations about parameter sniffing and different ways to handle it. One of the method is to use "OPTMIZE FOR UNKNOWN" hint.

sqlnaive SSCoach Points: 17437 More actions · Answer 8

Thanks Ankush. If I ever get such chance again, wil test this option. 🙂