Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird view issue


Weird view issue

Author
Message
Mike Scalise
Mike Scalise
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 814
I have a view whose underlying query will run indefinitely. The query ran within 15 seconds yesterday and today it just won't complete. However, if I run a query like:

SELECT *
FROM viewname

I do get results, which seems odd to me. Shouldn't I be able to run the query itself and have it complete?

Granted, this is a big query, but again, yesterday and prior it completed within 15 seconds. Any ideas on how I might identify what the issue is/how to solve it? It's been a while since I've had to tune this kind of thing, so I'm open to any suggestions.

Thanks,

Mike

__________________________________________________________________________________________________

Mike Scalise, PMP
TheGreenShepherd
TheGreenShepherd
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 591
You're using a couple of vague terms, so let me see if I can probe for more details. You start by saying that when you query it, it runs indefinitely, but if you "select * from yourview" you'll get results.

I'm assuming the following:

1. When you say that you're querying the view, you mean that you're supplying a clause to filter the data, i.e., WHERE id = @somevalue.

2. That when this happens, SSMS doesn't display anything for an "indefinite" period of time in the results pane, and the query timer is ticking happily away in the bottom corner.

3. That when you query "select * from yourview" that the results pane populates instantly.

4. That when #3 occurs, despite the fact that you have results, the timer is still ticking happily away in the bottom corner.

Let me know if I've got that right.

If so, here's something to keep in mind, especially considering that you mention that this is a rather large view. When you query "select * from yourview", SQL doesn't have to do much other than just dump the contents of the view into the results pane. That's why you'll always see results pretty quickly, even though SQL Server is still dumping the contents out. However, if you specify a filter, SQL does have to do a bit more work, as (from what you've mentioned) it's not able to perform the search for the data in a more expedient fashion than going through row-by-agonizing-row of the underlying tables to find data that qualifies to be in the result set.

In either case, it sounds like a) your view could stand to be refactored so that it is more efficient or b) your view could stand to be indexed. It's hard to say without knowing more about the view, the table structures that are being accessed, and/or the queries that are being asked to run against the view.

Edit: You also mentioned that this was able to run yesterday without issue. I know this sounds like a no-brainer, but have any changes taken place to this environment since the last time it was able to run successfully? Anything at all..
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Are the two uses of the query identical? "Select * from MyView" vs just running the exact query in the view without modification?

If so, have you checked for blocking issues when you run the view and it takes forever?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Mike Scalise
Mike Scalise
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 814
A few things to clarify...

1) It's the query that makes up the view that never completes. It churns along in SSMS. No records are returned while it's running, the timer is still running, etc.

2) When I run a query like "SELECT * FROM myview" I get results in about 20 seconds (different from when I run the actual query that makes up the view -- see point #1) and the query completes successfully....it's not that it continues running. GSquared -- I'm not using any WHERE criteria. In effect, it should just be running the query that's in the view.

3) There weren't any significant changes from yesterday to today. I updated statistics on all of the tables used in the view, which didn't resolve the problem. It's not my query/view, but I think the crux of the issue is that it was written inefficiently. I've worked with my coworker to rewrite the query and join indexed fields (which wasn't the case initially).

I think I'm OK for now, but I'm still a little puzzled as to why the view returned results yesterday (albeit after 15 seconds) and why today it required me to rewrite the whole thing. Moreover, I don't know why "SELECT * FROM myview" worked before I refactored the query that made up the view.

Mike

__________________________________________________________________________________________________

Mike Scalise, PMP
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
I'd have to see both execution plans, at the very least, to help more on this. Can you post those?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Mike Scalise
Mike Scalise
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 814
Gsquared,

Thanks for the reply. After rewriting the query, the process now runs in one second (down from 20 seconds / not completing at all), so I'm not sure it's worth revisiting it the old way. I appreciate the response.

Mike

__________________________________________________________________________________________________

Mike Scalise, PMP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search