Seperating large tables into seperate data file - SQL Server

Hi List,

Am pretty sure that I had read something on this topic a while back on list, but dint have any luck pulling it up again.

I wanted to know the options for seperating out large tables..like F0911 into sperate data file when using SQL Server as your E1 database .

Has any one done this and what are your views on the same. Is there any significant advantage or performance gain realised by implementing this.

Thanks in Advance

There's probably more to it, than meets the eye, but yes, there will be advantages most certainly.

In JDE you can have your data anywhere.

You just need to ensure that you create a new datasource to point to your the new database(s) in which you are placing the large / extra large table(s) into.

Once you have created the datasource(s) then you need to organise quiet time to move the tables. Once moved then amend your OCM mappings for the environment that you have done this for.

I am not sure if you will get the performance increase you are looking for as SQL increases space by a percentage value of the space used as against Oracle that does it by tablespace extents.

Test it in one environment first taking before and after performance stats and see would be my suggestion.

I think, the idea was to keep the table in the same datasource/database, but move it (or them) into different files, which, of course, SQL can do.

This, along with proper disk sizing and allocation, but not by itself, would certainly give performance improvements.

Creating a new DB/DSN is, probably, an overkill. Plus, this would give nothing extra, compared to the first option...

Terry / Alex

Thanks for your response. Terry I am looking more on the lines of what Alex mentioned. In the default configuration a patricular datasource would reside in a single data file say JDE_ProductionData.mdf. SQL Server gives you the option to have a seperate data file for a specific table say F0911.

As your production data size increases I feel , seperating a table like F0911 should give you better performance since the whole data file need not be scanned thru for a read.

apart from the SQL Server process to implement such a setup I wanted to know , if anything specific needs to be taken care of on the JDE front...

Any thoughts...

I've done something similar in the past, but not to put a large file on its own into a seperate file as SQL doesn't really work that way.

In a fairly optimum system there would be a number of data files within one file group each attached via a seperate controller. SQL witl then spread all of its data over the various disks giving a much better read performance.

What you can do is create a new database and put the F0911 in there, however a new Datasource and OCM mapping will not work, a query that joins the F0911 to anything else WILL NOT reference the DB name, its NOT handled by the middleware, I know this through bitter experience, you cannot put anything anywhere in JDE via the OCM. Instead from the production database you have to set up an Alias to the other table, in this way SQL will handle the table query for you, I'm struggling to find out exactly how I did it within SQL, but if I find the method I post it here.

I had another reason to do this, in my case it was logging. Some of the F346X files are temporary\transactional only and had huge data volumes put through them. This applied a huge load on the Production Server and massively increased the log size. In addition to the Production Database (full logging on) another Database called ProdWork(no logging) was created and the tables put in there. I then mapped a DS to it and it didn't work for the reasons stated above. The Alias was created in Production, the DS and OCM removed and the system worked perfectly.

Hope this helps

Paul

Paul,

Thanks a lot for your response. Thats a new angle there that I dint think of, using a table alias and putting the table in a different database.

But woud operations like Index generation work if done from E1 (OMW etc)...am curious as to how that would work. And I assume that you would use the same owner name say PRODDTA in the other database also. And the E1 proxy ID would also need access to that database.

When you said " SQL doesnt work that way" did you mean that I cant sepcify a different file group for one specific table withtin a database...or that I would'nt acheive much doing that....

Hi Ice_cube210,

I'm not actually a SQL DBA, so I could be wrong, but if you add a file to the file group SQL just spreads out over that file, I don't know if you can control which tables go to which file so I don't think it will help you, other than there is usually a performance gain in having multiple files.

Also I have no idea if an index gen from JDE will work using a alias. I would personally gen somewhere else, create a sql script of the table, ammend it and then run it over the real one outside of JDE. An alternative would be a new DataSource and just generate to that, but not map it in the OCM.

As far as the alias is concerned it was the only way we could find to do the job as the SQL join refers to:

Table 1: proddta.FXXXXXXX
Table 2: proddta.FYYYYYYY

It doesn't mention the databse name itself, this is where the put any table anywhere breaks down in JDE.

The owner in the secondary DB was indeed PRODDTA, although I suspect that it doesn't matter as the alias is called proddta.fxxxxx and refers to the other one.

I spent about 20mins trying to look up\remember exactly how I created the alias, I seem to recall it was pretty easy to do, but difficult to find exactly how it was done...

Rgds

Paul

"As your production data size increases I feel , seperating a table like F0911 should give you better performance since the whole data file need not be scanned thru for a read."


this isn't MS Access, real databases don't work that way (entire data file scanned). Unless you are actually able to place the separate file on a separate drive spindle with a separate controller (rare in these days of large raid drives) you're not going to see any performance benefit and will have more configuration complexity by going this route.

SQL Server filegroups is what you are looking for. You can create a secondary filegroup for jde_production and then create a data file that uses the new filegroup. If you use Enterprise Manager note that you must create the filegroup and click OK and reopen the database properties window and create the file and specify the filegroup you just created, otherwise the filegroup will not show up.

You would then go into the table design for the table desired, select the properties button and change the filegroup to your newly created filegroup. Your table is now in the new filegroup.

You can also place indexes in seperate filegroups for performance.

Beware that when you regenerate the table or indexes from within OneWorld it will create it back in the Primary filegroup.



Is it worth the performance gain? Probably.

One suggestion I can add to this is as follows

Create a brand new database and as noted in the thread ( unless its new diskdrive you will not get much benfit)...
Move your F0911 table there....with the data .
Now drop the table in your production database and create a view of f0911 table and call it f0911 in the old data base which is getting data from new database.... Make sure the owned is same Proddta....

This way the view will exist in the Production database, however the actual data and the related file only exit on a seprate database....

One gotcha for this approach is that Index generation will not work.... What you would have to do in this case will be generate the new index in other environment and copy the index creation sql from there and runnin it against Production F0911 table....
I haven't tested this but I am pretty sure this will work....

This is a similar concept used in CFR11 auditing where tables exist as a view and the real table exist as _adt table.. In this case both the view and table exist in the same database....

I hope this helps

A view will work, but I think the performance would suffer, so there's little point doing it.

On the other hand, moving the table to a different file is very sensitive to the implementation and can adversely affect the performance as well as improve it - depending on how it's done.

If you are going to do that why not just create a new database, place that database on a seperate drive for performance, move F0911 into it, then create a new E1 datasource that points to it?

We tried doing this with some other tables and ran into a limitation. The limitation is that you can't join more than two tables together from different datasources. If you have any business views that do this you will get an error.

Tom

All,

Thanks for the informationn and sharing your opinions and views.