Once you have tested a BAQ you can run “Get Query Execution Plan” from the actions menu.
This can then be opened with SSMS.
I had a to debug a finance BAQ that running out of memory and failing and used this. Turned out the SQL being executed bore no resemblance to what shown in the BAQ designer “Query Phrase” window!
I copied the query phrase directly to SSMS and it ran in a few msec 
Thanks @richardh and welcome to our Forum,
i have done what you suggested, the outcome file will certainly help analysing the performance of the BAQ query, but i need the SQL code to use it in different App as well as here, could you please explain how can be generated/viewed from this file?
I did the former already a few days ago. However, the execution plan is a graphical representation of the executed SQL. Is there a way to extract the requested query vs. the executing query? An Oracle database can track the submitted query and the executing query.
My end Goal is to convert the BAQ query to SQLserver SQL query and define a database View that can be accessed by an ASP application, SharePoint App, or MS Power BI.
Thank you all for your prompt response and suggestions.
You need to develop and test the SQL first. Then you can create a database View if you have DBA privileges. You will need to create a schema to contain all your custom views and to isolate them from Epicor views. Additionally, you will need to grant specific access to the view. The command for creating a view is below:
USE [Epicor10]
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [SCHEMA].[VIEW_NAME] AS
Insert your SQL statement here...….
GO
`Isn’t that already solved by calling the REST API BAQSvc? You can call the BAQ query from any client consuming the rest api service.
Carlos
We are currently running 10.1.400.38, is REST API BAQSvc available for this release? In the meantime, I need to create the VIEW for these existing applications. Thanks for your idea and suggestion. I will need to test this in the very near feature, and after I attend my scheduled training on REST at Insights 2019.
I had hoped REST would be useful for Power BI. So far I haven’t found a single situation where it isn’t too slow and unresponsive.
If you have any tips to make it work effectively, please share!
it much easier to clear the view from the SQL code of BAQ Phrase the way i recommend then run it straight a way in MSSM Epicor database, and will be ready for all SQL based applications
@asmar Oops, sorry, I didn’t see the version you working with. I believe REST is available since 10.1.500/600, so the suggestion may not apply to you, however, I believe you should still consider that once you or your customer upgrades to the latest version, they will have that feature available.
@dhewi Yes, I have seen PowerBI receive some Timeout errors when calling BAQs via the REST service too, but I believe that was mainly because the BAQs used were bringing back too much data, too many columns back to the client. Have you get those performance issues with smaller BAQs as well? My tip is to filter and reduce as much as possible the data those BAQs need to bring back to client tool.
To answer the original question, LINQ generated SQL is different from high performance written SQL and I haven’t ever seen anything to convert the two.
@dhewi Look at using Dataflows to load the BAQ data to your SSAS instance for PowerBI. You can now mix SSAS summaries with direct queries now by using a Composite Model. When a user drills down to report details PowerBI will run the Direct Query REST call to return just that detail and it doesn’t have to be loaded into the model.
Well, the problem with Power BI is that it’s most useful when there’s a massive amount of data, and that’s where REST seems to falter, as you say.
I remain hopeful we can make good use of REST for other projects we have on our agenda, and I think it’s a great thing to have available. I’ve just been disappointed in it for this particular purpose and have gone back to direct SQL Server query. I was hoping there might be something I’d missed!
@John_Mitchell - thanks. I think that’s something we’ll get to in due time.
@asmar you can either do that, or if you have full freedom on the SQL Server, you could create a separate database where all the custom SQL objects reside, and make direct usage of the Epicor db on your code, example:
USE [YOUR_DB]
GO
SELECT * FROM [EpicorDemo].Erp.GLJrnDtl
That way you don’t create custom objects on the Epicor DB.
Your custom Apps create direct connections to this custom DB instead of Epicor DB.
It sounds like you’re on a similar journey to us, which is interesting. From what we’ve found, BAQ to server view is an unhelpful detour, but it’s very interesting to see what others have to say and I’ll also be keen to see how it develops.
In our experience, if something is best done outside of Epicor, BAQs become a bad fit. We haven’t found a good substitute for knowing what you want out of the data and crafting a SQL query to get exactly that. As @carlosqt advises, we keep all that in a separate DB on the same instance, and the more complicated constructions rebuild themselves overnight - some summarised data can then be accessed in seconds instead of minutes that way. That DB then holds the data in the form best suited to the non-Epicor app that needs it, and the permissions etc are totally separate. In some cases we even use the same data in External BAQs back into Epicor.
I agree with your suggestion and method.
Probably the best approach for Epicor ERP customers On-Premise.
For Epicor Cloud ERP customers, they need to rely on BAQ + REST, or another complicate replication/sync data between Cloud and On-Premise.
So, even though this is a little old, I will add my 2 cents worth for future searchers. Not sure how much rewriting you are having to do, or if it is because we are on 10.2.300, but thus far I have not had any issues cutting and pasting from the BAQ Query Phrase window to SQL SSMS Query window with just a few edits needed. I have thus far used this method to convert 15 BAQs into SQL Stored Procedures to be used by .NET programs and Windows tasks for sending out daily, weekly, and monthly spreadsheets. usually the edits are the same from one query to the next. Mainly, you will have to add joins for the UD extended tables and then change the references in the SELECT statements, but for the most part, SQL SSMS shows you the parts it can’t interpret, you fix those, and you’re ready to rock and roll.
You don’t need to add extra joins for the UD tables.
Simply change the Erp. prefix to dbo.
Even easier, thanks!
or remove the Erp. prefix will work as well (if the table has any UD fields)