Slow intellisense while using FSharp.Data.Npgsql erasive type provider

[melanore]

Description

Hi! We are using FSharp.Data.Npgsql type provider library as strongly typed db access layer for PostgreSQL. This is erasive cross target type provider for netcore2.0/net461. Architecture wise, given type provider is very similar to FSharp.Data.SqlClient tp for MSSQL.

However at current stage solution is pretty big, and intellisense is very slow. Type provider api consists of next parts:

• DbConnection root type with provided types for schemas/tables/columns/user defined types
• AddCommand statically parametrized provided method for creating a new DbCommand.

Creation of new db command with 5 output columns and 5 input parameters typically leads to creation of:

• 1 provided type for command
• 1 provided type for output record with 5 provided properties and 1 constructor with 5 provided parameters
• 2 provided methods (Execute|AsyncExecute) with 5 provided parameters for input params
• 1 provided method method with 3 provided parameters (connection|transaction|timeout) for command instantiation

All caching is local to type provider instance:

• Schemas, tables and columns lookups are cached per connection string
• Provided methods for commands are cached per command text string

Code required for retrieving data for DbCommand provided type with descendants executes in ~5 to 10 ms.

Some profiling metrics: Operating System: Windows 10 IDE: Visual Studio 2019 v.16.0.3 / JetBrains Rider 2019.1 EAP Project framework: netcore 2.0 FSharp.TypeProviders.SDK: latest master

Intellisense time in Visual Studio 19 v.16.0.3 for file containing: 1 command = <.5 sec 5 commands = ~2 sec 9 commands = ~6 sec 12 commands = ~8 sec 24 commands = ~16 sec

Changing a 1 db command text to invalid command in module with 24 commands reports an error in ~10-12 sec after the change. Changing command text back to text, that is a key for provided type in type provider triggers intellisense in ~6sec.

Opening of file with 24 commands after fresh load of solution, after type provider DbConnection instantiation in separate file, takes ~ 28-35 sec.

Example of DotTrace profiling session:

Traces point to method ImportProvidedMethodBaseAsILMethodRef_OnStack_HACK in ProvidedTypes.fs, that is invoked for void returning ProvidedMethods, but this might be a wrong lead.

Are these performance metrics are expected for large solution using an erasive type provider? Any chance you could suggest possible changes, that could be done to library in order to achieve better performance?

2 example DotTrace sessions of devenv.exe attached. https://drive.google.com/file/d/15KrfBCfgThGk5D_c0RC3cj1ZKl4oAFL8/view?usp=sharing

Thanks for your time!

[dsyme]

@melanore I'm surprised at the performance figures, it would be great to sort this out.

Certainly ImportProvidedMethodBaseAsILMethodRef_OnStack_HACK looks like one major bottleneck, and I've created https://github.com/fsprojects/FSharp.TypeProviders.SDK/pull/309 which may solve this problem. Could you try that?

I suspect the type provider is also somehow stressing TPs in some unexpected way, perhaps in the size of the provided code. At a quick glance through https://github.com/demetrixbio/FSharp.Data.Npgsql/blob/master/src/DesignTime/QuotationsFactory.fs I can imagine the overall quotation size being handed over could be quite large. Is there any chance a good chunk of this can be moved into library helpers in the TPRTC runtime library component?

[melanore]

Intellisense is much more responsive now. Opening a file with 24 commands now takes ~ 8-9 sec, Returning an error / return type for DbCommand takes on average ~3 sec. Profiling picture is also different now, - looks like size of passed expressions is indeed stressing TP. https://drive.google.com/file/d/1GCJr6YYrzPdnFnIxiBeC2_c-vHVRUrT_/view?usp=sharing

There might be a way to optimize it by providing columns and parameters info as part of design time config, and then derive actual parameter population and mapping of output columns in runtime. Probably biggest bottlneck now are sequential expressions in TP:

Will try to rewrite this part of type provider. Thanks for pointing it out!

[melanore]

Hi. Any chance given fix could be merged as it did yield some performance improvements?

[dsyme]

Hi. Any chance given fix could be merged as it did yield some performance improvements?

Which PR? thanks

[dsyme]

This one? https://github.com/fsprojects/FSharp.TypeProviders.SDK/pull/309

thanks

[melanore]

Yes, https://github.com/fsprojects/FSharp.TypeProviders.SDK/pull/309. As mentioned, the biggest bottleneck is expression size, but this fix does yield a noticeable improvement.

[melanore]

Didn't notice it was already merged to master. Thanks for help!