E10: Tracing a bad BAQ

Yahoo Archive
1
Thanks Miguel.

It’s odd because in E10, it defaults to BTCustNum.
I working with support now and then are not seeing any difference between OrderHed.CustNum and OrderHed.BTNum.

I’m hoping they come up with something.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Tuesday, June 23, 2015 12:38 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: E10: Tracing a bad BAQ


When I created the query in E9, it naturally linked to Orderhed.CustNum and Customer.CustNum.

I pulled data as well and BTCustNum always was the same value as CustNum. Changing the relationship to BTCUstNum, it did give a slight performance difference (slower). Either way I link it, the same amount of records displayed.

Hope that helps.

Miguel A. Santillan
Compass Manufacturing Systems

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, June 22, 2015 1:26 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: E10: Tracing a bad BAQ



Thanks Jim.

I had called support but haven’t had any luck yet.

I did some testing and the issue appears to be with the join between customer and orderhed.
The BAQ designer will automatically link Customer.CustNum to OrderHed.BTCustNum, which is what I want but this appears to be the problem.
If I toggle it to OrderHed.CustNum, the BAQ runs in less than a second.

Any ideas if there is something I can do on my own to fix this?



[cid:f2fd2d.png@980d6bc4.43b1a29c]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>

From: vantage@yahoogroups.com<mailto:vantage@yahoogroups.com> [mailto:vantage@yahoogroups.com]
Sent: Monday, June 22, 2015 4:24 PM
To: vantage@yahoogroups.com<mailto:vantage@yahoogroups.com>
Subject: RE: [Vantage] Re: E10: Tracing a bad BAQ


The joins appear to be good and nothing crazy jumps out.

One is a view that has the standard and the UD tables in it so that it looks like a single table, and the other just has the standard orderhed table as you discovered. I have not seen a problem with retrieving things from the view over the standard table so don't have any specific advice. Might do a simple query on just erp/dbo.orderhed and see how the performance is to the view and to the standard table.

If it is way out of whack a call to Epicor may be in order, they may have some tuning/rebuild advice.

Jim Kinneman
Encompass Solutions, Inc.


[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
2
Hello,

I have a BAQ that takes 2.5 minutes to return 500 rows with information primarily related to sales orders.
This seems a bit extreme giving the speed of other BAQs that we have seen.

I used the SQL Server Profiler (my first time) and can see that it did 114 million reads, which for 500 records seem off.

Where I’m not yet a SQL Server expert, what other SQL tools are there to figure out why there are so many reads?

[cid:3460cd.png@93852a50.47a1be19]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>



[Non-text portions of this message have been removed]
3
Would you post what tables you are using and how you are joining them?  Might just be an incomplete or bad join.

Jim Kinneman
Encompass Solutions, Inc.
4
Here is the SQL from the BAQ.
Interesting to note that when I run this in Management Studio, with a slight change from Erp.OrderHed to dbo.OrderHed, because of the UD fields, it runs under one second.

select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
[OrderRel].[ReqDate] as [OrderRel_ReqDate],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
[OrderRel].[IUM] as [OrderRel_IUM],
[OrderRel].[PONum] as [OrderRel_PONum],
[PORel].[POLine] as [PORel_POLine],
[PORel].[PORelNum] as [PORel_PORelNum],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[Name] as [Vendor_Name],
[OrderHed].[CheckBox03] as [OrderHed_CheckBox03],
[OrderHed].[OrderComment] as [OrderHed_OrderComment],
[OrderDtl].[OrderComment] as [OrderDtl_OrderComment],
[OrderRel].[WarehouseCode] as [OrderRel_WarehouseCode],
[OrderHed].[CheckBox20] as [OrderHed_CheckBox20]
from Erp.Customer as Customer
inner join Erp.OrderHed as OrderHed on
Customer.Company = OrderHed.Company
And
Customer.CustNum = OrderHed.BTCustNum
and ( OrderHed.OpenOrder = 1 and OrderHed.OrderHeld = 0 and (OrderHed.CreditOverride = 1 or OrderHed.CreditOverride = Customer.CreditHold ) )

inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
And
OrderHed.OrderNum = OrderDtl.OrderNum
and ( OrderDtl.OpenLine = 1 )

inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
And
OrderDtl.OrderNum = OrderRel.OrderNum
And
OrderDtl.OrderLine = OrderRel.OrderLine
and ( OrderRel.OpenRelease = 1 and OrderRel.BuyToOrder = 1 )

left outer join Erp.PORel as PORel on
OrderRel.Company = PORel.Company
And
OrderRel.PONum = PORel.PONum
And
OrderRel.POLine = PORel.POLine
And
OrderRel.PORelNum = PORel.PORelNum

left outer join Erp.POHeader as POHeader on
PORel.Company = POHeader.Company
And
PORel.PONum = POHeader.PONum

left outer join Erp.Vendor as Vendor on
OrderRel.Company = Vendor.Company
And
OrderRel.VendorNum = Vendor.VendorNum


[cid:d2fd2b.png@de662880.448e4119]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, June 22, 2015 3:23 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10: Tracing a bad BAQ



Would you post what tables you are using and how you are joining them? Might just be an incomplete or bad join.

Jim Kinneman
Encompass Solutions, Inc.



[Non-text portions of this message have been removed]
5
The joins appear to be good and nothing crazy jumps out.

One is a view that has the standard and the UD tables in it so that it looks like a single table, and the other just has the standard orderhed table as you discovered.  I have not seen a problem with retrieving things from the view over the standard table so don't have any specific advice.  Might do a simple query on just erp/dbo.orderhed and see how the performance is to the view and to the standard table. 

If it is way out of whack a call to Epicor may be in order, they may have some tuning/rebuild advice.  

Jim Kinneman
Encompass Solutions, Inc.
6
Thanks Jim.

I had called support but haven’t had any luck yet.

I did some testing and the issue appears to be with the join between customer and orderhed.
The BAQ designer will automatically link Customer.CustNum to OrderHed.BTCustNum, which is what I want but this appears to be the problem.
If I toggle it to OrderHed.CustNum, the BAQ runs in less than a second.

Any ideas if there is something I can do on my own to fix this?



[cid:f2fd2d.png@980d6bc4.43b1a29c]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, June 22, 2015 4:24 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: E10: Tracing a bad BAQ


The joins appear to be good and nothing crazy jumps out.

One is a view that has the standard and the UD tables in it so that it looks like a single table, and the other just has the standard orderhed table as you discovered. I have not seen a problem with retrieving things from the view over the standard table so don't have any specific advice. Might do a simple query on just erp/dbo.orderhed and see how the performance is to the view and to the standard table.

If it is way out of whack a call to Epicor may be in order, they may have some tuning/rebuild advice.

Jim Kinneman
Encompass Solutions, Inc.



[Non-text portions of this message have been removed]
7

When I created the query in E9, it naturally linked to Orderhed.CustNum and Customer.CustNum.

 

I pulled data as well and BTCustNum always was the same value as CustNum.  Changing the relationship to BTCUstNum, it  did give a slight performance difference (slower).  Either way I link it, the same amount of records displayed.

 

Hope that helps.

 

Miguel A. Santillan

Compass Manufacturing Systems

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, June 22, 2015 1:26 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: E10: Tracing a bad BAQ

 

 

Thanks Jim.

I had called support but haven’t had any luck yet.

I did some testing and the issue appears to be with the join between customer and orderhed.
The BAQ designer will automatically link Customer.CustNum to OrderHed.BTCustNum, which is what I want but this appears to be the problem.
If I toggle it to OrderHed.CustNum, the BAQ runs in less than a second.

Any ideas if there is something I can do on my own to fix this?



[cid:f2fd2d.png@980d6bc4.43b1a29c]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg]
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png]<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png] <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png] <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png] <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png] <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png] <http://matsinc.com/blog>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Monday, June 22, 2015 4:24 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: E10: Tracing a bad BAQ


The joins appear to be good and nothing crazy jumps out.

One is a view that has the standard and the UD tables in it so that it looks like a single table, and the other just has the standard orderhed table as you discovered. I have not seen a problem with retrieving things from the view over the standard table so don't have any specific advice. Might do a simple query on just erp/dbo.orderhed and see how the performance is to the view and to the standard table.

If it is way out of whack a call to Epicor may be in order, they may have some tuning/rebuild advice.

Jim Kinneman
Encompass Solutions, Inc.


[Non-text portions of this message have been removed]