Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Weird view issue Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 8:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:47 PM
Points: 137, Visits: 533
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
Post #1369098
Posted Friday, October 5, 2012 9:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:00 PM
Points: 570, Visits: 483
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..
Post #1369155
Posted Friday, October 5, 2012 12:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1369252
Posted Friday, October 5, 2012 12:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:47 PM
Points: 137, Visits: 533
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
Post #1369255
Posted Tuesday, October 9, 2012 11:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1370507
Posted Tuesday, October 9, 2012 1:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:47 PM
Points: 137, Visits: 533
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
Post #1370569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse