Home

MSSQL DB with huge schema

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.

Comments

  • 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.
  • My performance testing database for large schema has 5000 tables, each with 30 columns. I take it your database is much larger than that?
  • 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.

  • 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.

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

  • 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.

  • 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?

Sign In or Register to comment.