SQL Server Performance

how to count table used in a query

Discussion in 'General DBA Questions' started by sramesh, May 3, 2006.

  1. sramesh New Member

    hi all
    this is my error

    Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

    in future i want avoid this error.
    i want count how many tables in the view

    any program available in market

    Thanks
    S. Ramesh
  2. shekhar157 New Member

    HI,

    You can just pass this query to find out which column or tables are using in a perticular DB.

    SELECT * FROM INFORMATION_SCHEMA.VIEWS

    Regards

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  3. mmarovic Active Member

    Was partitioned view involved there?
  4. satya Moderator

    What is the service pack level on SQL?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. FrankKalis Moderator

    Usually the maximum of 256 tables per SELECT statement is a rather theoretically one. If you get anywhere near this limitation, I would seriously suggest you review your complete data model (logical and physical).

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  6. Madhivanan Moderator

  7. Adriaan New Member

    Perhaps over-normalization? I've seen systems like that, where all FK columns are on a substitute key, and you need to add the RK table to your queries to read the 'natural' key.

    Some people seem to think identity columns are a performance boost in all thinkable situations. I think not.
  8. mmarovic Active Member

    I don't think that is the matter of using surragate keys. Either partitioned view with too much partitions is involved or it is bad design, specifically intention to create one or a couple over-general views that will cover every possible query.
  9. Adriaan New Member

    ... and perhaps it's both: over-general views on top of over-normalized tables.
  10. mmarovic Active Member

    It may be, but I am afraid I don't agree with you about the use of surogate keys. However, I don't have time right now to discuss such "religious" topic. I guess once we both have enough time, we can start a thread about it.
  11. Adriaan New Member

    I'm not religuous about anything.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  12. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I guess once we both have enough time, we can start a thread about it<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Oh my. You only need to follow the frequent disputes between Tony Rogerson and Joe Celko on the .programming newsgroup on that topic to be fed up with it. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  13. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />I'm not religuous about anything.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I know you are not, but that topic is, believe me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Just ask Frank. Besides the newsgroup he mentioned I saw a couple of his posts in related threads on different forums. I have seen he gave up immediately after figuring out that will be never ending flame with both sides shouting at each other. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]
  14. Adriaan New Member

    Flame wars on other forums should not imply that <i>we</i> cannot have a civil discussion on the same subject.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  15. FrankKalis Moderator

  16. mmarovic Active Member

    I believe it wouldn't be flame war here, but I am sure it would be much more time consuming then I can afford right now. However, you are free to start one if you really want, I may join later when I have enough time. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  17. mmarovic Active Member

    Frank: Yes, I know them but I resisted to contribute [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] I also remember discussion about how it is bad to use stored procedures in sql server and another discussion(s) about how great/bad are orm tools.
  18. Adriaan New Member

    I really don't frequent other database forums. The same for other forums - you stick to the civil and polite ones.

    Mirko, I don't think I'll go into even a civil discussion on surrogate keys and their uses. We are probably not working in systems of similar sizes - with me obviously on the 'small scale' end of the spectrum.
  19. mmarovic Active Member

    These topics were about articles I read. Besides, you see how many responses we produced after just one touch of "surogate against natural key" topic. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  20. sramesh New Member

    Hi all
    no one give correct solution. why i asked this question?
    if i get the answer, i will reduce the table links for the particular view.
    select * from viewProd
    viewprod -- viewa ---- fnout1
    viewb ---- fnout3
    viewc ---- view10
    viewd ---- view8

    this is my query. if i get the answer, i will solve the problem.
    please dont fight


    Thanks
    S. Ramesh
  21. Adriaan New Member

    Apologies for the thread hijack!

    You can check the dependencies for each view in Enterprise Manager. Right-click the view, then select All Tasks > Display Dependencies.

    You can also run a query against the appropriate INFORMATION_SCHEMA view - insert the name of the query that you're looking for:

    SELECT i.TABLE_NAME
    FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE i
    WHERE i.VIEW_NAME = '..................'

    Note that this query will also return UDFs called from the view, and underlying views. If I understand correctly, the tables in underlying views also count.

    This may already give you an indication of the 'heaviest' views:

    SELECT i.VIEW_NAME, COUNT(*)
    FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE i
    GROUP BY i.VIEW_NAME
    ORDER BY 2 DESC, 1 ASC
  22. Madhivanan Moderator

    Also use sp_depends 'yourView'

    >> also remember discussion about how it is bad to use stored procedures in sql server and another discussion(s) about how great/bad are orm tools.

    Mirko, can you give me the link?

    Madhivanan

    Failing to plan is Planning to fail
  23. hassima New Member

Share This Page