Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Insert multiple values (across different tables) using SqlCommand Provider #331

Closed
sharno opened this issue Mar 8, 2019 · 4 comments
Closed
Labels

Comments

@sharno
Copy link

sharno commented Mar 8, 2019

Description

Is there a way to execute multiple commands in one round trip to the DB?
I want to have the Async execution capability of commands (so couldn't use SqlProgrammability) and at the same time execute multiple SqlCommands together in a certain order as a bullk operation on SQL Server.
edit: I realized I can insert a determined number of rows in each table in a single SqlCommand, but I'd like to insert an arbitrary number of rows
Could this be done currently?

@smoothdeveloper
Copy link
Collaborator

@sharno thanks for bringing in your questions.

You can issue multiple SQL commands in a single command execution, but only the first resultset will be exposed as a return value, this is a limitation of SQL Server own meta data inspection mecanism that this library relies on.

You can use output parameters for retrieving scalar values, or temp tables + separate select statements if you need several result sets.

Performance wise, I haven't seen a big difference for simple tasks between issuing several IDbCommand on same connection/transaction versus batching in a single command, but that could pretty much depend the specifics of the batches and have significant impact under your circumstances.

I realized I can insert a determined number of rows in each table in a single SqlCommand, but I'd like to insert an arbitrary number of rows

Performance wise, if your data is of exact shape of a valid subset of columns of physical table, you can use bulk insert facility from the library, that will offer vastly better performance than a batch of insert statements.

If your data is a projection of a mix of user input (coming from your f# app memory) and things in your database, the best is to bulkinsert user input in temp table, and then use insert into ... select ... your projection using temp table.

I want to have the Async execution capability of commands (so couldn't use SqlProgrammability)

This could be added to the library, more details on implementation can be discussed and PR always welcome.

@VTJDailey that may be up your alley if you want to try a small contribution extending the library, the underlying machinery for async execution is already being used for SqlCommandProvider.

@sharno could you add more specifics to the question? maybe a code sample of what you'd do in plain SQL or ADO.NET.

@sharno
Copy link
Author

sharno commented Apr 11, 2019

@smoothdeveloper
Thanks a lot for taking the time to reply and cover all of that. I guess that pretty much answers all of the questions that I had. For the time being just issue multiple insert commands in my project and it's performing nicely for my use case at least. I think bulk insert would work well too, but for some reason SqlProgrammabilityProvider causes Intellisense to freeze and stop working in the whole project which I'm not sure of the cause of it because SqlCommandProvider works fine. But I could open another issue for that if I can investigate it more.

Also, I thought of using a table-valued parameter with a proc for more complicated queries, but I didn't get the time or chance to try it out yet.

@smoothdeveloper
Copy link
Collaborator

smoothdeveloper commented Apr 11, 2019

but for some reason SqlProgrammabilityProvider causes Intellisense to freeze and stop working in the whole project which I'm not sure of the cause of it because SqlCommandProvider works fine.

Could you post some of the specifics of your environment (IDE version, version of the library that has the issue), also, is there anything specific about the database schema you are connecting to? does it have large amount of objects?

I think there is machinery in the TP SDK to make some of the work providing the types/members lazy in context of the IDE, but the current implementation of the provider is doing the inspection of the database wholesale AFAICS:

let schemas =
conn.GetUserSchemas()
|> List.map (fun schema -> ProvidedTypeDefinition(schema, baseType = Some typeof<obj>, hideObjectMethods = true))
databaseRootType.AddMembers schemas
let udttsPerSchema = Dictionary()
let uomPerSchema = Dictionary()
for schemaType in schemas do
do //User-defined table types
let udttsRoot = ProvidedTypeDefinition("User-Defined Table Types", Some typeof<obj>)
udttsRoot.AddMembersDelayed <| fun () ->
this.UDTTs (conn.ConnectionString, schemaType.Name)
udttsPerSchema.Add( schemaType.Name, udttsRoot)
schemaType.AddMember udttsRoot
do //Units of measure
let xs = this.UnitsOfMeasure (conn.ConnectionString, schemaType.Name)
if not (List.isEmpty xs)
then
let uomRoot = ProvidedTypeDefinition("Units of Measure", Some typeof<obj>)
uomRoot.AddMembers xs
uomPerSchema.Add( schemaType.Name, xs)
schemaType.AddMember uomRoot
for schemaType in schemas do
schemaType.AddMembersDelayed <| fun() ->
[
let routines = this.Routines(conn, schemaType.Name, udttsPerSchema, resultType, designTimeConnectionString, useReturnValue, uomPerSchema)
routines |> List.iter tagProvidedType
yield! routines
yield this.Tables(conn, schemaType.Name, designTimeConnectionString, tagProvidedType)
]
let commands = ProvidedTypeDefinition( "Commands", None)
databaseRootType.AddMember commands
this.AddCreateCommandMethod(conn, designTimeConnectionString, databaseRootType, udttsPerSchema, commands, connectionStringOrName, uomPerSchema)
databaseRootType

I guess the least painful way for you to troubleshoot this would be to clone this repository, and add a test that would run the same code as highlighted above, outside of a provider context, and connects to your development database.

This way we'd know how long that takes in your environment and hopefully figure some testing / fix ideas around the issue.

https://fsprojects.github.io/SQLProvider/ provider has "Lazy schema exploration", @pezipink would you be able to hint if similar technique could be applied to the snipped above?

@smoothdeveloper
Copy link
Collaborator

@sharno I'm closing this issue, if you need to get more help, don't hesitate to open it again or create a new one.

Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants