Vantage 803.xx: Query speed

Yahoo Archive
1
Please...we all have brain farts! ;)

No problem!

Manasa












From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Dan Godfrey
Sent: Thursday, July 25, 2013 11:30 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 803.xx: Query speed



My bad. sorry for the brain lapse. After thinking about for oh 2 seconds after I sent the email I realized where it is used. It is still relatively morning on the West Coast.

Dan Godfrey
(805) 389-1935 x 251

________________________________
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Manasa Reddy
Sent: Thursday, July 25, 2013 8:51 AM
To: 'vantage@yahoogroups.com<mailto:%26%2339%3Bvantage%40yahoogroups.com>'
Subject: RE: [Vantage] Vantage 803.xx: Query speed

The sales module has always used this table....I remember it back on version 4...

Manasa

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Dan Godfrey
Sent: Thursday, July 25, 2013 10:49 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

WOW!! Thank you. Is there a module that I am not aware of that uses this BO (BookOrd)?

Dan Godfrey
(805) 389-1935 x 251

________________________________
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Thursday, July 25, 2013 4:42 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

Dan -

Did you check out the BookDtl table? This table stores the order bookings for the system.

Thank you in advance,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin@...<mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin%40codabears.com>>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8902.1D18AF60<mailto:image001.jpg%4001CE8902.1D18AF60><mailto:image001.jpg%4001CE8902.1D18AF60><mailto:image001.jpg%4001CE8902.1D18AF60><mailto:image001.jpg%4001CE8902.1D18AF60>]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Dan Godfrey
Sent: Wednesday, July 24, 2013 1:24 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

I added in the Company = Constants.CurComp and still the same results. Am I just going to have to accept this speed because I need the filter on the Date01. It is Booking Date since Vantage did not have a booking date on OrderDtl table.

Dan Godfrey
(805) 389-1935 x 251

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Wednesday, July 24, 2013 10:42 AM
To: Yahoo Vantage Group
Subject: Re: [Vantage] Vantage 803.xx: Query speed

You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484

----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...<mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM

I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.

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

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/><http://groups.yahoo.com/group/vantage/files/><http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

CONFIDENTIALITY NOTICE: The data attached/enclosed may contain information (including technology and technical data) which is subject to the U.S. International Traffic in Arms Regulations (ITAR) or Export Administration Regulations (EAR). This information may not be exported, released, or disclosed to foreign persons either inside or outside the United States without first obtaining the proper U.S. export license or written authorization. In addition, the information and articles described herein are either patented or proprietary, and the copying or reproduction thereof is prohibited without ADVANCED Motion Controls prior written consent. If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Thank you for your compliance.

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

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

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

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



[Non-text portions of this message have been removed]
2
I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.


So I am hoping that a query expert, or anyone that will answer, might have an answer for this.
3
You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484


----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM





I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.





[Non-text portions of this message have been removed]
4
I added in the Company = Constants.CurComp and still the same results. Am I just going to have to accept this speed because I need the filter on the Date01. It is Booking Date since Vantage did not have a booking date on OrderDtl table.


Dan Godfrey
(805) 389-1935 x 251

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Calvin Dekker
Sent: Wednesday, July 24, 2013 10:42 AM
To: Yahoo Vantage Group
Subject: Re: [Vantage] Vantage 803.xx: Query speed

You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484


----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM





I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.





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



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links



CONFIDENTIALITY NOTICE: The data attached/enclosed may contain information (including technology and technical data) which is subject to the U.S. International Traffic in Arms Regulations (ITAR) or Export Administration Regulations (EAR). This information may not be exported, released, or disclosed to foreign persons either inside or outside the United States without first obtaining the proper U.S. export license or written authorization. In addition, the information and articles described herein are either patented or proprietary, and the copying or reproduction thereof is prohibited without ADVANCED Motion Controls prior written consent. If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Thank you for your compliance.
5
Dan -

Did you check out the BookDtl table? This table stores the order bookings for the system.

Thank you in advance,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin@...>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8902.1D18AF60]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Dan Godfrey
Sent: Wednesday, July 24, 2013 1:24 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 803.xx: Query speed



I added in the Company = Constants.CurComp and still the same results. Am I just going to have to accept this speed because I need the filter on the Date01. It is Booking Date since Vantage did not have a booking date on OrderDtl table.

Dan Godfrey
(805) 389-1935 x 251

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Wednesday, July 24, 2013 10:42 AM
To: Yahoo Vantage Group
Subject: Re: [Vantage] Vantage 803.xx: Query speed

You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484

----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...<mailto:dgodfrey%40a-m-c.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM

I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.

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

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

CONFIDENTIALITY NOTICE: The data attached/enclosed may contain information (including technology and technical data) which is subject to the U.S. International Traffic in Arms Regulations (ITAR) or Export Administration Regulations (EAR). This information may not be exported, released, or disclosed to foreign persons either inside or outside the United States without first obtaining the proper U.S. export license or written authorization. In addition, the information and articles described herein are either patented or proprietary, and the copying or reproduction thereof is prohibited without ADVANCED Motion Controls prior written consent. If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Thank you for your compliance.



[Non-text portions of this message have been removed]
6
WOW!! Thank you. Is there a module that I am not aware of that uses this BO (BookOrd)?

Dan Godfrey
(805) 389-1935 x 251


________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Calvin Dekker
Sent: Thursday, July 25, 2013 4:42 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 803.xx: Query speed



Dan -

Did you check out the BookDtl table? This table stores the order bookings for the system.

Thank you in advance,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin%40codabears.com><mailto:calvin@...<mailto:calvin%40codabears.com>>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8902.1D18AF60<mailto:image001.jpg%4001CE8902.1D18AF60>]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Dan Godfrey
Sent: Wednesday, July 24, 2013 1:24 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

I added in the Company = Constants.CurComp and still the same results. Am I just going to have to accept this speed because I need the filter on the Date01. It is Booking Date since Vantage did not have a booking date on OrderDtl table.

Dan Godfrey
(805) 389-1935 x 251

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Wednesday, July 24, 2013 10:42 AM
To: Yahoo Vantage Group
Subject: Re: [Vantage] Vantage 803.xx: Query speed

You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484

----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...<mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM

I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.

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

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

CONFIDENTIALITY NOTICE: The data attached/enclosed may contain information (including technology and technical data) which is subject to the U.S. International Traffic in Arms Regulations (ITAR) or Export Administration Regulations (EAR). This information may not be exported, released, or disclosed to foreign persons either inside or outside the United States without first obtaining the proper U.S. export license or written authorization. In addition, the information and articles described herein are either patented or proprietary, and the copying or reproduction thereof is prohibited without ADVANCED Motion Controls prior written consent. If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Thank you for your compliance.

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





[Non-text portions of this message have been removed]
7
The sales module has always used this table....I remember it back on version 4...

Manasa










From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Dan Godfrey
Sent: Thursday, July 25, 2013 10:49 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 803.xx: Query speed



WOW!! Thank you. Is there a module that I am not aware of that uses this BO (BookOrd)?

Dan Godfrey
(805) 389-1935 x 251

________________________________
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Thursday, July 25, 2013 4:42 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

Dan -

Did you check out the BookDtl table? This table stores the order bookings for the system.

Thank you in advance,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin@...<mailto:calvin%40codabears.com><mailto:calvin%40codabears.com>>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8902.1D18AF60<mailto:image001.jpg%4001CE8902.1D18AF60><mailto:image001.jpg%4001CE8902.1D18AF60>]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Dan Godfrey
Sent: Wednesday, July 24, 2013 1:24 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

I added in the Company = Constants.CurComp and still the same results. Am I just going to have to accept this speed because I need the filter on the Date01. It is Booking Date since Vantage did not have a booking date on OrderDtl table.

Dan Godfrey
(805) 389-1935 x 251

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Wednesday, July 24, 2013 10:42 AM
To: Yahoo Vantage Group
Subject: Re: [Vantage] Vantage 803.xx: Query speed

You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484

----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...<mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM

I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.

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

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/><http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

CONFIDENTIALITY NOTICE: The data attached/enclosed may contain information (including technology and technical data) which is subject to the U.S. International Traffic in Arms Regulations (ITAR) or Export Administration Regulations (EAR). This information may not be exported, released, or disclosed to foreign persons either inside or outside the United States without first obtaining the proper U.S. export license or written authorization. In addition, the information and articles described herein are either patented or proprietary, and the copying or reproduction thereof is prohibited without ADVANCED Motion Controls prior written consent. If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Thank you for your compliance.

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

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



[Non-text portions of this message have been removed]
8
My bad. sorry for the brain lapse. After thinking about for oh 2 seconds after I sent the email I realized where it is used. It is still relatively morning on the West Coast.

Dan Godfrey
(805) 389-1935 x 251


________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Manasa Reddy
Sent: Thursday, July 25, 2013 8:51 AM
To: 'vantage@yahoogroups.com'
Subject: RE: [Vantage] Vantage 803.xx: Query speed



The sales module has always used this table....I remember it back on version 4...

Manasa

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Dan Godfrey
Sent: Thursday, July 25, 2013 10:49 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

WOW!! Thank you. Is there a module that I am not aware of that uses this BO (BookOrd)?

Dan Godfrey
(805) 389-1935 x 251

________________________________
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Thursday, July 25, 2013 4:42 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

Dan -

Did you check out the BookDtl table? This table stores the order bookings for the system.

Thank you in advance,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin@...<mailto:calvin%40codabears.com><mailto:calvin%40codabears.com><mailto:calvin%40codabears.com>>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8902.1D18AF60<mailto:image001.jpg%4001CE8902.1D18AF60><mailto:image001.jpg%4001CE8902.1D18AF60><mailto:image001.jpg%4001CE8902.1D18AF60>]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Dan Godfrey
Sent: Wednesday, July 24, 2013 1:24 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Vantage 803.xx: Query speed

I added in the Company = Constants.CurComp and still the same results. Am I just going to have to accept this speed because I need the filter on the Date01. It is Booking Date since Vantage did not have a booking date on OrderDtl table.

Dan Godfrey
(805) 389-1935 x 251

-----Original Message-----
From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Calvin Dekker
Sent: Wednesday, July 24, 2013 10:42 AM
To: Yahoo Vantage Group
Subject: Re: [Vantage] Vantage 803.xx: Query speed

You're filtering on a non-indexed field (date01) on the orderdtl table.

HTH
Calvin Dekker
CodaBears, Inc.
630-672-7688. Ext. 1484

----- Reply message -----
From: "dgodfrey_amc" <dgodfrey@...<mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com><mailto:dgodfrey%40a-m-c.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>>
Subject: [Vantage] Vantage 803.xx: Query speed
Date: Wed, Jul 24, 2013 12:29 PM

I created a simple BAQ with a filter to reduce the data to get about 5000+ records. I added the BAQ to a dashboard and set the dashboard to Auto-Refresh. When I activate the dashboard, it takes about 15 seconds to refresh. The dashboard has two grids from the query. Here is the query:

for each OrderDtl where ( OrderDtl.Date01 >= TODAY + -365) no-lock , each OrderHed where (OrderDtl.Company = OrderHed.Company and OrderDtl.OrderNum = OrderHed.OrderNum) no-lock , each Customer where (OrderDtl.Company = Customer.Company and OrderDtl.CustNum = Customer.CustNum) no-lock .

I thought that 15 seconds was long, so I flipped the query making the OrderHed the parent to OrderDtl and Customer. This query consistently took 5 seconds longer.

Does anyone know why going from the head to the detail table is slower? My thought was that getting a header record for EVERY detail would unnecessarily produce multiple header records. As oppose to 1 header record for many detail record (which I am already retrieving in the other query). I don't know, I just thought it would have been quicker going from the header to the detail.

So I am hoping that a query expert, or anyone that will answer, might have an answer for this.

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

------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.<http://groups.yahoo.com/group/vantage/files/><http://groups.yahoo.com/group/vantage/files/>
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links

CONFIDENTIALITY NOTICE: The data attached/enclosed may contain information (including technology and technical data) which is subject to the U.S. International Traffic in Arms Regulations (ITAR) or Export Administration Regulations (EAR). This information may not be exported, released, or disclosed to foreign persons either inside or outside the United States without first obtaining the proper U.S. export license or written authorization. In addition, the information and articles described herein are either patented or proprietary, and the copying or reproduction thereof is prohibited without ADVANCED Motion Controls prior written consent. If you are not the intended recipient (or have received this e-mail in error), please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Thank you for your compliance.

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

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

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





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