Received: by 10.224.180.141 with SMTP id bu13mr2902892qab.2.1353068568432;
Fri, 16 Nov 2012 04:22:48 -0800 (PST)
X-BeenThere: mongodb-user@googlegroups.com
Received: by 10.49.1.5 with SMTP id 5ls98811qei.18.gmail; Fri, 16 Nov 2012
04:22:27 -0800 (PST)
Received: by 10.49.86.106 with SMTP id o10mr833812qez.14.1353068546963;
Fri, 16 Nov 2012 04:22:26 -0800 (PST)
Date: Fri, 16 Nov 2012 04:22:26 -0800 (PST)
From: Brent Gracey <brentgra...@gmail.com>
To: mongodb-user@googlegroups.com
Message-Id: <d6aa28a2-927f-4c56-baf5-7974ceefb298@googlegroups.com>
In-Reply-To: <bee0ef74-bbef-46c5-952c-2443231b1a58@googlegroups.com>
References: <870192d2-906c-4537-a5d4-0baf052ebedd@googlegroups.com>
<e58418ab-e0e6-4f32-b967-d7c430b95d21@googlegroups.com>
<41589e13-27ee-455e-a232-8fb0aeffe9f1@googlegroups.com>
<8cb36b3a-d1aa-4930-82b4-91b19413b3a6@googlegroups.com>
<bee0ef74-bbef-46c5-952c-2443231b1a58@googlegroups.com>
Subject: Re: any alternative to client side processing in place of what
would be a RDMS join?
MIME-Version: 1.0
Content-Type: multipart/mixed;
boundary="----=_Part_1272_30935561.1353068546669"
------=_Part_1272_30935561.1353068546669
Content-Type: multipart/alternative;
boundary="----=_Part_1273_8065097.1353068546669"
------=_Part_1273_8065097.1353068546669
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Hi,
Its a scala application - and I'm a few layers of abstraction above the
actual db connection. But it comes down to
/mongo-java-driver-2.6.5.jar!/com/mongodb/DBTCPConnector.class and if this
is the right documentation
http://www.mongodb.org/display/DOCS/Java+Driver+Concurrency "The Mongo
object maintains an internal pool of connections to the database (default
pool size of 10)"
On Wednesday, November 14, 2012 7:24:26 PM UTC, William Zola wrote:
>
> Hi Brent!
>
> I'm not sure I understand your issues here. You're right that doing the
> lookup on the client side is expensive. I don't understand your concerns
> about running multiple queries, though.
>
> Here is some sample Python code: it only uses one database connection, and
> a maximum of two database cursors:
>
> def setup() :
> port = sys.argv[1]
> conn = Connection('localhost', int(port) )
> return conn.test
>
> def query_dept_emp(db) :
> dept = db.dept
> emp = db.emp
> for ddoc in dept.find().sort("name",1) :
> print "Employees in", ddoc["name"]
> for edoc in emp.find( {"dept": ddoc["_id"] } ).sort("name",1) :
> print " ", edoc["name"]
>
> def main() :
> if len(sys.argv) != 2:
> print "usage: ", sys.argv[0], " <port number>"
> exit(1)
>
> db = setup()
> query_dept_emp(db)
>
> if __name__ == "__main__":
> main()
>
>
> As I said before: this is no different than what an RDBMS engine would
> do. The only difference is that you're running the query on the client
> side, instead of having the server run it for you invisibly on the server
> side.
>
> If you're running into a separate performance issue, then you'll need to
> diagnose that.
>
> It's not clear from your application logs what language you're using, or
> how you've structured your code. If it doesn't look (roughly) like the
> sample above, then you're probably doing something inefficient.
>
> Let me know if you have further questions.
>
> -William
>
> On Tuesday, November 13, 2012 3:10:15 AM UTC-8, Brent Gracey wrote:
>>
>> Hi William - thanks again for the detailed reply
>>
>> I can see how doing this for a query that requires information for one
>> record is efficient, I however need to do a look up for multiple records.
>>
>> If I pull all the detail records in one query - then the client side
>> "detail" look up will be inefficient as there is no "index" concept in the
>> client side code.
>>
>> If I do a query to the db for each detail record then I get 100s of db
>> connections which is also a big overhead.
>>
>> So my understanding is I would need to update my data structure to
>> address this efficiently.
>>
>> On Monday, October 29, 2012 5:40:26 PM UTC, William Zola wrote:
>>>
>>>
>>> Hi Brent!
>>>
>>> Sorry for taking so long to get back to you.
>>>
>>> The answer to your question is: the database is not going to be any more
>>> efficient than doing the queries client side -- IF you have designed your
>>> schema properly.
>>>
>>> 1) Let's begin by looking at how you'd do this in SQL:
>>>
>>> CREATE TABLE dept (deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
>>> dname VARCHAR2(30) );
>>> CREATE TABLE emp (
>>> empid NUMBER CONSTRAINT pk_emp PRIMARY KEY,
>>> ename VARCHAR(20),
>>> deptno NUMER CONSTRAINT fk_deptno FOREIGN KEY REFERENCES
>>> dept(deptno) );
>>>
>>> First off: this creates two indexes "behind your back": one on
>>> dept.deptno, one on emp.empid.
>>>
>>> Second: consider how you would query these tables:
>>>
>>> SELECT empid, ename FROM emp, dept
>>> WHERE
>>> dept.dname = 'SALES'
>>> JOIN
>>> dept.deptno = emp.deptno
>>> ORDER BY ename;
>>>
>>> Note that unless you index 'emp.deptno', this query is going to be slow
>>> (since it will otherwise perform a full table scan on 'emp'). Unless you
>>> index 'dept.dname', it will have to perform a full table scan on 'dept'.
>>>
>>> You're now at no less than FOUR indexes.
>>>
>>> This query is going to force the RDBMS to do the following:
>>> A) Read the 'dept.dname' index to find the location of the row(s)
>>> where "dname = 'SALES'"
>>> B) Fetch the matching row(s) from the 'dept' table, and save off the
>>> set of 'deptno' values
>>> C) Read the 'emp.deptno' index to find the locations of the matching
>>> rows in the 'emp' table
>>> D) Fetch the matching rows from the 'emp' table
>>> E) Sort the result set in-memory
>>>
>>> 2) Now, let's consider how to do this in MongoDB. For legibility, I'm
>>> going to use integers for the _id field, but everything I will do will work
>>> with ObjectIDs as well:
>>>
>>> > db.dept.save( {_id: 1, name: 'Sales'} );
>>> > db.dept.save( {_id: 2, name: 'Marketing'} );
>>> > db.dept.save( {_id: 3, name: 'Support'} );
>>>
>>> > db.emp.save( { _id: 1, name: 'Ben', dept: 1 } );
>>> > db.emp.save( { _id: 2, name: 'William', dept: 3 } );
>>> > db.emp.save( { _id: 3, name: 'Jenna', dept: 3 } );
>>> > db.emp.save( { _id: 4, name: 'Steven', dept: 3 } );
>>>
>>> To make this efficient, I'll create a secondary index on the 'dept'
>>> field in the 'emp' collection, and the 'name' field in the 'dept'
>>> collection:
>>>
>>> > db.emp.ensureIndex( dept: 1 );
>>> > db.dept.ensureIndex( name: 1 );
>>>
>>> By default, there are already indexes on "dept._id" and "emp._id".
>>>
>>> To find all of the employees in the "Support" department, I need to do
>>> two queries:
>>>
>>> > result = db.dept.findOne({name: 'Support'}, {_id:1} );
>>> { "_id" : 3 }
>>> > desired_dept = result["_id"];
>>> 3
>>> > db.emp.find( { dept: desired_dept }).sort({name:1}).pretty();
>>> { "_id" : 3, "name" : "Jenna", "dept" : 3 }
>>> { "_id" : 4, "name" : "Steven", "dept" : 3 }
>>> { "_id" : 2, "name" : "William", "dept" : 3 }
>>>
>>> In terms of the actual work done, there are the same number of indexes
>>> involved, and the same number of operations involved as with the SQL
>>> example. The only difference is that I had to build one portion of the
>>> query in the client side instead of on the server side.
>>>
>>> Let me know if you have further questions.
>>>
>>> -William
>>>
>>>
>>>
>>> On Thursday, October 18, 2012 3:19:54 PM UTC-7, Brent Gracey wrote:
>>>>
>>>> Hi - thanks for the comprehensive reply - I'm still working through
>>>> most of the links you have suggested. One thing I would like to clarify -
>>>> for your comment "suggest staying with the simple strategy of doing joins
>>>> in your client application. The database would have to do these anyway:"
>>>>
>>>> Yes the database application would have to join - but assuming it uses
>>>> an index, isn't the database operation not much more efficient than
>>>>
>>>> for all x in array X
>>>> Look up Y[x]
>>>>
>>>> My assumptions for this are:
>>>>
>>>> There is overhead in maintaining DB indexes - this has already been
>>>> "expended effort" at the time of getting data
>>>> Getting all the data from the DB to the client application loses the
>>>> index info
>>>> Repeating the effort of generating an index (not sure how many
>>>> languages would support that - any Scala specific thoughts welcome) is
>>>> duplicated effort
>>>> Not doing something to replicate index functionality will make the look
>>>> up in Y much less efficient.
>>>>
>>>> So I can't see how client side processing can be as efficient as
>>>> getting the db to do it - and as one scales - you either get to a point of
>>>> having to do a large data restructure or live with the extra overhead.
>>>>
>>>> Main point of the post if for people to point out if any of my
>>>> assumptions are incorrect.
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>> On Sunday, October 7, 2012 11:06:38 AM UTC+1, Brent Gracey wrote:
>>>>>
>>>>> If a data set & use cases are focused on "relational" work - would
>>>>> that reduce the fit of Mongodb for the project? For example a "friendship"
>>>>> data set where questions like please get me the detailed info of users
>>>>> (held in person_info) who interacted with each other (held in person
>>>>> interaction) who bought product X ( held in product and purchase_history
>>>>> collections) at event Y.
>>>>>
>>>>> Would it be possible to use a map reduce function which operates over
>>>>> multiple tables?
>>>>>
>>>>> I guess foursquare have some way of answering those sorts of questions
>>>>> efficiently with Mongo!
>>>>>
>>>>> (this is a shortened version of half of post
>>>>> https://groups.google.com/forum/?fromgroups=#!searchin/mongodb-user/guidance/mongodb-user/dDEynRftg8E/IluWzJcABwQJ -
>>>>> maybe the length of that post was too much!)
>>>>>
>>>>> Regards,
>>>>> Brent
>>>>>
>>>>
------=_Part_1273_8065097.1353068546669
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Hi,<div><br></div><div>Its a scala application - and I'm a few layers=
of abstraction above the actual db connection. But it comes down to /mongo=
-java-driver-2.6.5.jar!/com/mongodb/DBTCPConnector.class and if this is the=
right documentation <a href=3D"http://www.mongodb.org/display/DOCS/Ja=
va+Driver+Concurrency">http://www.mongodb.org/display/DOCS/Java+Driver+Conc=
urrency</a> "<span style=3D"color: rgb(76, 58, 44); font-family: helve=
tica, arial, sans-serif; font-size: 13.333333015441895px; line-height: 21.3=
22221755981445px; background-color: rgb(243, 244, 235); ">The Mongo object =
maintains an internal pool of connections to the database (default pool siz=
e of 10)"</span><br><br><br><br>On Wednesday, November 14, 2012 7:24:26 PM =
UTC, William Zola wrote:<blockquote class=3D"gmail_quote" style=3D"margin: =
0;margin-left: 0.8ex;border-left: 1px #ccc solid;padding-left: 1ex;">Hi Bre=
nt!<br><br>I'm not sure I understand your issues here. You're right t=
hat doing the lookup on the client side is expensive. I don't underst=
and your concerns about running multiple queries, though. <br><br>Her=
e is some sample Python code: it only uses one database connection, and a m=
aximum of two database cursors:<span style=3D"font-family:courier new,monos=
pace"></span><br><div style=3D"margin-left:40px"><span style=3D"font-family=
:courier new,monospace"></span><br><span style=3D"font-family:courier new,m=
onospace">def setup() :</span><br><span style=3D"font-family:courier new,mo=
nospace"> port =3D sys.argv[1]</span><br><span style=3D"f=
ont-family:courier new,monospace"> conn =3D Connection('l=
ocalhost', int(port) )</span><br><span style=3D"font-family:courier new,mon=
ospace"> return conn.test</span><br><span style=3D"font-f=
amily:courier new,monospace"></span><br><span style=3D"font-family:courier =
new,monospace">def query_dept_emp(db) :</span><br><span style=3D"font-famil=
y:courier new,monospace"> dept =3D db.dept</span><br><spa=
n style=3D"font-family:courier new,monospace"> emp =3D db=
.emp</span><br><span style=3D"font-family:courier new,monospace"> &nbs=
p; for ddoc in dept.find().sort("name",1) :</span><br><span style=3D"=
font-family:courier new,monospace"> &nbs=
p; print "Employees in", ddoc["name"]</span><br><span style=3D"font-family:=
courier new,monospace"> for edoc =
in emp.find( {"dept": ddoc["_id"] } ).sort("name",1) :</span><br><span styl=
e=3D"font-family:courier new,monospace">  =
; print " ", edoc["name"]</span><br><sp=
an style=3D"font-family:courier new,monospace"></span><br><span style=3D"fo=
nt-family:courier new,monospace">def main() :</span><br><span style=3D"font=
-family:courier new,monospace"> if len(sys.argv) !=3D 2:<=
/span><br><span style=3D"font-family:courier new,monospace"> &nb=
sp; print "usage: ", sys.argv[0], " <port number=
>"</span><br><span style=3D"font-family:courier new,monospace"> &nb=
sp; exit(1)</span><br><span style=3D"font-fam=
ily:courier new,monospace"></span><br><span style=3D"font-family:courier ne=
w,monospace"> db =3D setup()</span><br><span style=3D"fon=
t-family:courier new,monospace"> query_dept_emp(db)</span=
><br><span style=3D"font-family:courier new,monospace"></span><br><span sty=
le=3D"font-family:courier new,monospace">if __name__ =3D=3D "__main__":</sp=
an><br><span style=3D"font-family:courier new,monospace"> =
main()</span><br><span style=3D"font-family:courier new,monospace"> =
</span><br></div><br>As I said before: this is no different than what an RD=
BMS engine would do. The only difference is that you're running the q=
uery on the client side, instead of having the server run it for you invisi=
bly on the server side.<br><br>If you're running into a separate performanc=
e issue, then you'll need to diagnose that. <br><br>It's not clear fr=
om your application logs what language you're using, or how you've structur=
ed your code. If it doesn't look (roughly) like the sample above, the=
n you're probably doing something inefficient.<br><br>Let me know if you ha=
ve further questions.<br><br> -William <br><br>On Tuesday, November 13=
, 2012 3:10:15 AM UTC-8, Brent Gracey wrote:<blockquote class=3D"gmail_quot=
e" style=3D"margin:0;margin-left:0.8ex;border-left:1px #ccc solid;padding-l=
eft:1ex">Hi William - thanks again for the detailed reply<div><br></div><di=
v>I can see how doing this for a query that requires information for one re=
cord is efficient, I however need to do a look up for multiple records.</di=
v><div><br></div><div>If I pull all the detail records in one query - then =
the client side "detail" look up will be inefficient as there is no "index"=
concept in the client side code.</div><div><br></div><div>If I do a query =
to the db for each detail record then I get 100s of db connections which is=
also a big overhead.</div><div><br></div><div>So my understanding is I wou=
ld need to update my data structure to address this efficiently.<br><b=
r>On Monday, October 29, 2012 5:40:26 PM UTC, William Zola wrote:<blockquot=
e class=3D"gmail_quote" style=3D"margin:0;margin-left:0.8ex;border-left:1px=
#ccc solid;padding-left:1ex"><br>Hi Brent!<br><br>Sorry for taking so long=
to get back to you. <br><br>The answer to your question is: the data=
base is not going to be any more efficient than doing the queries client si=
de -- IF you have designed your schema properly.<br><br>1) Let's begin by l=
ooking at how you'd do this in SQL:<br><br>CREATE TABLE dept (deptno NUMBER=
CONSTRAINT pk_dept PRIMARY KEY, <br> &n=
bsp; dname VARCHAR2(30) );<br>CREATE TABLE emp (<br>  =
; empid NUMBER CONSTRAINT pk_emp PRIMARY KEY, <br> &=
nbsp; ename VARCHAR(20),<br>  =
; deptno NUMER CONSTRAINT fk_deptno FOREIGN KEY REF=
ERENCES dept(deptno) );<br><br>First off: this creates two indexes "behind =
your back": one on dept.deptno, one on emp.empid.<br><br>Second: consider h=
ow you would query these tables:<br><br> SELECT empid, ename FROM emp,=
dept <br> WHERE <br> =
dept.dname =3D 'SALES' <br> JOIN<br> &n=
bsp; dept.deptno =3D emp.deptno<br> &nbs=
p; ORDER BY ename;<br><br>Note that unless you index 'emp.deptno', this que=
ry is going to be slow (since it will otherwise perform a full table scan o=
n 'emp'). Unless you index 'dept.dname', it will have to perform a fu=
ll table scan on 'dept'.<br><br>You're now at no less than FOUR indexes.<br=
><br>This query is going to force the RDBMS to do the following:<br> =
A) Read the 'dept.dname' index to find the location of the row(s) where "dn=
ame =3D 'SALES'"<br> B) Fetch the matching row(s) from the 'dept' tab=
le, and save off the set of 'deptno' values<br> C) Read the 'emp.dept=
no' index to find the locations of the matching rows in the 'emp' table<br>=
D) Fetch the matching rows from the 'emp' table<br> E) Sort th=
e result set in-memory<br><br>2) Now, let's consider how to do this in Mong=
oDB. For legibility, I'm going to use integers for the _id field, but=
everything I will do will work with ObjectIDs as well:<br><br>> db.dept=
.save( {_id: 1, name: 'Sales'} );<br>> db.dept.save( {_id: 2, name: 'Mar=
keting'} ); <br>> db.dept.save( {_id: 3, name: 'Support'} );<br><br>>=
db.emp.save( { _id: 1, name: 'Ben', dept: 1 } );<br>> db.emp.save( { _i=
d: 2, name: 'William', dept: 3 } );<br>> db.emp.save( { _id: 3, name: 'J=
enna', dept: 3 } );<br>> db.emp.save( { _id: 4, name: 'Steven', dept: 3 =
} );<br><br>To make this efficient, I'll create a secondary index on the 'd=
ept' field in the 'emp' collection, and the 'name' field in the 'dept' coll=
ection:<br><br>> db.emp.ensureIndex( dept: 1 );<br>> db.dept.ensureIn=
dex( name: 1 );<br><br>By default, there are already indexes on "dept._id" =
and "emp._id".<br><br>To find all of the employees in the "Support" departm=
ent, I need to do two queries:<br><br> > result =3D db=
.dept.findOne({name: 'Support'}, {_id:1} );<br> { "_id" :=
3 }<br> > desired_dept =3D result["_id"];<br> &n=
bsp; 3<br> > db.emp.find( { dept: desired_dept }=
).sort({name:1}).pretty();<br> { "_id" : 3, "name" : "Jen=
na", "dept" : 3 }<br> { "_id" : 4, "name" : "Steven", "de=
pt" : 3 }<br> { "_id" : 2, "name" : "William", "dept" : 3=
}<br> <br>In terms of the actual work done, there are th=
e same number of indexes involved, and the same number of operations involv=
ed as with the SQL example. The only difference is that I had to buil=
d one portion of the query in the client side instead of on the server side=
.<br><br>Let me know if you have further questions.<br><br> -William <=
br><br><br><br>On Thursday, October 18, 2012 3:19:54 PM UTC-7, Brent Gracey=
wrote:<blockquote class=3D"gmail_quote" style=3D"margin:0;margin-left:0.8e=
x;border-left:1px #ccc solid;padding-left:1ex">Hi - thanks for the com=
prehensive reply - I'm still working through most of the links you hav=
e suggested. One thing I would like to clarify - for your comment "suggest =
staying with the simple strategy of doing joins in your client application.=
The database would have to do these anyway:"<div><br></div><div>Yes =
the database application would have to join - but assuming it uses an index=
, isn't the database operation not much more efficient than </div><div=
><br></div><div>for all x in array X</div><div> Look up Y[x]</=
div><div><br></div><div>My assumptions for this are:</div><div><br></div><d=
iv>There is overhead in maintaining DB indexes - this has already=
been "expended effort" at the time of getting data</div><div>Getting all t=
he data from the DB to the client application loses the index info</div><di=
v>Repeating the effort of generating an index (not sure how many languages =
would support that - any Scala specific thoughts welcome) is duplicated eff=
ort</div><div>Not doing something to replicate index functionality will mak=
e the look up in Y much less efficient.</div><div><br></div><div>So I can't=
see how client side processing can be as efficient as getting the db to do=
it - and as one scales - you either get to a point of having to do a large=
data restructure or live with the extra overhead.</div><div><br></div><div=
>Main point of the post if for people to point out if any of my assumptions=
are incorrect.</div><div><br></div><div>Thanks</div><div><br></div><div><b=
r></div><div> <br>On Sunday, October 7, 2012 11:06:38 AM UTC+1, Brent =
Gracey wrote:<blockquote class=3D"gmail_quote" style=3D"margin:0;margin-lef=
t:0.8ex;border-left:1px #ccc solid;padding-left:1ex">If a data set & us=
e cases are focused on "relational" work - would that reduce the fit of Mon=
godb for the project? For example a "friendship" data set where questions l=
ike please get me the detailed info of users (held in person_info) who=
interacted with each other (held in person interaction) who bought product=
X ( held in product and purchase_history collections) at event Y. <di=
v><br></div><div>Would it be possible to use a map reduce function which op=
erates over multiple tables?<br><div><br></div><div>I guess foursquare have=
some way of answering those sorts of questions efficiently with Mong=
o!</div><div><br></div><div>(this is a shortened version of half of post&nb=
sp;<a href=3D"https://groups.google.com/forum/?fromgroups=3D#!searchin/mong=
odb-user/guidance/mongodb-user/dDEynRftg8E/IluWzJcABwQJ" target=3D"_blank">=
https://groups.google.<wbr>com/forum/?fromgroups=3D#!<wbr>searchin/mongodb-=
user/<wbr>guidance/mongodb-user/<wbr>dDEynRftg8E/IluWzJcABwQJ</a> - ma=
ybe the length of that post was too much!)</div><div><br></div><div>Regards=
,</div><div>Brent </div></div></blockquote></div></blockquote></blockq=
uote></div></blockquote></blockquote></div>
------=_Part_1273_8065097.1353068546669--
------=_Part_1272_30935561.1353068546669--
Message from discussion any alternative to client side processing in place of what would be a RDMS join?
| Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy |
| ©2013 Google |