Views continue to break

• Putts

  SSCommitted

  Points: 1857

  More actions

  August 15, 2008 at 6:48 am

  #191256

  I have a view that is based off a few tables and it keeps breaking every once in a while and we have to DROP/CREATE or ALTER it again to get it working again.

  The tables behind the View.....

  1. Agency table

  2. Agency-to-lobs relational table

  3. Allowable-lobs table

  4. Lobs table

  The Agency table is your standard "primary table behind a database app." So it gets touched a lot. The Agency-to-lobs table just keeps track of which Lines of Business are set up for that Agency. It also gets updated via the application. The Allowable-Lobs table stores which LOBs are available for each state. That table gets wiped clean every night and re-inserted. The LOBs table is very static and just stores which Lines of Business we have.

  Every so often, a certain agency will start causing timeouts in the application. The line it's timing out on is a SP that uses the fore-mentioned View to verify that the agency has all the LOBs for that agency properly identified. No other agency will cause this timeout.....even agencies that would share state or LOBs with that agency. As soon as the view is re-Created, everything is just fine.

  Why would this be happening? No structure changes are happening that should cause that View to break that I can think of so it's really become quite a dead-end at the moment.

  Thanks in advance for any help.

• Luke L

  SSC-Dedicated

  Points: 35547

  More actions

  August 15, 2008 at 6:52 am

  #858658

  For us to be better able to help you, you'll need to give us some DDL, some sample data and the like. Otherwise we'd just be taking wild stabs in the dark. Also, it would be good to include how your PK and FK relations are set up.

  Thanks

  -Luke.

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 15, 2008 at 7:05 am

  #858668

  here's my stab in the dark:

  the agency in question probably has more data or more changes than the other agencies.

  since the execution plan of a view is compiled, I'd guess that the statistics are growing out of date over time to gather the relevant data, and the execution plan for the view takes longer and longer.

  can you tell us if statisics are set to automatic, or if you'vce got a manual plan to create/update statistics? maybe it's not updated often enough?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!