How can I get a SQL stored procedure with SMO more efficiently?

Question

I am trying to get DefaultValue of all parameters in StoredProcedure. My application is built on C # .NET, access to Microsoft SQL 2008 database.

I use SqlCommandBuilder.DeriveParameters to get most of the parameter information quite efficiently, however it does not return the value "DefaultValue" of the parameter, so I turned to SMO to get this specific property.

Here is my current code:

Server svr = new Server(new ServerConnection(new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))); StoredProcedure sp = svr.Databases["MyDatabase"].StoredProcedures["mySproc", "myScheme"]; svr.SetDefaultInitFields(typeof(StoredProcedureParameter), "Name"); svr.SetDefaultInitFields(typeof(StoredProcedureParameter), "DefaultValue"); Dictionary<string, string> defaultValueLookup = new Dictionary<string, string>(); foreach (StoredProcedureParameter parameter in sp.Parameters) { string defaultValue = parameter.DefaultValue; string parameterName = parameter.Name; defaultValueLookup.Add(parameterName, defaultValue); } 

However, it is very slow, even after I added svr.SetDefaultInitFields optimization (which significantly improved the improvement by 10x).

Has anyone got further optimization ideas?

Answer 1

I had a familiar problem, and I found that if you use ...

 svr.SetDefaultInitFields(typeof(StoredProcedure), false) 

It is much faster. I assume that with any other options, it actually extracts everything, but if you turn it off, just get your parameters, the speed of increase is huge. Mine when from 5-6 seconds to 0.5 seconds for 10 steam. Still not quite perfect, but liveable.

EDIT

Since playing with this is a bit more, I found that typeof(StoredProcedure) level evaluation typeof(StoredProcedure) works best. In my tests, using the typeof(StoreedProcedureParameter) option typeof(StoreedProcedureParameter) was sequentially 1.5 seconds or so compared to the version of typeof(StoredProcedure) by 0.5 seconds.

I would be wondering if anyone would tell me why?

Answer 2

You can use the parser to extract default values ​​from a stored procedure, for example. Microsoft.Data.Schema.ScriptDom .