x

Criteria in a view

Hi, I have a view which contains a TeamID field. If I place criteria in the field for teams 1,3,6,7,8 or 9 the view runs fine. If I set crietria for teams 2, 4 or 5 it just freezes for a while and returns a time out error.

Have you got any suggestions as to why this could be happening?

Thanks

.............................................................................................

Thank you all for your help. A few suggestions were close to the mark but not quite there. I have just fixed it this morning. I have a view that determines the name of a learner, their program details and other information. The data for this view comes from another sql database. I have been using this view in a lot of other views that are used consistently by members of staff throughout the day. To fix my issue (I still don't know why it made a difference just for one team but it did) I rebuilt that view inside of my current view (I added the fields from the tables in the other database instead of adding them from the already created view). The report now runs smoothly. If you have the time could you please answer the following three questions..

1) Is using the same view in a lot of other views bad practise? I've not had any issues until now?

2) While I have your attention, I use a microsoft access adp project to create my views, procedures and functions. Sometimes I need to create several views to get my desired result, for example I'd use a view to gather data and group it accordingly (with some sum, min, max fields) and then I'd use another view to manipulate this data. Does it matter whether the first view is actually a function or a procedure? I used to use all views but I started using functions for these little side calculations to differentiate between the views that are linked to my front end and functions that just set up the data. Is using functions for this purpose bad practise?

3) linked to q2, in a nut-shell when should a procedure or function be used? I've read a lot on these but can't get a simple answer. I know they can be used in the same way as views but I want to know whether this will slow down my system?

Thank you very much for the help you have provided me with. I know it was difficult based on the limited information I had but something mentioned, although not my problem, triggered me to try my solution. Thanks

more ▼

asked Jan 06, 2010 at 08:55 AM in Default

Christian gravatar image

Christian
2 1 1 2

I think the definition of the view and underlying tables would be helpful to us
Jan 06, 2010 at 08:59 AM Kev Riley ♦♦
It's really long-winded and calculates a lot of information through a set of sub-views and functions. I'm putting criteria in the same field though. That field comes from a table that lists the teamid and team name. The teamid is a primarykey. I appreciate that in most instances the whole definition would be needed but in this instance I don't see how it would be relevant as really speaking it's a simple lookup from a table. I can't understand why it doesn't like certain teams....
Jan 06, 2010 at 10:05 AM Christian
I agree with Kev. The entire definition and underlying tables would be really useful in this case. Off the cuff with this limited information, are there perhaps vastly more entries for teams 2, 4, and 5 than for the others? This is not likely to be the answer, but it could possibly be under some circumstances.
Jan 06, 2010 at 10:46 AM TimothyAWiseman
Are you putting the criteria IN the view definition or are you adding it when you select from the view?
Jan 06, 2010 at 11:19 AM Jack Corbett
Christian - you'd be better to ask the other questions as separate questions, that way the question title will have more relevance, and allow more people to find it, in turn giving you more chance of getting them answered
Jan 07, 2010 at 04:02 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

We all have to do a lot of guessing here. I will only give you some hints on where to look.

  • Look at your execution plan, look for table scans, key lookup, etc.
  • Look at your index, not the Team table, but the tables you are joining on, is it fragmented? Is your statistics up to date?
  • Look at your data, is the filter for team 2, 4 or 5 selective enough? (does it filter the data enough)
  • Look at your code, do you have any correlated subqueries? Are you using a scalar valued function that is called a lot more times when team is 2, 4, or 5. Do you have any hidden RBAR (see http://www.sqlservercentral.com/articles/T-SQL/61539/)?
  • What version of SQL server are you using and how complex is your query? In SQL server 2000 you will end up with table scans if your query is too complex ( a basic rule in SQL server 2000 is no more than 4 joins)

If you have any further question, please leave a comment and I will try to answer that.

more ▼

answered Jan 07, 2010 at 05:54 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left

You mentioned that there are a lot of subviews and functions. Without further details, my guess is that something (perhaps other criteria or perhaps an inner join) limits the data returned for 1,3,6,7,8 and 9 but not for the others. Maybe something in one of those functions takes a lot longer for those values. One way to test via trial and error would be to just run some of the subqueries and/or functions for the different sets until you find the one(s) that are making the difference.

more ▼

answered Jan 06, 2010 at 12:19 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(comments are locked)
10|1200 characters needed characters left

My guess is that you're using a user-defined scalar function that ends up needing to be called many millions of times, and the system is dying somewhat. If you do some research into SQL Scalar functions (including my blog if you like), you'll see how bad they can be. If you're using one in a WHERE clause, it could be drastically bad.

So the system may figure that teams 1,3,6,7,8 or 9 should be filtered in a different way based on statistics involved (maybe they're less active). Perhaps for the other teams, the system figures that it should work out the results of a nasty scalar function for every row and filter on that first... if 'nasty' is an understatement, then that could be a problem.

There's a few approaches you could consider...

I'm wondering if you could try querying the view but selecting just one of the columns, to see if the system has a bit more luck.

Try looking at the Execution Plan (Ctrl-L in SSMS) to try to get a bit of a clue about what's going on. Look at the plan for team 1, and then do the same for team 2, to see if they're the same or different. You may be able to force the plan that's used for team 1 on your team 2 query, to see if that gives you results. It's not pretty though, and you should really try to fix up your system design and query instead.

And definitely edit your question to show all the code involved.

more ▼

answered Jan 06, 2010 at 11:47 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x63

asked: Jan 06, 2010 at 08:55 AM

Seen: 1719 times

Last Updated: Jan 07, 2010 at 07:42 AM