Smalltalk › Cincom › VisualWorks

[vwnc] [Store query] Faster way to query for common parentRecord of two pundles

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

7 messages Options

Paul Baumann

[vwnc] [Store query] Faster way to query for common parentRecord of two pundles

I'm looking for a quick way to do a query for a code release tool that is used in-house and is also in the Store Public Repository. Right now the query is slow due to network latency making multiple trips to Oracle for each #parentRecord query.

A Pundle knows what it was based on through DBRecord.trace, and the base version record can be retrieved using #parentRecord. A list of pundles ancestors could be retrieved by asking #parentRecord of the last parentRecord until nil is returned. The current implementation uses a timeStamp directed query to determine the common ancestor of two pundles.

basicVersionAncestors
"There is probably a faster way to do this. This does a query for each parentRecord."

| a b answer |
answer := Array
  with: OrderedCollection new
  with: OrderedCollection new.
a := self basisPundle.
b := self storePundle.
a = b ifTrue: [b := self releasedPundle].
[
  a isNil | b isNil ifTrue: [^answer "No common parent"].
  a = b
] whileFalse: [
  a timeStamp > b timeStamp
  ifTrue: [(a := a parentRecord) isNil ifFalse: [answer first add: a] ]
  ifFalse: [(b := b parentRecord) isNil ifFalse: [answer last add: b] ].
].
^answer

Network latency is absolutely destroying performance doing this query. It may take just a few milliseconds to do the query, but the query is frequent enough that this can mean a couple minutes for the first window display. To support new functionality, the common ancestor must be known at window display time. I'm looking for faster options.

The code I showed collects all the ancestors between two pundles, but most senders are only interested in the common ancestor. This kind of query would be trivial in an object database; I'd just execute a data-directed loop on the server. I'm not as familiar with SQL though.

I'm looking for either a single-trip query to answer the common ancestor record for two pundles; or, a single-trip query to answer all ancestor records for one of the pundles. It might be that store already has something like that, but I haven't been able to find it in the code.

Any ideas?

Thanks,

Paul Baumann

This message may contain confidential information and is intended for specific recipients unless explicitly noted otherwise. If you have reason to believe you are not an intended recipient of this message, please delete it and notify the sender. This message may not represent the opinion of IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and does not constitute a contract or guarantee. Unencrypted electronic mail is not secure and the recipient of this message is expected to provide safeguards from viruses and pursue alternate means of communication where privacy or a binding message is desired.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

thomas.hawker

Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

Paul,

I don’t believe a relational model can do what you want short of a stored procedure. Store as of 7.6 doesn’t support them, and I kind of doubt they were added for 7.7, but I haven’t studied Glorp yet.

Cheers!

Tom Hawker
Senior Framework Developer
Home	+1 (408) 274-4128	The Environment: *We take it personally*
Office	+1 (408) 576-6591
Mobile	+1 (408) 835-3643

From: [hidden email] [mailto:[hidden email]] On Behalf Of Paul Baumann
Sent: Tuesday, August 11, 2009 9:59 AM
To: VWNC
Subject: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

basicVersionAncestors
"There is probably a faster way to do this. This does a query for each parentRecord."

Any ideas?

Thanks,

Paul Baumann

IMPORTANT NOTICE
Email from OOCL is confidential and may be legally privileged.  If it is not
intended for you, please delete it immediately unread.  The internet
cannot guarantee that this communication is free of viruses, interception
or interference and anyone who communicates with us by email is taken
to accept the risks in doing so.  Without limitation, OOCL and its affiliates
accept no liability whatsoever and howsoever arising in connection with
the use of this email.  Under no circumstances shall this email constitute
a binding agreement to carry or for provision of carriage services by OOCL,
which is subject to the availability of carrier's equipment and vessels and
the terms and conditions of OOCL's standard bill of lading which is also
available at http://www.oocl.com.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Alan Knight-2

Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

In reply to this post by Paul Baumann

That's going to be tricky. Relational databases aren't great at following chains of things.

Probably the easiest thing to do is to read back tuples of all
descendant id , parent id
where the id is <= the largest and >= the smallest. Then sort out the relationship in memory, and then read back the real pundles.

It's the sort of thing I'd find entertaining to write up as a couple of lines of glorp code in my spare time, but unfortunately spare time is well into the negatives right now.

At 12:58 PM 2009-08-11, Paul Baumann wrote:

Content-Language: en-US
Content-Type: multipart/alternative;
boundary="_000_3FCC5C63F3371940900A2160FD399FD3E13FB431atbpixmx02CPEXc_"

I'm looking for a quick way to do a query for a code release tool that is used in-house and is also in the Store Public Repository. Right now the query is slow due to network latency making multiple trips to Oracle for each #parentRecord query.

A Pundle knows what it was based on through DBRecord.trace, and the base version record can be retrieved using #parentRecord. A list of pundles ancestors could be retrieved by asking #parentRecord of the last parentRecord until nil is returned. The current implementation uses a timeStamp directed query to determine the common ancestor of two pundles.

basicVersionAncestors
"There is probably a faster way to do this. This does a query for each parentRecord."
| a b answer |
answer := Array
with: OrderedCollection new
with: OrderedCollection new.
a := self basisPundle.
b := self storePundle.
a = b ifTrue: [b := self releasedPundle].
[
a isNil | b isNil ifTrue: [^answer "No common parent"].
a = b
] whileFalse: [
a timeStamp > b timeStamp
ifTrue: [(a := a parentRecord) isNil ifFalse: [answer first add: a] ]
ifFalse: [(b := b parentRecord) isNil ifFalse: [answer last add: b] ].
].
^answer

Network latency is absolutely destroying performance doing this query. It may take just a few milliseconds to do the query, but the query is frequent enough that this can mean a couple minutes for the first window display. To support new functionality, the common ancestor must be known at window display time. I'm looking for faster options.

The code I showed collects all the ancestors between two pundles, but most senders are only interested in the common ancestor. This kind of query would be trivial in an object database; I'd just execute a data-directed loop on the server. I'm not as familiar with SQL though.

I'm looking for either a single-trip query to answer the common ancestor record for two pundles; or, a single-trip query to answer all ancestor records for one of the pundles. It might be that store already has something like that, but I haven't been able to find it in the code.

Any ideas?

Thanks,

Paul Baumann

This message may contain confidential information and is intended for specific recipients unless explicitly noted otherwise. If you have reason to believe you are not an intended recipient of this message, please delete it and notify the sender. This message may not represent the opinion of IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and does not constitute a contract or guarantee. Unencrypted electronic mail is not secure and the recipient of this message is expected to provide safeguards from viruses and pursue alternate means of communication where privacy or a binding message is desired.
_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Alan Knight [|], Engineering Manager, Cincom Smalltalk

[hidden email]

http://www.cincom.com/smalltalk

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

kobetic

Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

In reply to this post by Paul Baumann

I'm not quite clear on the details here but assuming this is all in the context of version history of a single pundle, how about bringing over the relevant fields for all the version records of the pundle and doing the search in smalltalk ? Maybe you could even cut down the size of the query with some timestamp based constraints.

HTH,

Martin

"Paul Baumann"<[hidden email]> wrote:

> I'm looking for a quick way to do a query for a code release tool that is used in-house and is also in the Store Public Repository. Right now the query is slow due to network latency making multiple trips to Oracle for each #parentRecord query.
>
> A Pundle knows what it was based on through DBRecord.trace, and the base version record can be retrieved using #parentRecord. A list of pundles ancestors could be retrieved by asking #parentRecord of the last parentRecord until nil is returned. The current implementation uses a timeStamp directed query to determine the common ancestor of two pundles.
>
> basicVersionAncestors
> "There is probably a faster way to do this. This does a query for each parentRecord."
> | a b answer |
> answer := Array
> with: OrderedCollection new
> with: OrderedCollection new.
> a := self basisPundle.
> b := self storePundle.
> a = b ifTrue: [b := self releasedPundle].
> [
> a isNil | b isNil ifTrue: [^answer "No common parent"].
> a = b
> ] whileFalse: [
> a timeStamp > b timeStamp
> ifTrue: [(a := a parentRecord) isNil ifFalse: [answer first add: a] ]
> ifFalse: [(b := b parentRecord) isNil ifFalse: [answer last add: b] ].
> ].
> ^answer
>
> Network latency is absolutely destroying performance doing this query. It may take just a few milliseconds to do the query, but the query is frequent enough that this can mean a couple minutes for the first window display. To support new functionality, the common ancestor must be known at window display time. I'm looking for faster options.
>
> The code I showed collects all the ancestors between two pundles, but most senders are only interested in the common ancestor. This kind of query would be trivial in an object database; I'd just execute a data-directed loop on the server. I'm not as familiar with SQL though.
>
> I'm looking for either a single-trip query to answer the common ancestor record for two pundles; or, a single-trip query to answer all ancestor records for one of the pundles. It might be that store already has something like that, but I haven't been able to find it in the code.
>
> Any ideas?
>
> Thanks,
>
> Paul Baumann
>
>
> ________________________________
> This message may contain confidential information and is intended for specific recipients unless explicitly noted otherwise. If you have reason to believe you are not an intended recipient of this message, please delete it and notify the sender. This message may not represent the opinion of IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and does not constitute a contract or guarantee. Unencrypted electronic mail is not secure and the recipient of this message is expected to provide safeguards from viruses and pursue alternate means of communication where privacy or a binding message is desired.
>
> _______________________________________________
> vwnc mailing list
> [hidden email]
> http://lists.cs.uiuc.edu/mailman/listinfo/vwnc
>

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Paul Baumann

Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

In reply to this post by Paul Baumann

I did exactly that. I wrote the SQL to return all the is relationships for the pundle name, indexed them in VW, and then figured out the common ancestor. I'm still integrating it, but it looks like it will be fast enough.

Thanks for all the responses.

Paul Baumann

From: Alan Knight [mailto:[hidden email]]
Sent: Tuesday, August 11, 2009 1:34 PM
To: Paul Baumann; VWNC
Subject: Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles
Importance: High

Content-Language: en-US
Content-Type: multipart/alternative;
boundary="_000_3FCC5C63F3371940900A2160FD399FD3E13FB431atbpixmx02CPEXc_"

I'm looking for a quick way to do a query for a code release tool that is used in-house and is also in the Store Public Repository. Right now the query is slow due to network latency making multiple trips to Oracle for each #parentRecord query.

A Pundle knows what it was based on through DBRecord.trace, and the base version record can be retrieved using #parentRecord. A list of pundles ancestors could be retrieved by asking #parentRecord of the last parentRecord until nil is returned. The current implementation uses a timeStamp directed query to determine the common ancestor of two pundles.

basicVersionAncestors
"There is probably a faster way to do this. This does a query for each parentRecord."
| a b answer |
answer := Array
with: OrderedCollection new
with: OrderedCollection new.
a := self basisPundle.
b := self storePundle.
a = b ifTrue: [b := self releasedPundle].
[
a isNil | b isNil ifTrue: [^answer "No common parent"].
a = b
] whileFalse: [
a timeStamp > b timeStamp
ifTrue: [(a := a parentRecord) isNil ifFalse: [answer first add: a] ]
ifFalse: [(b := b parentRecord) isNil ifFalse: [answer last add: b] ].
].
^answer

Network latency is absolutely destroying performance doing this query. It may take just a few milliseconds to do the query, but the query is frequent enough that this can mean a couple minutes for the first window display. To support new functionality, the common ancestor must be known at window display time. I'm looking for faster options.

The code I showed collects all the ancestors between two pundles, but most senders are only interested in the common ancestor. This kind of query would be trivial in an object database; I'd just execute a data-directed loop on the server. I'm not as familiar with SQL though.

I'm looking for either a single-trip query to answer the common ancestor record for two pundles; or, a single-trip query to answer all ancestor records for one of the pundles. It might be that store already has something like that, but I haven't been able to find it in the code.

Any ideas?

Thanks,

Paul Baumann

This message may contain confidential information and is intended for specific recipients unless explicitly noted otherwise. If you have reason to believe you are not an intended recipient of this message, please delete it and notify the sender. This message may not represent the opinion of IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and does not constitute a contract or guarantee. Unencrypted electronic mail is not secure and the recipient of this message is expected to provide safeguards from viruses and pursue alternate means of communication where privacy or a binding message is desired.
_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Alan Knight [|], Engineering Manager, Cincom Smalltalk

[hidden email]

http://www.cincom.com/smalltalk

Holger Kleinsorgen-4

Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

In reply to this post by Paul Baumann

Paul Baumann wrote:

> I'm looking for a quick way to do a query for a code release tool that
> is used in-house and is also in the Store Public Repository. Right now
> the query is slow due to network latency making multiple trips to
> Oracle for each #parentRecord query.
>
> A Pundle knows what it was based on through DBRecord.trace, and the
> base version record can be retrieved using #parentRecord. A list of
> pundles ancestors could be retrieved by asking #parentRecord of the
> last parentRecord until nil is returned. The current implementation
> uses a timeStamp directed query to determine the common ancestor of
> two pundles.
>

Paul Baumann

Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

Returning all rows and sorting it out in VW cut execution time from three minutes to 12 seconds for our most complex bundle. I'm sure I could cut even more trips by changing the query to take a collection of contained package or bundle names instead of doing each pundle query one at a time.

A query that returns only the parental records I'm interested in would be nice, but data volume is less a factor now than network latency. Besides that, the generic query (that returns all ancestry for a pundle name) can be reused.

Thanks again for all of the suggestions.

Paul Baumann

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Holger Kleinsorgen
Sent: Tuesday, August 11, 2009 5:04 PM
To: VWNC
Subject: Re: [vwnc] [Store query] Faster way to query for common parentRecord of two pundles

Paul Baumann wrote:

maybe it can be solved with two queries by using "common table expressions":

http://wiki.postgresql.org/wiki/CTEReadme

have never user them myself, though, so I'm not sure if this works better than the other proposed solutions ;) _______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

This message may contain confidential information and is intended for specific recipients unless explicitly noted otherwise. If you have reason to believe you are not an intended recipient of this message, please delete it and notify the sender. This message may not represent the opinion of IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and does not constitute a contract or guarantee. Unencrypted electronic mail is not secure and the recipient of this message is expected to provide safeguards from viruses and pursue alternate means of communication where privacy or a binding message is desired.

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc