MSSQL DB with huge schema
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?
-
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.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.comAny chance I could get a v5 (Developer) build with that removed?