As a developer, you want to make sure everything going into production is as optimized and as fast as possible. There's nothing worse than bloating a system with poorly executing queries, but when dealing with potentially hundreds of BAQs in your system analyzing their performance can be a daunting task. One thing that can help is to automate the generation of their execution plans, setting you up with an XML file that can then be separately parsed and programmatically reviewed.
/// <source> wesbos </source>
public void DadJoke()
{
Q: What did the server say to his client who was having a bad day?
A: Everything's going to be 200
}
First step is to determine how you'd like to implement this program. It can be done either in Epicor itself (maybe in a new menu calling a BPM or even an updateable BAQ), or it could be run as a standalone application. For this exercise, we'll focus on the ladder and build it in a standalone executable.
Create a new Visual Studio Project
- Open Visual Studio (this was written using VS Enterprise 2015) and select New Project.
- Choose the Console Application template and name your project "Generate BAQ Execution Plans".
- Change the .NET Framework to 4.7.2 and click OK.
Add Project References and Using Statements
- You'll need several Epicor .dll references added to your project. These will be found in your App Servers installation folder and in your local client install folder.
Ice.Core.Session (Client)
Ice.Contract.BO.DynamicQuery (Client)
Epicor.ServiceModel (Server. Located in the "../Server/Bin" directory)
- Add the following Using Statement. This will be used to save the file at the end.
using System.IO;
Create a user Session
Because we don't have access to a Db Context to generate our Business Objects, we will Epicor's WCF Service to build the objects using a Session. Be cautious if your licensing is limited, as this will consume one while running.
- Add a global variable to your Program class for your session
private static Ice.Core.Session session;- In your Main method, let's create our session. This does require login credentials, so best to abstract them in some way. In our case we've stored them in the App config.
- To instantiate your session, you'll also need the location of your local sys config file.
static void Main(string[] args)
{
string userID = ConfigurationManager.AppSettings["UserName"];
string password = ConfigurationManager.AppSettings["Password"];
session = new Ice.Core.Session(userID, password, Ice.Core.Session.LicenseType.EnterpriseProcessing,@"C:\Epicor\Epicor10.2.400\Client\config\EpicorTraining.sysconfig");
}Build your Business Object
Now that we have a session we can instantiate our business objects. We'll be leveraging Epicor's BAQ objects to do all the heavy lifting for us.
- Add another Program class variable for the BO.
private static Ice.Proxy.BO.DynamicQueryImpl baqBOService;- After instantiating your session in your Main method, instantiate the BO.
baqBOService = Ice.Lib.Framework.WCFServiceSupport.CreateImpl<Ice.Proxy.BO.DynamicQueryImpl>(session, Epicor.ServiceModel.Channels.ImplBase<Ice.Contracts.DynamicQuerySvcContract>.UriPath);Get the Query Parameters
Now that our objects are setup, we can start calling BO Methods.
If a query requires any parameters to be run, we will query them out and ask the user for input on their values. You could just as well store these values off in another file to be pulled in as needed or hard code them into the App.
For this example, we'll also be using the first input of the startup args as our BAQ ID that we'll run this against.
- In the Main method, let's get the list or parameters that we will use as a template. We'll loop through these and ask the user for input.
Ice.BO.QueryExecutionDataSet templateParams = baqBOService.GetQueryExecutionParametersByID(args.FirstOrDefault());- Now lets build out a the dataset that will hold the users input.
Ice.BO.QueryExecutionDataSet baqParams = baqBOService.GetQueryExecutionParametersByID(args.FirstOrDefault());
baqParams.ExecutionParameter.Clear();- Now let's loop through the templateParams and ask the user for input. This also displays the value type to the user to assist.
foreach(Ice.BO.QueryExecutionDataSet.ExecutionParameterRow param in templateParams.ExecutionParameter)
{
Console.WriteLine("\nPlease provide values for the following execution
paramters:");
Console.Write(" Param: " + param.ParameterID + " (type: " +
param.ValueType + "): ");
string paramValue = Console.ReadLine();
baqParams.ExecutionParameter.AddExecutionParameterRow(
param.ParameterID, paramValue, param.ValueType, false, Guid.NewGuid(),
"A");
}- Lastly, we'll add an additional execution parameter for to hold the execution token. This guid will be used to get the execution plan back from the SQL Server later on.
Guid executionToken = Guid.NewGuid();
baqParams.ExecutionSetting.AddExecutionSettingRow("TestExecutionToken", executionToken.ToString(), Guid.Empty, "A");Execute the BAQ
The BAQ must first be executed in order for us to pull the Execution Plan back from the sever.
- Create the BAQs query dataset. This will feed the execution method with additional BAQ data.
Ice.BO.DynamicQueryDataSet queryDS = baqBOService.GetByID(args.FirstOrDefault());- Execute the BAQ.
baqBOService.Execute(queryDS, baqParams);Finally, get and save the Execution Plan
Now that the server has executed the BAQ, we can extract the execution plan.
- Generate the execution plan using the token we previously generated. We store the Execution Plan as a string, but it's in an XML format that can be parsed separately.
string executionPlan = baqBOService.GetQueryExecutionPlan(executionToken, "");- Save the plan by first getting our current location.
string fullPath = System.Reflection.Assembly.GetExecutingAssembly().Location;- Extract the current Directory from the full path
string currentDir = System.IO.Path.GetDirectoryName(fullPath);- Build the final output path. This is how we'll also name our Execution Plan file using the BAQ ID.
string fullLogPath = Path.Combine(currentDir, args.FirstOrDefault() + "-ExecutionPlan.sqlplan");- Save the file.
StreamWriter sw = new StreamWriter(fullLogPath, true, Encoding.ASCII);
sw.WriteLine(executionPlan);
sw.Close(); Time to run it!
- Build your project, then using a command prompt navigate to your projects ".../bin/Debug" folder.
- Execute your program. The "testbaq" is my BAQ ID and should be replaced with yours.
- If your BAQ has parameters, you'll be asked for input.
- Once it's done running, you'll now see your Execution Plan in the same Debug folder as the exe.
Thoughts on next steps...
So where from here? Well, the options are endless. Now that you have the execution plan (which at the end of the day is just an XMl file), you could parse out the XML to look for any information you'd like. One option could be to look for missing query indexes or other SQL Server messages that could then be logged out and acted on.
Whether you implement this upstream into your development process to validate new BAQs, use it in regression testing, or post install server health checks, systematically getting the Execution Plans for your BAQs could be immensely useful!
Happy coding!
- Matt
Comments