Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
- Subject: Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
- From: Jonathan Rochkind <email@hidden>
- Date: Mon, 09 Jun 2003 17:36:07 -0500
At 11:22 PM 6/9/2003 +0100, Jonathan Fleming wrote:
As above... does this cause a perfomace drag? Or is this acceptable if a
fetch is going on for the first time around anyway?
Well... usually, I don't bother doing something tricky for count (like a
dervied attribute) unless it's a count that is not available from simply
following a relationship and seeing how many objects are in it. Maybe I
don't have the relationship modeled/exposed. Or maybe the count I want is
not even definable as a relationship, it's not the objects in a
relationship I want the count of, it's some more complex calculation. In
that case, you really need to do something tricky.
Are there cases where you _could_ just use a relationship to find the
number of objects in it, but you might choose want to? Sure, there are
some. Following the relationship (at least the first time) will cause a
trip to the db---but on top of the time it will take for the db to find all
these rows, it will also take time for the db to return all the rows to
your WO app, and (more significantly) time for WO to instantiate all these
rows into EO objects. If you use, for instance, a derived count(*)
attribute to find them instead, it will still take the db about the same
amount of time to resolve the query, but you are saved the time of taking
all the rows and turning them into EOs. If you're going to want them as
EOs anyway, there's no point to this, but if you're not neccesarily going
to want them as EOs, you might want to do things differently. If there are
going to be 1000s of rows returned, you might not want to model it as a
relationship at all, you might just want to fetch the count(*) specifically
when you need it, especially if you are never going to need all the objects
themselves.
But in general, if you have access to the 'count' you need simply by
following the relationship and then seeing how many objects are in the
NSArray for the to-many relationship, sure, that's definitely the way to do
it.
But with the method I suggest it would probably require a seperate fetch
for each iteration of the repetition. But you can just bind a WOString to
a method which sets up the fetch to perform the count. But maybe I'm not
understanding the situation we're talking about here.
No, I think you are, it's moree likley i've not explained myself
correctly, however, you've got the gist of what I've been trying to do
The way I have gone about this is to create a view table in MSSQLServer2000
Yes, this seems like a fine idea too. Personally, I prefer to implement
all of my logic in WO code, and avoid special stuff set up on the db.
to be honest I would have prefered this too but I just wasn't having much
luck with do it so that i would have this performance problem. I didn't
know the SQL that the derived attribute would accept and time was pressing on.
Now that I have a fairly good workaround i feel better but would still
like to know if there is a better way to do this within WebObjects
Thanks
Jonathan F :^)
For a variety of reasons, some more rational than others, but none of
them universally applying to everyone, it's really just my personal
preference.
A view is probably quite a good way to do this, if you are willing to
set up a view like that. I don't see any problems with what you are
outlining below.
--Jonathan
(I know in the past you have said you use MS-SQL-7.0 but I have never
used it so I don't know the features it in order to say you can take
this approach there too). Anyway, create a view table in MSSQLServer2000
and set it up with code similar to this:
CREATE VIEW dbo.CountOfMyObjects
AS
SELECT COUNT(*) AS countOfMyObjects,
dbo.MY_TO_MANY_OBJECT.MY_TO_ONE_OBJECT_ID
FROM dbo.MY_TO_ONE_OBJECT INNER JOIN
dbo.MY_TO_MANY_OBJECT ON
dbo.MY_TO_ONE_OBJECT.MY_TO_ONE_OBJECT_ID =
dbo.MY_TO_MANY_OBJECT.MY_TO_ONE_OBJECT_ID
GROUP BY dbo.MY_TO_MANY_OBJECT.MY_TO_ONE_OBJECT_ID
A view is a virtual table whose contents are defined by a query. Like a
real table, a view consists of a set of named columns and rows of data.
However, a view does not exist as a stored set of data values in a
database. The rows and columns of data come from tables referenced in
the query defining the view and are produced dynamically when the view
is referenced. This is much like how the derived column in an EOModel
works I would say since you would use the same sort of code to reference
the other table(s).
A view acts as a filter on the underlying tables referenced in the view.
The query that defines the view can be from one or more tables or from
other views in the current or other databases. Distributed queries can
also be used to define views that use data from multiple heterogeneous
sources. This is useful, for example, if you want to combine similarly
structured data from different servers each of which stores data for a
different region of your organization.
There are no restrictions on querying through views and few restrictions
on modifying data through them. However in our case I have made the one
attribute we are referencing read only as you will learn.
Once you have setup the view table you can then import that into your
model by choosing Model>New Updated Model from the menu bar. You will be
presented with a dialog box where you select the table/s you want, there
you will find your view table/s Eg. MY_DERIVED_COUNT (if you named it
such. It may be appended with dbo. but this can be droped from the name
in the table inspector dialog box with no adverse effects). Next you
will be presented with the stored procedure list (uncheck them if you
don't want them). Click OK and you will now have a new model with your
selected table/s and SP's if you chose any. All you need do now is copy
the required table/s into you working model and set it up as a toOne
link on the table you are trying to obtain the count for. Before you
finish make sure you make the attribute of your derived table read only
by using the advanced inspector after highlighting that attribute, as
well as making the attribute a class property, but do remove it from the
locking mechanism as it is a derived property and therefore not needed
as a qualifier in a snapshot.
There will be no need to make the derived table an EOCustomObject so you
can leave it as an EOGenericRecord, but you will have to update your
to-One tables if they are of an EOCustomObject class, otherwise, that's
it, you're done. Code and link as you would any other object.
Now you have the befefit of getting the count of each row's to_Many
objects without creating an EO -- so having the database trawl through
everything in it just to get a count -- and therefore the count of an
individual row's to-Many objects when needed.
If this approach is fraught with danger, some please tell me fast! But
right now it looks good and tested good. I can't really see that there
would be any major problems with this.
Jonathan F ;^)
As far as I remember, EOF DO support such derived atributes; they have
to be flatened in EOModeler. But as far as I remember they slow down the fetch.
I suppose you could define a stored procedure instead if you
didn't want to fetch the related objects in order to count them on
the > client.
Aloha,
Art
_________________________________________________________________
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.