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
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?
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.
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.SPECIFThe 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?