MSSQL DB with huge schema

Options
dkg
dkg
in General
I have a large MSSQL database that I work with. I tried to use LINQPad to query this database for the first time yesterday, and ran into issues because the database schema is so large. LINQPad would say 'fetching schema' for a long while then finally error out (out of memory space?). I turned off all the options I could (e.g., Include Stored Procedures and Functions), but it didn't help. The problem is the software associated with this DB uses many, MANY views and table synonyms to implement data security. Each software user gets their own schema, and either a view or synonym for each real table/view. It causes problems with even SQL Server Management Studio, so LINQPad is not unique in choking on the massive schema. I worked around this by creating a user that only had access to the 'dbo' schema, but I'm wondering other people's thoughts on how LINQPad might help work around something like this.
· Share on FacebookShare on Twitter

Comments

  • dkg
    dkg
    Oops, I left out an important detail. This is a LINQ/C# query. I've done SQL queries against this DB for a long time without issue.
    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari
    My performance testing database for large schema has 5000 tables, each with 30 columns. I take it your database is much larger than that?
    · Share on FacebookShare on Twitter
  • dkg
    dkg

    Hello again. I had gotten around this by setting up a user that only had access to the 'dbo' schema (the application uses a different schema for each user with many user-specific views and synonyms). That stopped working, although I may have been using LINQPad 4 at the time, while now I'm using LINQPad 5. In the dbo schema alone, there are 1,421 tables with 38,696 columns.

    · Share on FacebookShare on Twitter
  • dkg
    dkg

    I just re-read my original post. I think the problem is different now. After running for a few minutes, it complains about a timeout.

    · Share on FacebookShare on Twitter
  • dkg
    dkg

    A few minutes of 'Fetching schema...', that is.

    · Share on FacebookShare on Twitter
  • JoeAlbahari
    JoeAlbahari

    It times out after two minutes. I could increase the timeout, but that wouldn't really address the underlying problem, which appears to be that the schema is unusually large - too large to be retrieved in a reasonable time.

    · Share on FacebookShare on Twitter
  • dkg
    dkg

    Sure, but I don't have any control over the schema since the product is third-party. Generally, I can get it to work, but it can take several tries, 2 minutes at a time. Any specific suggestions about how to speed up the schema fetches?

    · Share on FacebookShare on Twitter
  • dkg
    dkg

    I've done a trace to see the exact SQL it is using the fetch the schema. It has four SELECT statements. The first three run in a fairly reasonable amount of time. The last is very slow, and appears to be fetching information on stored procedure and function parameters. I find it very strange that it is doing this, because I have "Include Stored Procedures and Functions" unchecked in the connection properties.

    · Share on FacebookShare on Twitter
  • dkg
    dkg

    Here is the SQL in question:
    select r.ROUTINE_TYPE, r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME, p.IS_RESULT, p.PARAMETER_NAME, p.PARAMETER_MODE, p.ORDINAL_POSITION, p.DATA_TYPE from INFORMATION_SCHEMA.ROUTINES r join INFORMATION_SCHEMA.PARAMETERS p on r.SPECIFIC_SCHEMA = p.SPECIFIC_SCHEMA and r.SPECIFIC_NAME = p.SPECIFIC_NAME where r.ROUTINE_TYPE in ('PROCEDURE', 'FUNCTION') order by r.SPECIF

    The problem is the WHERE criteria that filters on r.ROUTINE_TYPE. Because it is a computed column, and not derived from a table, filtering on it is very slow. If I run the above as a standalone query, it takes 10 minutes to execute; without the WHERE, it takes a mere 12 seconds!

    In fact, the criterion is unnecessary. According to Microsoft documentation, those are the only two values produced by the ROUTINES view.
    https://docs.microsoft.com

    Any chance I could get a v5 (Developer) build with that removed?

    · Share on FacebookShare on Twitter

Categories