Vantage 8.03 conversion to Epicor 9/9.05 performance issues (large d

Bob,

Yeah TempDB is a pesky creature. What recovery model are you using? Just wondering if your logs are part of your space issue. Either way I highly suggest you address your storage subsystem as I described earlier. Virtualization isn't doing much for your performance either, but there may be a management benefit for you.

Yes, the approach in the script posted by Chris Robisch is pretty much spot on. He's also rebuilding the full text catalogs for the part table. You may or may not have impemented full text, or you may have more catalogs (we do). Either way I would treat the full text catalogs separately. As a side note, SQL Server 2005 and 2008 handle Full Text completely differently.

Here's a slightly modified script to be run against 8.03 you could include in a maintenance plan. I use a fillfactor of 80, but 90 may be better, I really haven't done any analysis on that.

USE Mfgsys803
GO
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @TableName
DBCC DBREINDEX(@TableName,' ',80)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
EXEC sp_updatestats
DBCC SHRINKDATABASE(Mfgsys803,10)
GO
DBCC CHECKDB(Mfgsys803)
GO

Here's an alternate but equivalent method. Credit goes to Pinal Dave for this one.

USE Mfgsys803
GO
EXEC sp_MSforeachtable
@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO

If you have implemented full text, here you go.

USE MfgSys803
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'custxprt', 'start_full'
GO
sp_fulltext_catalog 'fscalldt', 'start_full'
GO
sp_fulltext_catalog 'fscontdt', 'start_full'
GO
sp_fulltext_catalog 'glacct', 'start_full'
GO
sp_fulltext_catalog 'glchart', 'start_full'
GO
sp_fulltext_catalog 'invchead', 'start_full'
GO
sp_fulltext_catalog 'jobasmbl', 'start_full'
GO
sp_fulltext_catalog 'jobhead', 'start_full'
GO
sp_fulltext_catalog 'langorg', 'start_full'
GO
sp_fulltext_catalog 'langtran', 'start_full'
GO
sp_fulltext_catalog 'orderdtl', 'start_full'
GO
sp_fulltext_catalog 'orderhed', 'start_full'
GO
sp_fulltext_catalog 'part', 'start_full'
GO
sp_fulltext_catalog 'podetail', 'start_full'
GO
sp_fulltext_catalog 'QuoteDtl', 'start_full'
GO
sp_fulltext_catalog 'reqdetail', 'start_full'
GO
sp_fulltext_catalog 'rfqitem', 'start_full'
GO
sp_fulltext_catalog 'rcvdtl', 'start_full'
GO
sp_fulltext_catalog 'ShipDtl', 'start_full'
GO
sp_fulltext_catalog 'ECORev', 'start_full'
GO
sp_fulltext_catalog 'FAsset', 'start_full'
GO
sp_fulltext_catalog 'CostPart', 'start_full'
GO
sp_fulltext_catalog 'HDCase', 'start_full'
GO
sp_fulltext_catalog 'ResourceCollection', 'start_full'
GO
sp_fulltext_catalog 'WCGroup', 'start_full'
GO
sp_fulltext_catalog 'xfileref', 'start_full'
GO

Cheers,
Jared

--- In vantage@yahoogroups.com, "bob.herold" <bherold@...> wrote:
>
> Jared,
>
> One of the replies to my original post contained a script that suggested running a rebuild on the indexes to reduce fragmentation in the database. SQL server 2005 has this built in as an option under their maintenance plans.
>
> I haven't run an index rebuild in the past and I'm thinking that I would benefit from running it. Has anyone run the database integrity check or index rebuild maintenance plans against their production Vantage instance?
>
> I tried running the dbcc checkdb command against my test instance and ran out of room on my c: after my temdb file grew beyond 9.5 GB. I am thinking of relocating the file and trying again.
>
> PS - Thanks to everyone for all the discussion regarding our conversion issues.
>
> Bob
Did you say "108 GB database"? I believe that might the largest database out
there in this group.



Randy Duly, BSBIS, MCP, A+

IT Manager

National Tube Form

A Global Tube Form Company

3405 Engle Road

Fort Wayne IN 46809

P: 260-478-2363

F: 260-478-1043

C: 260-402-3216



<blocked::http://www.nationaltubeform.com/>

<http://www.nationaltubeform.com/>




This e-mail transmission and any attachments to it are intended solely for
the use of the individual or entity to whom it is addressed and may contain
confidential and privileged information. If you are not the intended
recipient, your use, forwarding, printing, storing, disseminating,
distribution, or copying of this communication is prohibited. If you
received this communication in error, please notify the sender immediately
by replying to this message and delete it from your computer.





[Non-text portions of this message have been removed]
I just had a thought related to my first paragraph in my previous post. I hope your DB isn't that large because you've been attaching files within the application. SQL server embeds these as binary large objects (BLOBs), which is sooooo not ideal. SQL Server 2008 introduced a technology called Filestream (developed for the SharePoint team I believe) which stores the actual file data in a physical file folder and not as binary field data within the SQL data file. The data file only has information that points to the file. One nice feature is you can backup all the file data just like a regular SQL server backup. Unfortunately, Epicor doesn't leverage this feature (yet).

If you're using the attachment feature for document management, then I think it's time to go shopping for a real document management system. SharePoint is one choice among many.

If you're not attaching files, then I gotta echo Randy's post, why so large?
Jared

--- In vantage@yahoogroups.com, "Randy Duly" <randy.duly@...> wrote:
>
> Did you say "108 GB database"? I believe that might the largest database out
> there in this group.
>
>
>
> Randy Duly, BSBIS, MCP, A+
>
> IT Manager
>
> National Tube Form
>
> A Global Tube Form Company
>
> 3405 Engle Road
>
> Fort Wayne IN 46809
>
> P: 260-478-2363
Jared- do you know where in the DB the BLOB is stored? I looked in xfileattch and xfileref but it just stores the file path, not the actual file. Is it stored in another table?

Thanks,
Aimee
--- In vantage@yahoogroups.com, "k99ja04" <jallmond@...> wrote:
>
> I just had a thought related to my first paragraph in my previous post. I hope your DB isn't that large because you've been attaching files within the application. SQL server embeds these as binary large objects (BLOBs), which is sooooo not ideal. SQL Server 2008 introduced a technology called Filestream (developed for the SharePoint team I believe) which stores the actual file data in a physical file folder and not as binary field data within the SQL data file. The data file only has information that points to the file. One nice feature is you can backup all the file data just like a regular SQL server backup. Unfortunately, Epicor doesn't leverage this feature (yet).
>
> If you're using the attachment feature for document management, then I think it's time to go shopping for a real document management system. SharePoint is one choice among many.
>
> If you're not attaching files, then I gotta echo Randy's post, why so large?
> Jared
>
> --- In vantage@yahoogroups.com, "Randy Duly" <randy.duly@> wrote:
> >
> > Did you say "108 GB database"? I believe that might the largest database out
> > there in this group.
> >
> >
> >
> > Randy Duly, BSBIS, MCP, A+
> >
> > IT Manager
> >
> > National Tube Form
> >
> > A Global Tube Form Company
> >
> > 3405 Engle Road
> >
> > Fort Wayne IN 46809
> >
> > P: 260-478-2363
>
BLOB's aren't files, they are stored within the database in columns.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of aimee.grebe
Sent: Monday, August 30, 2010 1:00 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re:Vantage 8.03 conversion to Epicor 9/9.05
performance issues (large d





Jared- do you know where in the DB the BLOB is stored? I looked in
xfileattch and xfileref but it just stores the file path, not the actual
file. Is it stored in another table?

Thanks,
Aimee
--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"k99ja04" <jallmond@...> wrote:
>
> I just had a thought related to my first paragraph in my previous
post. I hope your DB isn't that large because you've been attaching
files within the application. SQL server embeds these as binary large
objects (BLOBs), which is sooooo not ideal. SQL Server 2008 introduced a
technology called Filestream (developed for the SharePoint team I
believe) which stores the actual file data in a physical file folder and
not as binary field data within the SQL data file. The data file only
has information that points to the file. One nice feature is you can
backup all the file data just like a regular SQL server backup.
Unfortunately, Epicor doesn't leverage this feature (yet).
>
> If you're using the attachment feature for document management, then I
think it's time to go shopping for a real document management system.
SharePoint is one choice among many.
>
> If you're not attaching files, then I gotta echo Randy's post, why so
large?
> Jared
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Randy Duly" <randy.duly@> wrote:
> >
> > Did you say "108 GB database"? I believe that might the largest
database out
> > there in this group.
> >
> >
> >
> > Randy Duly, BSBIS, MCP, A+
> >
> > IT Manager
> >
> > National Tube Form
> >
> > A Global Tube Form Company
> >
> > 3405 Engle Road
> >
> > Fort Wayne IN 46809
> >
> > P: 260-478-2363
>





[Non-text portions of this message have been removed]
Perhaps I did not phrase my question correctly. When an attachment is saved in Vantage- let's say a pdf file- according to Jared below, SQL embeds these as binary large objects (BLOBs). I took this to mean there is a table somewhere in the database with column of data type binary and within that column is my pdf file-in binary format.

The only tables I see that have to do with attachments are xfileattch and xfileref but there are no columns of binary data type. All I see is my file path.

Thanks,
Aimee
--- In vantage@yahoogroups.com, "Sean McDaniel" <smcdaniel@...> wrote:
>
> BLOB's aren't files, they are stored within the database in columns.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of aimee.grebe
> Sent: Monday, August 30, 2010 1:00 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re:Vantage 8.03 conversion to Epicor 9/9.05
> performance issues (large d
>
>
>
>
>
> Jared- do you know where in the DB the BLOB is stored? I looked in
> xfileattch and xfileref but it just stores the file path, not the actual
> file. Is it stored in another table?
>
> Thanks,
> Aimee
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> "k99ja04" <jallmond@> wrote:
> >
> > I just had a thought related to my first paragraph in my previous
> post. I hope your DB isn't that large because you've been attaching
> files within the application. SQL server embeds these as binary large
> objects (BLOBs), which is sooooo not ideal. SQL Server 2008 introduced a
> technology called Filestream (developed for the SharePoint team I
> believe) which stores the actual file data in a physical file folder and
> not as binary field data within the SQL data file. The data file only
> has information that points to the file. One nice feature is you can
> backup all the file data just like a regular SQL server backup.
> Unfortunately, Epicor doesn't leverage this feature (yet).
> >
> > If you're using the attachment feature for document management, then I
> think it's time to go shopping for a real document management system.
> SharePoint is one choice among many.
> >
> > If you're not attaching files, then I gotta echo Randy's post, why so
> large?
> > Jared
> >
> > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> "Randy Duly" <randy.duly@> wrote:
> > >
> > > Did you say "108 GB database"? I believe that might the largest
> database out
> > > there in this group.
> > >
> > >
> > >
> > > Randy Duly, BSBIS, MCP, A+
> > >
> > > IT Manager
> > >
> > > National Tube Form
> > >
> > > A Global Tube Form Company
> > >
> > > 3405 Engle Road
> > >
> > > Fort Wayne IN 46809
> > >
> > > P: 260-478-2363
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
> Perhaps I did not phrase my question correctly. When an attachment is saved in Vantage- let's say
> a pdf file- according to Jared below, SQL embeds these as binary large objects (BLOBs).
...
> The only tables I see that have to do with attachments are xfileattch and xfileref but there are no
> columns of binary data type. All I see is my file path.

I don't think Vantage/Epicor stores documents in the database. It
stores the link to the file on the file system or a reference to
SharePoint (which DOES store files in its database).

Mark W.
Aimee,
You asked the question perfectly fine, and you know what, I have to check myself on this one. I was passing on how the Epicor Document Module was explained to me. Which also jived with what I've experienced with other DBs in the past, so I took it at face value and never bothered to look into it. Shame on me! We use SharePoint for document management, so more reason I never bothered to peek under the hood. But enough with excuses, I eat crow on this one.

After a bit of digging, it appears that the DB is only storing a text only link to wherever the file is stored. If you don't define a base URL in Document Type under Company Maintenance the xfileref.xfilename defaults to the original file location, which is probably whoever attempted to attach the file's C:\ drive. How silly is that?!?! While their approach is less expensive on the DB from a storage perspective as opposed to BLOBs, it's not the most user friendly. I could totally see someone not defining the Base URL, fast forward a bit, and nothing is ever actually uploaded. It'd be nice if they had a system default catchall folder, instead of relying on you to establish (and backup) UNC paths or mapped drives. Now that Epicor have jumped to SQL 2008 (sorry Progress users) it wouldn't be hard at all from a development standpoint to implement FILESTREAM. Maybe in Epicor 11...
Cheers,
Jared
P.S.
Now that we've established file attachments don't embed as BLOBs, how in the world do you get to 108 GB?!?!

--- In vantage@yahoogroups.com, "aimee.grebe" <aimee.grebe@...> wrote:
>
> Perhaps I did not phrase my question correctly. When an attachment is saved in Vantage- let's say a pdf file- according to Jared below, SQL embeds these as binary large objects (BLOBs). I took this to mean there is a table somewhere in the database with column of data type binary and within that column is my pdf file-in binary format.
>
> The only tables I see that have to do with attachments are xfileattch and xfileref but there are no columns of binary data type. All I see is my file path.
>
> Thanks,
> Aimee
> --- In vantage@yahoogroups.com, "Sean McDaniel" <smcdaniel@> wrote:
> >
> > BLOB's aren't files, they are stored within the database in columns.
> >
> >
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> > Of aimee.grebe
> > Sent: Monday, August 30, 2010 1:00 PM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Re:Vantage 8.03 conversion to Epicor 9/9.05
> > performance issues (large d
> >
> >
> >
> >
> >
> > Jared- do you know where in the DB the BLOB is stored? I looked in
> > xfileattch and xfileref but it just stores the file path, not the actual
> > file. Is it stored in another table?
> >
> > Thanks,
> > Aimee
> > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> > "k99ja04" <jallmond@> wrote:
> > >
> > > I just had a thought related to my first paragraph in my previous
> > post. I hope your DB isn't that large because you've been attaching
> > files within the application. SQL server embeds these as binary large
> > objects (BLOBs), which is sooooo not ideal. SQL Server 2008 introduced a
> > technology called Filestream (developed for the SharePoint team I
> > believe) which stores the actual file data in a physical file folder and
> > not as binary field data within the SQL data file. The data file only
> > has information that points to the file. One nice feature is you can
> > backup all the file data just like a regular SQL server backup.
> > Unfortunately, Epicor doesn't leverage this feature (yet).
> > >
> > > If you're using the attachment feature for document management, then I
> > think it's time to go shopping for a real document management system.
> > SharePoint is one choice among many.
> > >
> > > If you're not attaching files, then I gotta echo Randy's post, why so
> > large?
> > > Jared
> > >
> > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> > "Randy Duly" <randy.duly@> wrote:
> > > >
> > > > Did you say "108 GB database"? I believe that might the largest
> > database out
> > > > there in this group.
> > > >
> > > >
> > > >
> > > > Randy Duly, BSBIS, MCP, A+
> > > >
> > > > IT Manager
> > > >
> > > > National Tube Form
> > > >
> > > > A Global Tube Form Company
> > > >
> > > > 3405 Engle Road
> > > >
> > > > Fort Wayne IN 46809
> > > >
> > > > P: 260-478-2363
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
Jared wrote:
> After a bit of digging, it appears that the DB is only storing a text only link to wherever the file is stored.
> If you don't define a base URL in Document Type under Company Maintenance the xfileref.xfilename
> defaults to the original file location, which is probably whoever attempted to attach the file's C:\ drive.
> How silly is that?!?! While their approach is less expensive on the DB from a storage perspective as
> opposed to BLOBs, it's not the most user friendly. I could totally see someone not defining the
> Base URL, fast forward a bit, and nothing is ever actually uploaded.
> ...

In the Files section of the group, I posted a C# program that will let
you update the locations of the files so you can fix an implementation
error such as that.

Mark W.
Mark, very nice work. I will beat you up a little, Hungarian notation is frowned upon these days in .NET (http://msdn.microsoft.com/en-us/library/ms229042.aspx), but most important your code is readable and easy to follow.
Thanks,
Jared

--- In vantage@yahoogroups.com, Mark Wonsil <mark_wonsil@...> wrote:
>
> Jared wrote:
> > After a bit of digging, it appears that the DB is only storing a text only link to wherever the file is stored.
> > If you don't define a base URL in Document Type under Company Maintenance the xfileref.xfilename
> > defaults to the original file location, which is probably whoever attempted to attach the file's C:\ drive.
> > How silly is that?!?! While their approach is less expensive on the DB from a storage perspective as
> > opposed to BLOBs, it's not the most user friendly. I could totally see someone not defining the
> > Base URL, fast forward a bit, and nothing is ever actually uploaded.
> > ...
>
> In the Files section of the group, I posted a C# program that will let
> you update the locations of the files so you can fix an implementation
> error such as that.
>
> Mark W.
>
Hi Jared,

> Mark, very nice work. I will beat you up a little, Hungarian notation is frowned upon these days
> in .NET (http://msdn.microsoft.com/en-us/library/ms229042.aspx), but most important your code
> is readable and easy to follow.

That was my first shot at a C# program and I was trying to follow the
old Microsoft "Code Complete" guidelines. Thanks for the link,
resistance is futile, I will assimilate... ;-)

Mark W.
Jared,

One of the replies to my original post contained a script that suggested running a rebuild on the indexes to reduce fragmentation in the database. SQL server 2005 has this built in as an option under their maintenance plans.

I haven't run an index rebuild in the past and I'm thinking that I would benefit from running it. Has anyone run the database integrity check or index rebuild maintenance plans against their production Vantage instance?

I tried running the dbcc checkdb command against my test instance and ran out of room on my c: after my temdb file grew beyond 9.5 GB. I am thinking of relocating the file and trying again.

PS - Thanks to everyone for all the discussion regarding our conversion issues.

Bob


--- In vantage@yahoogroups.com, "k99ja04" <jallmond@...> wrote:
>
> Aimee,
> You asked the question perfectly fine, and you know what, I have to check myself on this one. I was passing on how the Epicor Document Module was explained to me. Which also jived with what I've experienced with other DBs in the past, so I took it at face value and never bothered to look into it. Shame on me! We use SharePoint for document management, so more reason I never bothered to peek under the hood. But enough with excuses, I eat crow on this one.
>
> After a bit of digging, it appears that the DB is only storing a text only link to wherever the file is stored. If you don't define a base URL in Document Type under Company Maintenance the xfileref.xfilename defaults to the original file location, which is probably whoever attempted to attach the file's C:\ drive. How silly is that?!?! While their approach is less expensive on the DB from a storage perspective as opposed to BLOBs, it's not the most user friendly. I could totally see someone not defining the Base URL, fast forward a bit, and nothing is ever actually uploaded. It'd be nice if they had a system default catchall folder, instead of relying on you to establish (and backup) UNC paths or mapped drives. Now that Epicor have jumped to SQL 2008 (sorry Progress users) it wouldn't be hard at all from a development standpoint to implement FILESTREAM. Maybe in Epicor 11...
> Cheers,
> Jared
> P.S.
> Now that we've established file attachments don't embed as BLOBs, how in the world do you get to 108 GB?!?!
>
> --- In vantage@yahoogroups.com, "aimee.grebe" <aimee.grebe@> wrote:
> >
> > Perhaps I did not phrase my question correctly. When an attachment is saved in Vantage- let's say a pdf file- according to Jared below, SQL embeds these as binary large objects (BLOBs). I took this to mean there is a table somewhere in the database with column of data type binary and within that column is my pdf file-in binary format.
> >
> > The only tables I see that have to do with attachments are xfileattch and xfileref but there are no columns of binary data type. All I see is my file path.
> >
> > Thanks,
> > Aimee
> > --- In vantage@yahoogroups.com, "Sean McDaniel" <smcdaniel@> wrote:
> > >
> > > BLOB's aren't files, they are stored within the database in columns.
> > >
> > >
> > >
> > > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> > > Of aimee.grebe
> > > Sent: Monday, August 30, 2010 1:00 PM
> > > To: vantage@yahoogroups.com
> > > Subject: [Vantage] Re:Vantage 8.03 conversion to Epicor 9/9.05
> > > performance issues (large d
> > >
> > >
> > >
> > >
> > >
> > > Jared- do you know where in the DB the BLOB is stored? I looked in
> > > xfileattch and xfileref but it just stores the file path, not the actual
> > > file. Is it stored in another table?
> > >
> > > Thanks,
> > > Aimee
> > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> > > "k99ja04" <jallmond@> wrote:
> > > >
> > > > I just had a thought related to my first paragraph in my previous
> > > post. I hope your DB isn't that large because you've been attaching
> > > files within the application. SQL server embeds these as binary large
> > > objects (BLOBs), which is sooooo not ideal. SQL Server 2008 introduced a
> > > technology called Filestream (developed for the SharePoint team I
> > > believe) which stores the actual file data in a physical file folder and
> > > not as binary field data within the SQL data file. The data file only
> > > has information that points to the file. One nice feature is you can
> > > backup all the file data just like a regular SQL server backup.
> > > Unfortunately, Epicor doesn't leverage this feature (yet).
> > > >
> > > > If you're using the attachment feature for document management, then I
> > > think it's time to go shopping for a real document management system.
> > > SharePoint is one choice among many.
> > > >
> > > > If you're not attaching files, then I gotta echo Randy's post, why so
> > > large?
> > > > Jared
> > > >
> > > > --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> > > "Randy Duly" <randy.duly@> wrote:
> > > > >
> > > > > Did you say "108 GB database"? I believe that might the largest
> > > database out
> > > > > there in this group.
> > > > >
> > > > >
> > > > >
> > > > > Randy Duly, BSBIS, MCP, A+
> > > > >
> > > > > IT Manager
> > > > >
> > > > > National Tube Form
> > > > >
> > > > > A Global Tube Form Company
> > > > >
> > > > > 3405 Engle Road
> > > > >
> > > > > Fort Wayne IN 46809
> > > > >
> > > > > P: 260-478-2363
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>