Re: optimization/indexing
Re: optimization/indexing
- Subject: Re: optimization/indexing
- From: Chuck Hill <email@hidden>
- Date: Thu, 18 Dec 2008 20:18:58 -0800
On Dec 18, 2008, at 8:11 PM, Jeff Schmitz wrote:
"LOBs"? Sorry, I'm not familiar with that term.
I have set the EOAdaptorDebugEnabled flag. Below is the output from
a ERXBatchFetchUtilities.batchFetch call that takes 94 seconds.
It's traversing something like a 1 to 65 to 2 relationship.
Dec 18 20:48:38 netBrackets[2001] (ERXNSLogLog4jBridge.java:46)
DEBUG NSLog - === Begin Internal TransactionDec 18 20:48:38
netBrackets[2001] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog -
evaluateExpression: <com.webobjects.jdbcadaptor._FrontBasePlugIn
$FrontbaseExpression: "SELECT t0."entryID", t0."c_game",
t0."c_group", t0."id", t0."c_items", t0."c_score"
, t0."c_selected_item", t0."c_selected_weight" FROM "t_combo_team"
t0 WHERE (t0."entryID" = 1000001 OR t0."entryID" = 1000002)"
withBindings: >
Dec 18 20:48:38 netBrackets[2001] (ERXNSLogLog4jBridge.java:46)
DEBUG NSLog - 126 row(s) processed
Dec 18 20:48:38 netBrackets[2001] (ERXNSLogLog4jBridge.java:46)
DEBUG NSLog - === Commit Internal Transaction
Dec 18 20:48:38 netBrackets[2001] (ERXNSLogLog4jBridge.java:46)
DEBUG NSLog - === Begin Internal TransactionDec 18 20:48:38
netBrackets[2001] (ERXNSLogLog4jBridge.java:46) DEBUG NSLog -
evaluateExpression: <com.webobjects.jdbcadaptor._FrontBasePlugIn
$FrontbaseExpression: "SELECT t0."comboTeamID", t0."c_count",
t0."id", t0."c_must", t0."c_must_team", t0."c_place" FROM
"WinAnalysis" t0 WHERE (t0."comboTeamID" = 1000114 OR
t0."comboTeamID" = 1000098 OR t0."comboTeamID" = 1000067 OR
t0."comboTeamID" = 1000071 OR t0."comboTeamID" = 1000101 OR
t0."comboTeamID" = 1000080 OR t0."comboTeamID" = 1000085 OR
t0."comboTeamID" = 1000078 OR t0."comboTeamID" = 1000088 OR
t0."comboTeamID" = 1000122 OR t0."comboTeamID" = 1000076 OR
t0."comboTeamID" = 1000116 OR t0."comboTeamID" = 1000125 OR
t0."comboTeamID" = 1000110 OR t0."comboTeamID" = 1000069 OR
t0."comboTeamID" = 1000092 OR t0."comboTeamID" = 1000086 OR
t0."comboTeamID" = 1000064 OR t0."comboTeamID" = 1000074 OR
t0."comboTeamID" = 1000119 OR t0."comboTeamID" = 1000100 OR
t0."comboTeamID" = 1000093 OR t0."comboTeamID" = 1000066 OR
t0."comboTeamID" = 1000099 OR t0."comboTeamID" = 1000120 OR
t0."comboTeamID" = 1000121 OR t0."comboTeamID" = 1000105 OR
t0."comboTeamID" = 1000097 OR t0."comboTeamID" = 1000089 OR
t0."comboTeamID" = 1000126 OR t0."comboTeamID" = 1000117 OR
t0."comboTeamID" = 1000095 OR t0."comboTeamID" = 1000104 OR
t0."comboTeamID" = 1000077 OR t0."comboTeamID" = 1000109 OR
t0."comboTeamID" = 1000090 OR t0."comboTeamID" = 1000075 OR
t0."comboTeamID" = 1000096 OR t0."comboTeamID" = 1000079 OR
t0."comboTeamID" = 1000081 OR t0."comboTeamID" = 1000111 OR
t0."comboTeamID" = 1000091 OR t0."comboTeamID" = 1000087 OR
t0."comboTeamID" = 1000106 OR t0."comboTeamID" = 1000124 OR
t0."comboTeamID" = 1000084 OR t0."comboTeamID" = 1000108 OR
t0."comboTeamID" = 1000068 OR t0."comboTeamID" = 1000113 OR
t0."comboTeamID" = 1000094 OR t0."comboTeamID" = 1000107 OR
t0."comboTeamID" = 1000070 OR t0."comboTeamID" = 1000072 OR
t0."comboTeamID" = 1000102 OR t0."comboTeamID" = 1000112 OR
t0."comboTeamID" = 1000103 OR t0."comboTeamID" = 1000082 OR
t0."comboTeamID" = 1000118 OR t0."comboTeamID" = 1000065 OR
t0."comboTeamID" = 1000073 OR t0."comboTeamID" = 1000123 OR
t0."comboTeamID" = 1000083 OR t0."comboTeamID" = 1000115)"
withBindings: >
Dec 18 20:50:12 netBrackets[2001] (ERXNSLogLog4jBridge.java:46)
DEBUG NSLog - 189 row(s) processed
Dec 18 20:50:12 netBrackets[2001] (ERXNSLogLog4jBridge.java:46)
DEBUG NSLog - === Commit Internal Transaction
Does WinAnalysis.comboTeamID have an index? I'd guess that it does
and that the index is corrupt. Or that is one VERY large table and
there is no index. If you can get FrontBase to use an IN instead of
ORs that will also make it faster, but you have some kind of hunka
burning serious SQL problem happening there.
Chuck
On Dec 18, 2008, at 9:52 PM, Clark Mueller wrote:
Jeff,
What does your model/database schema actually look like? Have you
tried setting EOAdaptorDebugEnabled to true in your launch config
(or via property)? How do the LOBs fit into your design?
Clark
On 18-Dec-08, at 7:41 PM, Jeff Schmitz wrote:
By consider a different design, do you mean something like the
below (from the wiki)? Coming form an OO world, I perhaps took
the paradigm too far and chopped up my data into too many tables?
e.g. Would denormalizing my 1 --> 65 --> 2 tables into a single
table help? Or would a better suggestion be to use "blobs" for
the 65-->2 part so in the end I'd have a relationship of 1-->1
blob? If the blob route, can I assume you wouldn't want the
optimistic locking to check the blob for changes?
A common experience with large and complex object model, is that
people model their objects, then do a large fetch and find out
that bringing in a large set of EO's can be really slow .
Adapt your model
When you are going to be using a relational to object mapping
library (like EOF), you should expect that this will change your
requirements; enough that you can adapt your model to fit the tool.
If fetching an EO is heavy/slow, then generally the fewer objects
you bring in, the faster your system will perform. So if you
collapse and denormalize many small tables, into a few bigger
ones, you will be doing fewer EO loads, and probably dealing less
with all that fault and relationship management of all those
little fragments and relationships; which can result in
performance savings.
You can do this a little by flattening relationships, or using
views in the database to make things appear to be flatter than
they are; or you can go right to model and actually flatten.
Arguments can be made for each, depending on your requirements.
You can even go further, and start moving complex data structures
and relationships into blobs that you manage yourself. This
offloads EOF from managing them, and often allows you to speed
things up; but the cost is more code maintenance on your part, and
of course denormalizing can negatively impact the design so you
want to be careful about how zealously you go down this path.
On Dec 17, 2008, at 12:39 PM, Chuck Hill wrote:
On Dec 17, 2008, at 9:25 AM, Jeff Schmitz wrote:
Yes, now that I think of it, there is one of these "crazy" joins
that's probably coming into play that joins each of my 7000 rows
with 65 rows in a different table, so that table must have about
450,000 rows. Any good optimization approaches for these type
of one to "very many" relationships? recursive fetch? I can
see this table getting into the many millions of rows real fast.
I'd spend some quality time considering a different design. I
doubt it is doing a crazy join. My money would be on Mike's
prediction of insane amounts of rapidly scrolling SQL.
ERXBatchFetching can be a big help here, properly used.
Chuck
On Tuesday, December 16, 2008, at 11:09PM, "Chuck Hill" <email@hidden
> wrote:
Either some crazy joins with other tables or something you are
not
aware of is going on. 7K rows is tiny.
Chuck
On Dec 16, 2008, at 9:07 PM, Jeff Schmitz wrote:
hmm, I'm not doing an insert at all, just a read. Kind of
thought
there must be something else too though (with my limited
experience)
but figured indexing would be a good thing to do regardless
before
digging into debugging the real culprit here.
Jeff
On Dec 16, 2008, at 11:01 PM, Mike Schrag wrote:
More than a minute to insert to a 7000 row table?
Do other operations on the same DB take an appropriate
amount of
time? If not I would start looking at DNS or other
connectivity
issues. I can't fathom a FB DB sucking at that level.
this was my first thought, too ... something else is going on
here. I suspect if sql debug was turned on, you'd see tons of
faulting going on that you didn't realize and that the insert
itself is not actually the slow thing.
ms
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden
)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-
email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve
specific
problems.
http://www.global-village.net/products/practical_webobjects
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve
specific problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden