MSSQL DB with huge schema

dkg

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.

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.

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?

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.

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.

dkg

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

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.

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?

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.

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?

dkg

Still having lots of issues with this, including LINQPad 8. It would be helpful if LINQPad would load synonyms. I could create synonyms in another database pointing to the objects I need.

JoeAlbahari

Sorry, I forgot to mention in 2021 that I removed the WHERE clause as you suggested. Is it still running slowly, and if so, are you able to determine why?

dkg

Sorry it took a while get back to you. As you can imagine, it takes time to research this. Yes, in current LINQPad 8, it is slow. I captured the schema query for current LINQPad 8. Running it on its own is taking around 3 minutes. I haven't yet found any obvious bottlenecks that could be tweaked. The "12 seconds" I reported earlier I think was in a different version of LINQPad.

That said, there is something I may not have explained clearly in a previous post. The counts I gave (1,421 tables with 38,969 columns) are for one of many schema. In total, there are over 3,500,000 rows in sys.columns in the production database. What would go a LONG WAY in my case is a filter on what schemas are loaded. If I add and schema_name (o.schema_id) = 'dbo' to the query, it takes 13 seconds. Or, if I add and o.schema_id = 1 to the query, it takes 1 second. Could a schema filter be added to the advanced connection options?

JoeAlbahari

LINQPad 9 has regex schema filtering options, although on SQL Server, it filters on the client side because SQL Server still doesn't support regex, so it won't help in your case.

Regex support is planned, though, in SQL Server 2025. I'm also planning a possible alternative filtering mechanism that will let you use % and _ wildcards instead. Stay posted.

JoeAlbahari

The LINQ-to-SQL driver in 9.3.5 supports server-side filtering. Give it a go and let me know how you get along.