cx-oracle-users

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Chris D. <cdu...@ya...>]

Henning,

I know what you mean, but the issue is that this "if test" has to go in some sort of function and
be called for each col returned in each row and it's the function call overhead that's really the
issue. I'd like to use the following:

append = rowsOutput.append
for row in data: # where data is the resultset from a query
    rowsGroup = rowsGroup + 1
    append("%s%s%s%s" %(row[0], colSep, row[1], colSep)) # In reality many more columns
    append(recSep)
    if rowsGroup > :
        outFile.write(''.join(rowsOutput))
        rowsGroup = 0
        rowsOutput = []
outFile.write(''.join(rowsOutput))

Now if I need to do the test on None then I believe I need to call a function or have the if test
inline in a loop and with many columns this is quite an overhead. In my test case on a 13 column
table returning 200K rows:

Doing the if test approx 20 CPU secs in Python
No if test approx 16 CPU secs secs in Python

A 20% reduction. I know the numbers here are small but given the app will be running against many
tables concurrently and extracting millions of rows the potential saving is significant.

Thanks for taking the time to respond.

Cheers,

Chris

--- Henning von Bargen <H.v...@t-...> wrote:
> Chris,
> 
> why do you think the simple statement "if x is None: x=''" means an
> overhead?
> It means about 0.000(insert zeroes here)1 percent performance overhead,
> because transferring/outputting the data is what takes most of time.
> 
> And - with Python - it should not mean too much typing overhead.
> You could easily write a function to do this automatically for each
> record.
> 
> HTH
> Henning
> 
> 
> > -----Ursprüngliche Nachricht-----
> > From: Chris Dunscombe <cdu...@ya...>
> > To: cx-oracle-list <cx-...@li...>
> > Subject: [cx-oracle-users] Nulls returned as None and not as 
> > as "empty or zero lenght string"
> > Reply-To: cx-...@li...
> > 
> > All,
> > 
> > Is there anyway that Null values returned from a query can be 
> > an "empty or zero length string"
> > instead of None?
> > 
> > This may seem a strange request as it's easy to use type to 
> > test if a null has been returned as
> > None. However I've a performance critical app where I need to 
> > output "nothing" (zero length /
> > empty string) if the value is null. Hence at present I have 
> > to perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the 
> > database which is quite an overhead when in
> > some cases I need to do it millions and sometimes billions of times.
> > 
> > Thanks,
> > 
> > Chris Dunscombe
> > 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Chris D. <cdu...@ya...>]

Rich,

Thanks for the idea, I'll give it a look.

Chris
--- Richard Moore <ri...@we...> wrote:
> 
> 
> Chris Dunscombe wrote:
> > Now if I need to do the test on None then I believe I need to call a
> > function or have the if test inline in a loop and with many columns
> > this is quite an overhead. In my test case on a 13 column table
> > returning 200K rows:
> 
> If your application is that performance critical then you might
> want to benchmark a few different implementations. eg. using an
> array instead of an if with something like:
> 
> newowner    = [self.newowner, None][self.contactid == self.newowner]
> 
> This is taken from one of my apps, you'll obviously need to
> modify if for yours.
> 
> Rich.
> -- 
> Richard Moore, Principal Software Engineer,
> Westpoint Ltd,
> Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
> Tel: +44 161 237 1028
> Fax: +44 161 237 1031
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Richard M. <ri...@we...>]

Chris Dunscombe wrote:
> Now if I need to do the test on None then I believe I need to call a
> function or have the if test inline in a loop and with many columns
> this is quite an overhead. In my test case on a 13 column table
> returning 200K rows:

If your application is that performance critical then you might
want to benchmark a few different implementations. eg. using an
array instead of an if with something like:

newowner    = [self.newowner, None][self.contactid == self.newowner]

This is taken from one of my apps, you'll obviously need to
modify if for yours.

Rich.
-- 
Richard Moore, Principal Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[D.R. B. <da...@as...>]

Hoi Chris,

Have you considered the following decorate/undecorate pattern?

   SQL:    SELECT strcolumn || 'x' FROM ...

   python: strcolumn =3D row[0][:-1]

As far as I can tell this will give about as much overhead as an if stateme=
nt.
However, only benchmarking your application will tell you what works best.

                                                   Danny


On Thu, May 12, 2005 at 01:22:56AM -0700, Chris Dunscombe wrote:
> Henning,
>=20
> I know what you mean, but the issue is that this "if test" has to go in s=
ome sort of function and
> be called for each col returned in each row and it's the function call ov=
erhead that's really the
> issue. I'd like to use the following:
>=20
> append =3D rowsOutput.append
> for row in data: # where data is the resultset from a query
>     rowsGroup =3D rowsGroup + 1
>     append("%s%s%s%s" %(row[0], colSep, row[1], colSep)) # In reality man=
y more columns
>     append(recSep)
>     if rowsGroup > :
>         outFile.write(''.join(rowsOutput))
>         rowsGroup =3D 0
>         rowsOutput =3D []
> outFile.write(''.join(rowsOutput))
>=20
> Now if I need to do the test on None then I believe I need to call a func=
tion or have the if test
> inline in a loop and with many columns this is quite an overhead. In my t=
est case on a 13 column
> table returning 200K rows:
>=20
> Doing the if test approx 20 CPU secs in Python
> No if test approx 16 CPU secs secs in Python
>=20
> A 20% reduction. I know the numbers here are small but given the app will=
 be running against many
> tables concurrently and extracting millions of rows the potential saving =
is significant.
>=20
> Thanks for taking the time to respond.
>=20
> Cheers,
>=20
> Chris
>=20
> --- Henning von Bargen <H.v...@t-...> wrote:
> > Chris,
> >=20
> > why do you think the simple statement "if x is None: x=3D''" means an
> > overhead?
> > It means about 0.000(insert zeroes here)1 percent performance overhead,
> > because transferring/outputting the data is what takes most of time.
> >=20
> > And - with Python - it should not mean too much typing overhead.
> > You could easily write a function to do this automatically for each
> > record.
> >=20
> > HTH
> > Henning
> >=20
> >=20
> > > -----Urspr?ngliche Nachricht-----
> > > From: Chris Dunscombe <cdu...@ya...>
> > > To: cx-oracle-list <cx-...@li...>
> > > Subject: [cx-oracle-users] Nulls returned as None and not as=20
> > > as "empty or zero lenght string"
> > > Reply-To: cx-...@li...
> > >=20
> > > All,
> > >=20
> > > Is there anyway that Null values returned from a query can be=20
> > > an "empty or zero length string"
> > > instead of None?
> > >=20
> > > This may seem a strange request as it's easy to use type to=20
> > > test if a null has been returned as
> > > None. However I've a performance critical app where I need to=20
> > > output "nothing" (zero length /
> > > empty string) if the value is null. Hence at present I have=20
> > > to perform this "type test" on ALL
> > > columns that don't have a "Not Null" constraint in the=20
> > > database which is quite an overhead when in
> > > some cases I need to do it millions and sometimes billions of times.
> > >=20
> > > Thanks,
> > >=20
> > > Chris Dunscombe
> > >=20
> >=20
> >=20
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_ids93&alloc_id=16281&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >=20
>=20
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around=20
> http://mail.yahoo.com=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] AW: cx-oracle-users digest, Vol 1 #99 - 8 msgs

[Chris D. <cdu...@ya...>]

Danny,

Thanks for the idea, I'll give it a look.

Chris
--- "D.R.   Boxhoorn" <da...@as...> wrote:

> 
> Hoi Chris,
> 
> Have you considered the following decorate/undecorate pattern?
> 
>    SQL:    SELECT strcolumn || 'x' FROM ...
> 
>    python: strcolumn = row[0][:-1]
> 
> As far as I can tell this will give about as much overhead as an if statement.
> However, only benchmarking your application will tell you what works best.
> 
>                                                    Danny
> 
> 
> On Thu, May 12, 2005 at 01:22:56AM -0700, Chris Dunscombe wrote:
> > Henning,
> > 
> > I know what you mean, but the issue is that this "if test" has to go in some sort of function
> and
> > be called for each col returned in each row and it's the function call overhead that's really
> the
> > issue. I'd like to use the following:
> > 
> > append = rowsOutput.append
> > for row in data: # where data is the resultset from a query
> >     rowsGroup = rowsGroup + 1
> >     append("%s%s%s%s" %(row[0], colSep, row[1], colSep)) # In reality many more columns
> >     append(recSep)
> >     if rowsGroup > :
> >         outFile.write(''.join(rowsOutput))
> >         rowsGroup = 0
> >         rowsOutput = []
> > outFile.write(''.join(rowsOutput))
> > 
> > Now if I need to do the test on None then I believe I need to call a function or have the if
> test
> > inline in a loop and with many columns this is quite an overhead. In my test case on a 13
> column
> > table returning 200K rows:
> > 
> > Doing the if test approx 20 CPU secs in Python
> > No if test approx 16 CPU secs secs in Python
> > 
> > A 20% reduction. I know the numbers here are small but given the app will be running against
> many
> > tables concurrently and extracting millions of rows the potential saving is significant.
> > 
> > Thanks for taking the time to respond.
> > 
> > Cheers,
> > 
> > Chris
> > 
> > --- Henning von Bargen <H.v...@t-...> wrote:
> > > Chris,
> > > 
> > > why do you think the simple statement "if x is None: x=''" means an
> > > overhead?
> > > It means about 0.000(insert zeroes here)1 percent performance overhead,
> > > because transferring/outputting the data is what takes most of time.
> > > 
> > > And - with Python - it should not mean too much typing overhead.
> > > You could easily write a function to do this automatically for each
> > > record.
> > > 
> > > HTH
> > > Henning
> > > 
> > > 
> > > > -----Urspr?ngliche Nachricht-----
> > > > From: Chris Dunscombe <cdu...@ya...>
> > > > To: cx-oracle-list <cx-...@li...>
> > > > Subject: [cx-oracle-users] Nulls returned as None and not as 
> > > > as "empty or zero lenght string"
> > > > Reply-To: cx-...@li...
> > > > 
> > > > All,
> > > > 
> > > > Is there anyway that Null values returned from a query can be 
> > > > an "empty or zero length string"
> > > > instead of None?
> > > > 
> > > > This may seem a strange request as it's easy to use type to 
> > > > test if a null has been returned as
> > > > None. However I've a performance critical app where I need to 
> > > > output "nothing" (zero length /
> > > > empty string) if the value is null. Hence at present I have 
> > > > to perform this "type test" on ALL
> > > > columns that don't have a "Not Null" constraint in the 
> > > > database which is quite an overhead when in
> > > > some cases I need to do it millions and sometimes billions of times.
> > > > 
> > > > Thanks,
> > > > 
> > > > Chris Dunscombe
> > > > 
> > > 
> > > 
> > > -------------------------------------------------------
> > > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > > Want to be the first software developer in space?
> > > Enter now for the Oracle Space Sweepstakes!
> > > http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-...@li...
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > > 
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam protection around 
> > http://mail.yahoo.com 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
Discover Yahoo! 
Find restaurants, movies, travel and more fun for the weekend. Check it out! 
http://discover.yahoo.com/weekend.html