|
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
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
|
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.
(comments are locked)
|
I think the definition of the view and underlying tables would be helpful to us
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....
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.
Are you putting the criteria IN the view definition or are you adding it when you select from the view?
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