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?
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
We all have to do a lot of guessing here. I will only give you some hints on where to look.
If you have any further question, please leave a comment and I will try to answer that.
answered Jan 07, 2010 at 05:54 AM
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.
answered Jan 06, 2010 at 12:19 PM
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.
answered Jan 06, 2010 at 11:47 PM