9.05.607B TranGLC to PartTran

We had some GL reports written off SQL Views in 8.03. When I went to 9.05 I had to remap them - very ugly since it also included changing to segments too. Putting cast statements for linking key fields in a SQL view brings reports to a crawl! I ended switching them to stored procedures that create temp tables for linking. Originally I tried linking on BookID as part of the keying and found converted data doesn't have a book so none of the historical reporting was working. I'm just hoping that doesn't come back and bite me later.
Have fun!
Jenn


[Non-text portions of this message have been removed]
Here's a unique issue we've just run into. The PartTran table is supposed
to tie to the TranGLC table on the following fields:
PartTran.SysDate = TranGLC.Key1
PartTran.SysTime = TranGLC.Key2
PartTran.TranNum = TranGLC.Key3

When I query each one of these tables individually I can easily find the
matching transactions but when I join them with the following query and set
a very limited date range the query just locks up my system.

for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' , each
PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2 =
PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
TranGLC.SegValue1 by TranGLC.SegValue2 .

I noticed that the date formats between PartTran.SysDate and TranGLC.Key1
don't display the same in the 2 tables. TranGLC.Key1 displays 04/20/12 and
PartTran.SysDate displays 04/20/2012. Could this be causing the issue? Any
way around it?

Thanks,
John Kreger


[Non-text portions of this message have been removed]
Welcome to TranGLC, in my opinion the biggest mistake Epicor has ever made. All of the key fields in TranGLC are character fields. Therefore if you try to join them to tables which contain date, numeric or other type key field records you will get errors (using ODBC). If you use BAQ, Epicor handles the field type discrepancies for you. I have not discovered a way to join different type fields using standard SQL ODBC connects in products like Crystal. If someone else has figured this out I would love to hear about it.

Keith Mailloux
Ferguson Perforating
(401)941-8876 Ext 2238


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of John Kreger
Sent: Thursday, April 26, 2012 11:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] 9.05.607B TranGLC to PartTran



Here's a unique issue we've just run into. The PartTran table is supposed
to tie to the TranGLC table on the following fields:
PartTran.SysDate = TranGLC.Key1
PartTran.SysTime = TranGLC.Key2
PartTran.TranNum = TranGLC.Key3

When I query each one of these tables individually I can easily find the
matching transactions but when I join them with the following query and set
a very limited date range the query just locks up my system.

for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' , each
PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2 =
PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
TranGLC.SegValue1 by TranGLC.SegValue2 .

I noticed that the date formats between PartTran.SysDate and TranGLC.Key1
don't display the same in the 2 tables. TranGLC.Key1 displays 04/20/12 and
PartTran.SysDate displays 04/20/2012. Could this be causing the issue? Any
way around it?

Thanks,
John Kreger

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



[Non-text portions of this message have been removed]
If you have a SQL database, try making a SQL view casting the columns to
correct data types before joining. I experimented with this once, think
I had to make a subquery with RelatedToFile='PartTran' before casting to
prevent errors with non-PartTran related transactions.



Brian.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Keith Mailloux
Sent: Thursday, April 26, 2012 12:16 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] 9.05.607B TranGLC to PartTran





Welcome to TranGLC, in my opinion the biggest mistake Epicor has ever
made. All of the key fields in TranGLC are character fields. Therefore
if you try to join them to tables which contain date, numeric or other
type key field records you will get errors (using ODBC). If you use BAQ,
Epicor handles the field type discrepancies for you. I have not
discovered a way to join different type fields using standard SQL ODBC
connects in products like Crystal. If someone else has figured this out
I would love to hear about it.

Keith Mailloux
Ferguson Perforating
(401)941-8876 Ext 2238

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of John Kreger
Sent: Thursday, April 26, 2012 11:42 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] 9.05.607B TranGLC to PartTran

Here's a unique issue we've just run into. The PartTran table is
supposed
to tie to the TranGLC table on the following fields:
PartTran.SysDate = TranGLC.Key1
PartTran.SysTime = TranGLC.Key2
PartTran.TranNum = TranGLC.Key3

When I query each one of these tables individually I can easily find the
matching transactions but when I join them with the following query and
set
a very limited date range the query just locks up my system.

for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
each
PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2
=
PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
TranGLC.SegValue1 by TranGLC.SegValue2 .

I noticed that the date formats between PartTran.SysDate and
TranGLC.Key1
don't display the same in the 2 tables. TranGLC.Key1 displays 04/20/12
and
PartTran.SysDate displays 04/20/2012. Could this be causing the issue?
Any
way around it?

Thanks,
John Kreger

[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]
We are in Progress and attempting to do this in BAQ. I set a criteria on
the TranGLC table to only pull related to file PartTran transactions... It
doesn't seem to be working at all... I'm pulling one days worth of
transactions which when pulling either of the tables data by themselves
takes 2-3 seconds but when doing the join BAQ designer just sits there for
upsides of 20 minutes....

Any ides out there? Does anyone see an issue with my query I posted earlier?

for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
each
PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2
=
PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
TranGLC.SegValue1 by TranGLC.SegValue2

Thanks,
--John Kreger

On Thu, Apr 26, 2012 at 3:41 PM, Brian Roberts <broberts@...> wrote:

> **
>
>
> If you have a SQL database, try making a SQL view casting the columns to
> correct data types before joining. I experimented with this once, think
> I had to make a subquery with RelatedToFile='PartTran' before casting to
> prevent errors with non-PartTran related transactions.
>
> Brian.
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of Keith Mailloux
> Sent: Thursday, April 26, 2012 12:16 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] 9.05.607B TranGLC to PartTran
>
>
> Welcome to TranGLC, in my opinion the biggest mistake Epicor has ever
> made. All of the key fields in TranGLC are character fields. Therefore
> if you try to join them to tables which contain date, numeric or other
> type key field records you will get errors (using ODBC). If you use BAQ,
> Epicor handles the field type discrepancies for you. I have not
> discovered a way to join different type fields using standard SQL ODBC
> connects in products like Crystal. If someone else has figured this out
> I would love to hear about it.
>
> Keith Mailloux
> Ferguson Perforating
> (401)941-8876 Ext 2238
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
>
> Behalf Of John Kreger
> Sent: Thursday, April 26, 2012 11:42 AM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] 9.05.607B TranGLC to PartTran
>
> Here's a unique issue we've just run into. The PartTran table is
> supposed
> to tie to the TranGLC table on the following fields:
> PartTran.SysDate = TranGLC.Key1
> PartTran.SysTime = TranGLC.Key2
> PartTran.TranNum = TranGLC.Key3
>
> When I query each one of these tables individually I can easily find the
> matching transactions but when I join them with the following query and
> set
> a very limited date range the query just locks up my system.
>
> for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> each
> PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2
> =
> PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> TranGLC.SegValue1 by TranGLC.SegValue2 .
>
> I noticed that the date formats between PartTran.SysDate and
> TranGLC.Key1
> don't display the same in the 2 tables. TranGLC.Key1 displays 04/20/12
> and
> PartTran.SysDate displays 04/20/2012. Could this be causing the issue?
> Any
> way around it?
>
> Thanks,
> John Kreger
>
> [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]
Try making TranGLC.RelatedToFile = 'PartTran' your first criteria on TranGLC; not sure it will make a difference, but I have a similiar BAQ that only takes a minute or 2 to run so it may be worth a try.




--- In vantage@yahoogroups.com, John Kreger <lilj8069@...> wrote:
>
> We are in Progress and attempting to do this in BAQ. I set a criteria on
> the TranGLC table to only pull related to file PartTran transactions... It
> doesn't seem to be working at all... I'm pulling one days worth of
> transactions which when pulling either of the tables data by themselves
> takes 2-3 seconds but when doing the join BAQ designer just sits there for
> upsides of 20 minutes....
>
> Any ides out there? Does anyone see an issue with my query I posted earlier?
>
> for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> each
> PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2
> =
> PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> TranGLC.SegValue1 by TranGLC.SegValue2
>
> Thanks,
> --John Kreger
>
> On Thu, Apr 26, 2012 at 3:41 PM, Brian Roberts <broberts@...> wrote:
>
> > **
> >
> >
> > If you have a SQL database, try making a SQL view casting the columns to
> > correct data types before joining. I experimented with this once, think
> > I had to make a subquery with RelatedToFile='PartTran' before casting to
> > prevent errors with non-PartTran related transactions.
> >
> > Brian.
> >
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> > Of Keith Mailloux
> > Sent: Thursday, April 26, 2012 12:16 PM
> > To: vantage@yahoogroups.com
> > Subject: RE: [Vantage] 9.05.607B TranGLC to PartTran
> >
> >
> > Welcome to TranGLC, in my opinion the biggest mistake Epicor has ever
> > made. All of the key fields in TranGLC are character fields. Therefore
> > if you try to join them to tables which contain date, numeric or other
> > type key field records you will get errors (using ODBC). If you use BAQ,
> > Epicor handles the field type discrepancies for you. I have not
> > discovered a way to join different type fields using standard SQL ODBC
> > connects in products like Crystal. If someone else has figured this out
> > I would love to hear about it.
> >
> > Keith Mailloux
> > Ferguson Perforating
> > (401)941-8876 Ext 2238
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> >
> > Behalf Of John Kreger
> > Sent: Thursday, April 26, 2012 11:42 AM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] 9.05.607B TranGLC to PartTran
> >
> > Here's a unique issue we've just run into. The PartTran table is
> > supposed
> > to tie to the TranGLC table on the following fields:
> > PartTran.SysDate = TranGLC.Key1
> > PartTran.SysTime = TranGLC.Key2
> > PartTran.TranNum = TranGLC.Key3
> >
> > When I query each one of these tables individually I can easily find the
> > matching transactions but when I join them with the following query and
> > set
> > a very limited date range the query just locks up my system.
> >
> > for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> > TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> > TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> > each
> > PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and TranGLC.Key2
> > =
> > PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> > TranGLC.SegValue1 by TranGLC.SegValue2 .
> >
> > I noticed that the date formats between PartTran.SysDate and
> > TranGLC.Key1
> > don't display the same in the 2 tables. TranGLC.Key1 displays 04/20/12
> > and
> > PartTran.SysDate displays 04/20/2012. Could this be causing the issue?
> > Any
> > way around it?
> >
> > Thanks,
> > John Kreger
> >
> > [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]
>
Include the Company field in your join. Almost all indexes in Epicor
include Company, and using it can make a huge difference in performance.

Brian.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jean
Sent: Friday, April 27, 2012 9:12 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: 9.05.607B TranGLC to PartTran





Try making TranGLC.RelatedToFile = 'PartTran' your first criteria on
TranGLC; not sure it will make a difference, but I have a similiar BAQ
that only takes a minute or 2 to run so it may be worth a try.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , John
Kreger <lilj8069@...> wrote:
>
> We are in Progress and attempting to do this in BAQ. I set a criteria
on
> the TranGLC table to only pull related to file PartTran
transactions... It
> doesn't seem to be working at all... I'm pulling one days worth of
> transactions which when pulling either of the tables data by
themselves
> takes 2-3 seconds but when doing the join BAQ designer just sits there
for
> upsides of 20 minutes....
>
> Any ides out there? Does anyone see an issue with my query I posted
earlier?
>
> for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> each
> PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and
TranGLC.Key2
> =
> PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> TranGLC.SegValue1 by TranGLC.SegValue2
>
> Thanks,
> --John Kreger
>
> On Thu, Apr 26, 2012 at 3:41 PM, Brian Roberts <broberts@...> wrote:
>
> > **
> >
> >
> > If you have a SQL database, try making a SQL view casting the
columns to
> > correct data types before joining. I experimented with this once,
think
> > I had to make a subquery with RelatedToFile='PartTran' before
casting to
> > prevent errors with non-PartTran related transactions.
> >
> > Brian.
> >
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
> > Of Keith Mailloux
> > Sent: Thursday, April 26, 2012 12:16 PM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: RE: [Vantage] 9.05.607B TranGLC to PartTran
> >
> >
> > Welcome to TranGLC, in my opinion the biggest mistake Epicor has
ever
> > made. All of the key fields in TranGLC are character fields.
Therefore
> > if you try to join them to tables which contain date, numeric or
other
> > type key field records you will get errors (using ODBC). If you use
BAQ,
> > Epicor handles the field type discrepancies for you. I have not
> > discovered a way to join different type fields using standard SQL
ODBC
> > connects in products like Crystal. If someone else has figured this
out
> > I would love to hear about it.
> >
> > Keith Mailloux
> > Ferguson Perforating
> > (401)941-8876 Ext 2238
> >
> > 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 John Kreger
> > Sent: Thursday, April 26, 2012 11:42 AM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] 9.05.607B TranGLC to PartTran
> >
> > Here's a unique issue we've just run into. The PartTran table is
> > supposed
> > to tie to the TranGLC table on the following fields:
> > PartTran.SysDate = TranGLC.Key1
> > PartTran.SysTime = TranGLC.Key2
> > PartTran.TranNum = TranGLC.Key3
> >
> > When I query each one of these tables individually I can easily find
the
> > matching transactions but when I join them with the following query
and
> > set
> > a very limited date range the query just locks up my system.
> >
> > for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> > TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> > TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> > each
> > PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and
TranGLC.Key2
> > =
> > PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> > TranGLC.SegValue1 by TranGLC.SegValue2 .
> >
> > I noticed that the date formats between PartTran.SysDate and
> > TranGLC.Key1
> > don't display the same in the 2 tables. TranGLC.Key1 displays
04/20/12
> > and
> > PartTran.SysDate displays 04/20/2012. Could this be causing the
issue?
> > Any
> > way around it?
> >
> > Thanks,
> > John Kreger
> >
> > [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]
Brian and Jean,
Huge thanks, tried both of those suggestions and it pulled the query in 2
seconds... Thanks alot!

--John Kreger

On Fri, Apr 27, 2012 at 8:38 AM, Brian Roberts <broberts@...> wrote:

> **
>
>
> Include the Company field in your join. Almost all indexes in Epicor
> include Company, and using it can make a huge difference in performance.
>
>
> Brian.
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of Jean
> Sent: Friday, April 27, 2012 9:12 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: 9.05.607B TranGLC to PartTran
>
>
> Try making TranGLC.RelatedToFile = 'PartTran' your first criteria on
> TranGLC; not sure it will make a difference, but I have a similiar BAQ
> that only takes a minute or 2 to run so it may be worth a try.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , John
>
> Kreger <lilj8069@...> wrote:
> >
> > We are in Progress and attempting to do this in BAQ. I set a criteria
> on
> > the TranGLC table to only pull related to file PartTran
> transactions... It
> > doesn't seem to be working at all... I'm pulling one days worth of
> > transactions which when pulling either of the tables data by
> themselves
> > takes 2-3 seconds but when doing the join BAQ designer just sits there
> for
> > upsides of 20 minutes....
> >
> > Any ides out there? Does anyone see an issue with my query I posted
> earlier?
> >
> > for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> > TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> > TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> > each
> > PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and
> TranGLC.Key2
> > =
> > PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> > TranGLC.SegValue1 by TranGLC.SegValue2
> >
> > Thanks,
> > --John Kreger
> >
> > On Thu, Apr 26, 2012 at 3:41 PM, Brian Roberts <broberts@...> wrote:
> >
> > > **
> > >
> > >
> > > If you have a SQL database, try making a SQL view casting the
> columns to
> > > correct data types before joining. I experimented with this once,
> think
> > > I had to make a subquery with RelatedToFile='PartTran' before
> casting to
> > > prevent errors with non-PartTran related transactions.
> > >
> > > Brian.
> > >
> > >
> > > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf
> > > Of Keith Mailloux
> > > Sent: Thursday, April 26, 2012 12:16 PM
> > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > > Subject: RE: [Vantage] 9.05.607B TranGLC to PartTran
> > >
> > >
> > > Welcome to TranGLC, in my opinion the biggest mistake Epicor has
> ever
> > > made. All of the key fields in TranGLC are character fields.
> Therefore
> > > if you try to join them to tables which contain date, numeric or
> other
> > > type key field records you will get errors (using ODBC). If you use
> BAQ,
> > > Epicor handles the field type discrepancies for you. I have not
> > > discovered a way to join different type fields using standard SQL
> ODBC
> > > connects in products like Crystal. If someone else has figured this
> out
> > > I would love to hear about it.
> > >
> > > Keith Mailloux
> > > Ferguson Perforating
> > > (401)941-8876 Ext 2238
> > >
> > > 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 John Kreger
> > > Sent: Thursday, April 26, 2012 11:42 AM
> > > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> <mailto:vantage%40yahoogroups.com>
> > > Subject: [Vantage] 9.05.607B TranGLC to PartTran
> > >
> > > Here's a unique issue we've just run into. The PartTran table is
> > > supposed
> > > to tie to the TranGLC table on the following fields:
> > > PartTran.SysDate = TranGLC.Key1
> > > PartTran.SysTime = TranGLC.Key2
> > > PartTran.TranNum = TranGLC.Key3
> > >
> > > When I query each one of these tables individually I can easily find
> the
> > > matching transactions but when I join them with the following query
> and
> > > set
> > > a very limited date range the query just locks up my system.
> > >
> > > for each TranGLC no-lock where TranGLC.TranDate >= {DateBegin} And
> > > TranGLC.TranDate <= {DateEnd} And TranGLC.SegValue1 >= '6100' And
> > > TranGLC.SegValue1 <= '6105' And TranGLC.RelatedToFile = 'PartTran' ,
> > > each
> > > PartTran no-lock where (TranGLC.Key1 = PartTran.SysDate and
> TranGLC.Key2
> > > =
> > > PartTran.SysTime and TranGLC.Key3 = PartTran.TranNum ) by
> > > TranGLC.SegValue1 by TranGLC.SegValue2 .
> > >
> > > I noticed that the date formats between PartTran.SysDate and
> > > TranGLC.Key1
> > > don't display the same in the 2 tables. TranGLC.Key1 displays
> 04/20/12
> > > and
> > > PartTran.SysDate displays 04/20/2012. Could this be causing the
> issue?
> > > Any
> > > way around it?
> > >
> > > Thanks,
> > > John Kreger
> > >
> > > [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]
>
>
>


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

We never went LIVE on E9 without the ioFusion cards, so its difficult to give you anything exact. I can tell you without a physical server running ioFusion we would never have been able to convert from 8.03.408b to 9.05.607b in a weekend. Originally we tried a VM with SQL split out from Epicor. It would have taken a week to convert. Next we tried all-in-one VM with SSDs, little faster but not enough. We went a physical box with SSDs (over fiber) and got it down to 50+ hours. When we went ioFusion on a physical box we got it down to 33 hours. Epicor did suggest a white box with SSDs for the conversion to get comparable performance but we wanted something we could repurpose & with redundancy just in case.



Our test environment is still in a VM on SSDs and moving around in it is very sluggish compared to live. Mass updating parts (going through business logic) is 6x faster on a physical box with ioFusion for us. An RMA credit test between v8 and e9 ioFusion was amost 4x faster - we haven't tried e9 VM so I don't have any specifics but I am certain it would have been painfully slower than v8 just from other experiences.



I read about the ioFusion in this user group and talked to 1 of the advocates. You should be able to find more info in a search - I don't want to misquote what he saw for improvements but can tell you it was enough to sell us.



As for cost there are several different options and you would need to find what's right for your company. Our DB is 130 GB now so we needed higher capacity cards and keeping disaster recovery in mind we got a backup one for mirroring. You might be able to see if you can try-before-you-buy to put an exact performance value for your justification - tough to quantify user frustration at wait times though.



Jenn





Jenn





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

Jenn,

We are currently on 9.05 and everyone is always complaining about the speed of Epicor... can you tell me more about the ioFusion cards...
How much did they speed it up... how much are they?

Marco Vissuet
Systems Engineering
Pacific Contours Corporation
Office (619) 670-3900
Fax (619) 670-1643


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