Thread: [cx-oracle-users] Arraysize dependent on run time?
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Moritz L. <mor...@go...> - 2016-08-23 15:43:36
|
Hi, I fetch rows form an Oracle Locator DB with web.py and cx_Oracle! I use the unreleased version of cx_Oracle! Furthermore I read the sdo_geometry objects in the sql-statement and build a json based on this. For a 500m-Tile with 4634 rows I need 0,326 (measured time in the browser= waiting time (DB + Webserver)) For a 500m-Tile with 5081 rows I need 10,1 second??? You can see an overview *500m-Tile_A ( 2311 rows)* *500m-Tile_B (3799 rows)* *400m-Tile (4137 rows)* *500m-Tile_E (4634 rows)* *500m-Tile_C (5081 rows)* *500m-Tile _D (6241 rows)* *sdo_geometry (cx_Oracle)* 0,18s 0,26s 0,27s 0,326s 10,1s 13,77s *Size* 729KB 1,2 MB 1,3 MB 1,4MB 1,6 MB 2 MB Is there anywhere a Threshold? Because the time is increasing and I do not understand why. It does not refer to Browser problems. I think that this is depending on the webserver. Does someone knows this issue? -- *Moritz Liebelt, B. Sc.* Murgstraße 19 76437 Rastatt E-Mail: mor...@go... |
|
From: Moritz L. <mor...@go...> - 2016-08-23 15:40:04
Attachments:
overview.JPG
|
Hi, I fetch rows form an Oracle Locator DB with web.py and cx_Oracle! I use the unreleased version of cx_Oracle! Furthermore I read the sdo_geometry objects in the sql-statement and build a json based on this. For a 500m-Tile with 4634 rows I need 0,326 (measured time in the browser= waiting time (DB + Webserver)) For a 500m-Tile with 5081 rows I need 10,1 second??? You can see an overview in the attachment. Is there anywhere a Threshold? Because the time is increasing and I do not understand why. It does not refer to Browser problems. I think that this is depending on the webserver. Does someone knows this issue? -- *Moritz Liebelt, B. Sc.* Murgstraße 19 76437 Rastatt E-Mail: mor...@go... |
|
From: Shai B. <sh...@pl...> - 2016-08-23 19:42:24
|
Hi Moritz, Two notions come to mind: 1) Are you sure the time is wasted in Python-land? Do you have a way to measure the execution of the query without Python or cx_Oracle being invoved (e.g. in SQL*Plus)? 2) Assuming it is -- I've seen delays on the order of seconds show up when you create a lot of circular references -- the Python GC gets invoked then and can cause surprisingly large "lapses". You can check the documentation of the stdlib's gc module for ways to verify and/or eliminate this issue. HTH, Shai. On Tuesday 23 August 2016 18:43:26 Moritz Liebelt wrote: > Hi, > > I fetch rows form an Oracle Locator DB with web.py and cx_Oracle! I use the > unreleased version of cx_Oracle! Furthermore I read the sdo_geometry > objects in the sql-statement and build a json based on this. > > For a 500m-Tile with 4634 rows I need 0,326 (measured time in the browser= > waiting time (DB + Webserver)) > For a 500m-Tile with 5081 rows I need 10,1 second??? > You can see an overview > *500m-Tile_A ( 2311 rows)* *500m-Tile_B (3799 rows)* *400m-Tile (4137 > rows)* *500m-Tile_E (4634 rows)* *500m-Tile_C (5081 rows)* *500m-Tile _D > (6241 rows)* > *sdo_geometry (cx_Oracle)* 0,18s 0,26s 0,27s 0,326s 10,1s 13,77s > *Size* 729KB 1,2 MB 1,3 MB 1,4MB 1,6 MB 2 MB > > Is there anywhere a Threshold? Because the time is increasing and I do not > understand why. > It does not refer to Browser problems. I think that this is depending on > the webserver. > > Does someone knows this issue? |
|
From: Moritz L. <mor...@go...> - 2016-08-25 09:59:16
|
Hi Shai,
I did a test query in sql Developer and it seems that Oracle is not
responsible for that.
But to fetch all the rows from my Database I have to increase the arraysize
in cx_Oracle (*cursor.arraysize* default=50, my setting = 6.000)
Furthermore it is written in the Oracle Docu
*SET ARRAY[SIZE] {15 | n}
<https://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm#i2698625>*
* Sets the number of rows, called a batch, that SQL*Plus will fetch from
the database at one time.Valid values are 1 to 5000. A large value
increases the efficiency of queries and subqueries that fetch many rows,
but requires more memory.*
And In my case under 5000 rows it neeeds some milliseconds and over 5000
rows it needs some seconds. So is this depending on the arraysize?
2016-08-23 21:42 GMT+02:00 Shai Berger <sh...@pl...>:
> Hi Moritz,
>
> Two notions come to mind:
>
> 1) Are you sure the time is wasted in Python-land? Do you have a way to
> measure the execution of the query without Python or cx_Oracle being
> invoved
> (e.g. in SQL*Plus)?
>
> 2) Assuming it is -- I've seen delays on the order of seconds show up when
> you
> create a lot of circular references -- the Python GC gets invoked then and
> can
> cause surprisingly large "lapses". You can check the documentation of the
> stdlib's gc module for ways to verify and/or eliminate this issue.
>
> HTH,
> Shai.
>
> On Tuesday 23 August 2016 18:43:26 Moritz Liebelt wrote:
> > Hi,
> >
> > I fetch rows form an Oracle Locator DB with web.py and cx_Oracle! I use
> the
> > unreleased version of cx_Oracle! Furthermore I read the sdo_geometry
> > objects in the sql-statement and build a json based on this.
> >
> > For a 500m-Tile with 4634 rows I need 0,326 (measured time in the
> browser=
> > waiting time (DB + Webserver))
> > For a 500m-Tile with 5081 rows I need 10,1 second???
> > You can see an overview
> > *500m-Tile_A ( 2311 rows)* *500m-Tile_B (3799 rows)* *400m-Tile (4137
> > rows)* *500m-Tile_E (4634 rows)* *500m-Tile_C (5081 rows)* *500m-Tile _D
> > (6241 rows)*
> > *sdo_geometry (cx_Oracle)* 0,18s 0,26s 0,27s 0,326s 10,1s 13,77s
> > *Size* 729KB 1,2 MB 1,3 MB 1,4MB 1,6 MB 2 MB
> >
> > Is there anywhere a Threshold? Because the time is increasing and I do
> not
> > understand why.
> > It does not refer to Browser problems. I think that this is depending on
> > the webserver.
> >
> > Does someone knows this issue?
>
> ------------------------------------------------------------
> ------------------
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
--
*Moritz Liebelt, B. Sc.*
Murgstraße 19
76437 Rastatt
E-Mail: mor...@go...
|
|
From: Shai B. <sh...@pl...> - 2016-08-28 20:21:43
|
Then, perhaps you can just avoid doing that?
If I get it right, the ArraySize does not limit the size of overall returned
results, but only the size returned to the client in one batch.
Add to this that, according to the documentation you linked and quoted
yourself, 6,000 is not a valid value...
Why don't you just try leaving that value alone?
On Thursday 25 August 2016 12:59:07 Moritz Liebelt wrote:
> Hi Shai,
>
> I did a test query in sql Developer and it seems that Oracle is not
> responsible for that.
>
> But to fetch all the rows from my Database I have to increase the arraysize
> in cx_Oracle (*cursor.arraysize* default=50, my setting = 6.000)
> Furthermore it is written in the Oracle Docu
>
> *SET ARRAY[SIZE] {15 | n}
> <https://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm#i269862
> 5>* * Sets the number of rows, called a batch, that SQL*Plus will fetch
> from the database at one time.Valid values are 1 to 5000. A large value
> increases the efficiency of queries and subqueries that fetch many rows,
> but requires more memory.*
>
> And In my case under 5000 rows it neeeds some milliseconds and over 5000
> rows it needs some seconds. So is this depending on the arraysize?
>
> 2016-08-23 21:42 GMT+02:00 Shai Berger <sh...@pl...>:
> > Hi Moritz,
> >
> > Two notions come to mind:
> >
> > 1) Are you sure the time is wasted in Python-land? Do you have a way to
> > measure the execution of the query without Python or cx_Oracle being
> > invoved
> > (e.g. in SQL*Plus)?
> >
> > 2) Assuming it is -- I've seen delays on the order of seconds show up
> > when you
> > create a lot of circular references -- the Python GC gets invoked then
> > and can
> > cause surprisingly large "lapses". You can check the documentation of the
> > stdlib's gc module for ways to verify and/or eliminate this issue.
> >
> > HTH,
> >
> > Shai.
> >
> > On Tuesday 23 August 2016 18:43:26 Moritz Liebelt wrote:
> > > Hi,
> > >
> > > I fetch rows form an Oracle Locator DB with web.py and cx_Oracle! I use
> >
> > the
> >
> > > unreleased version of cx_Oracle! Furthermore I read the sdo_geometry
> > > objects in the sql-statement and build a json based on this.
> > >
> > > For a 500m-Tile with 4634 rows I need 0,326 (measured time in the
> >
> > browser=
> >
> > > waiting time (DB + Webserver))
> > > For a 500m-Tile with 5081 rows I need 10,1 second???
> > > You can see an overview
> > >
> > > *500m-Tile_A ( 2311 rows)* *500m-Tile_B (3799 rows)* *400m-Tile (4137
> > >
> > > rows)* *500m-Tile_E (4634 rows)* *500m-Tile_C (5081 rows)* *500m-Tile
> > > _D (6241 rows)*
> > > *sdo_geometry (cx_Oracle)* 0,18s 0,26s 0,27s 0,326s 10,1s 13,77s
> > > *Size* 729KB 1,2 MB 1,3 MB 1,4MB 1,6 MB 2 MB
> > >
> > > Is there anywhere a Threshold? Because the time is increasing and I do
> >
> > not
> >
> > > understand why.
> > > It does not refer to Browser problems. I think that this is depending
> > > on the webserver.
> > >
> > > Does someone knows this issue?
> >
> > ------------------------------------------------------------
> > ------------------
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Moritz L. <mor...@go...> - 2016-08-31 14:56:29
|
No I was wrong the Oracle docu *SET ARRAY[SIZE] {15 | n}
<https://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm#i2698625>
*is
regarding to sql* Plus and is not refering to python.
So it doesn´t matter in my case. But I have some problems with the aslist()
method for objects.
*My case:*
1.) If I only execute the query (cursor.execute(sql) and print the result
the browser needs around 0,8s
2.) If I do the for-loop this way I need 0,2 seconds:
for id, geometry, classname in cursor:
result.append({"building_nr":id,"geometry":{"type":"
polygon","coordinates":geometry,},"polygon_typ":classname,})
retunr result
3.) But If I create a List and iterate over this list I need 11 seconds!!!:
for id, geometry, classname in cursor:
mo = geometry.SDO_ORDINATES.aslist()
result.append({"building_nr":id,"geometry":{"type":"
polygon","coordinates":zip(*[iter(mo)] * ,},"polygon_typ":classname,})
return json.dumps(result)
But sadly I need the coordinates. Is there a workaround for object.aslist()
?
2016-08-28 22:04 GMT+02:00 Shai Berger <sh...@pl...>:
> Then, perhaps you can just avoid doing that?
>
> If I get it right, the ArraySize does not limit the size of overall
> returned
> results, but only the size returned to the client in one batch.
>
> Add to this that, according to the documentation you linked and quoted
> yourself, 6,000 is not a valid value...
>
> Why don't you just try leaving that value alone?
>
> On Thursday 25 August 2016 12:59:07 Moritz Liebelt wrote:
> > Hi Shai,
> >
> > I did a test query in sql Developer and it seems that Oracle is not
> > responsible for that.
> >
> > But to fetch all the rows from my Database I have to increase the
> arraysize
> > in cx_Oracle (*cursor.arraysize* default=50, my setting = 6.000)
> > Furthermore it is written in the Oracle Docu
> >
> > *SET ARRAY[SIZE] {15 | n}
> > <https://docs.oracle.com/cd/B28359_01/server.111/b31189/
> ch12040.htm#i269862
> > 5>* * Sets the number of rows, called a batch, that SQL*Plus will fetch
> > from the database at one time.Valid values are 1 to 5000. A large value
> > increases the efficiency of queries and subqueries that fetch many rows,
> > but requires more memory.*
> >
> > And In my case under 5000 rows it neeeds some milliseconds and over 5000
> > rows it needs some seconds. So is this depending on the arraysize?
> >
> > 2016-08-23 21:42 GMT+02:00 Shai Berger <sh...@pl...>:
> > > Hi Moritz,
> > >
> > > Two notions come to mind:
> > >
> > > 1) Are you sure the time is wasted in Python-land? Do you have a way to
> > > measure the execution of the query without Python or cx_Oracle being
> > > invoved
> > > (e.g. in SQL*Plus)?
> > >
> > > 2) Assuming it is -- I've seen delays on the order of seconds show up
> > > when you
> > > create a lot of circular references -- the Python GC gets invoked then
> > > and can
> > > cause surprisingly large "lapses". You can check the documentation of
> the
> > > stdlib's gc module for ways to verify and/or eliminate this issue.
> > >
> > > HTH,
> > >
> > > Shai.
> > >
> > > On Tuesday 23 August 2016 18:43:26 Moritz Liebelt wrote:
> > > > Hi,
> > > >
> > > > I fetch rows form an Oracle Locator DB with web.py and cx_Oracle! I
> use
> > >
> > > the
> > >
> > > > unreleased version of cx_Oracle! Furthermore I read the sdo_geometry
> > > > objects in the sql-statement and build a json based on this.
> > > >
> > > > For a 500m-Tile with 4634 rows I need 0,326 (measured time in the
> > >
> > > browser=
> > >
> > > > waiting time (DB + Webserver))
> > > > For a 500m-Tile with 5081 rows I need 10,1 second???
> > > > You can see an overview
> > > >
> > > > *500m-Tile_A ( 2311 rows)* *500m-Tile_B (3799 rows)* *400m-Tile
> (4137
> > > >
> > > > rows)* *500m-Tile_E (4634 rows)* *500m-Tile_C (5081 rows)* *500m-Tile
> > > > _D (6241 rows)*
> > > > *sdo_geometry (cx_Oracle)* 0,18s 0,26s 0,27s 0,326s 10,1s 13,77s
> > > > *Size* 729KB 1,2 MB 1,3 MB 1,4MB 1,6 MB 2 MB
> > > >
> > > > Is there anywhere a Threshold? Because the time is increasing and I
> do
> > >
> > > not
> > >
> > > > understand why.
> > > > It does not refer to Browser problems. I think that this is depending
> > > > on the webserver.
> > > >
> > > > Does someone knows this issue?
> > >
> > > ------------------------------------------------------------
> > > ------------------
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
> ------------------------------------------------------------
> ------------------
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
--
*Moritz Liebelt, B. Sc.*
Murgstraße 19
76437 Rastatt
E-Mail: mor...@go...
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X