RE: [sqr-users] improving DB2 SQL select performance.

["Steve Cavill" <steve.cavill@infoclarity.com.au>]

Hi Peter,
1. SQR 8.1.0
2. Windows 2000 professional
3. DB2 8.1
4. Windows 2000 professional (different box to client - this will eventually
move to an AIX box)
5. char, integer and real, about 30 columns, average row length about 500
bytes
6. was 2,000 rows per second until i added "for fetch only" which pushed it
up to about 10,000 rows per second.  But Brio Intelligence on the same box
gets about  20,000 rows per second.

For testing I was just selecting (no printing).  I guess there are some CLI
parms I can fiddle but don't know what they are.  I am intrigued that BI is
faster.  I looked at the DBGPRINT file that BI produces but it gave me no
clues about any defences in the SQL.  I guess there are DB2 tracing options,
but I'm not a DBA so my research skills are a bit limited.

In production we will be running reports to read about 20 million rows, so
that's why I'm looking at performance now.

On a separate performance question, I will also be using SQR to create very
large tables (20 million rows).  I was going to use inserts, but that also
looks a bit slow.  I could use DBLOAD but creating the flat files is a messy
process.  How can I increase the number of records per I/O?

I know these aren't really specifically SQR questions, but I was hoping
there were some DB2 gurus in the group who can give me some tips.  I have
very limited access to clued up DB2 people at the company I am working for.

Thanks, Steve.

-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Peter Burton
Sent: Thursday, 26 February 2004 11:13 AM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] improving DB2 SQL select performance.


Steve,

1) What version of SQR?
2) What platform is SQR running on (OS and Version)
3) What DB2 database version?
4) What platform is DB2 running on (OS and Version)
5) What type of data are you retrieving?
6) What is the transfer rate (how many records per seconds)?

Peter


> -----Original Message-----
> From: sqr-users-admin@sqrug.org@HYPERION  On Behalf Of "Steve Cavill"
> <steve.cavill@infoclarity.com.au>
> Sent: Wednesday, February 25, 2004 6:49 PM
> To:   sqr-users@sqrug.org
> Subject:      RE: [sqr-users] improving DB2 SQL select performance.
>
> Hi Arnon,
> Thanks for the reply
> I was not doing any printing, just begin-select with no prints. (simply to
> test performance)
> Still was a lot slower until a used "for fetch only" on the select.
> Now still a bit slower - I assume there are some CLI parameters I can
> tweak
> but I don't know what they are.
> Steve.
>
> -----Original Message-----
> From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
> Behalf Of Arnon Oppenheimer
> Sent: Thursday, 26 February 2004 4:53 AM
> To: sqr-users@sqrug.org
> Subject: Re: [sqr-users] improving DB2 SQL select performance.
>
>
> Do you Print with SQR ?
> Try run SQR with selects only, without Prints !!
>
> BI just bring data to the PC Memory,
> SQR does many IO while it print, and this makes the difference.
>
> SQR/DB2 use CLI and it performance is perfect.
>
> Arnon
>
> ----- Original Message -----
> From: "Steve Cavill" <steve.cavill@infoclarity.com.au>
> To: <sqr-users@sqrug.org>
> Sent: Friday, February 20, 2004 10:46 PM
> Subject: [sqr-users] improving DB2 SQL select performance.
>
>
> > DB2 8.1 on Windows NT server
> > SQR 8.1
> >
> > Can anyone recommend any "quick fixes" for DB2 select performance.  I am
> > running SQR and Brio intelligence on the same box, using the same DB2
> > connection, to a remote DB2 server, and Intelligence is running much
> faster.
> > I am basically running table scans against large databases - 4 million
> rows.
> >
> > I added FOR FETCH ONLY to the end of my select which doubled the speed.
> >  The -Bnnn option doesn't seem to work with DB2.  Is there a way to
> increase
> > the buffer size?
> >
> > I'll confess I know very little about DB2.  I know Brio Intelligence is
> > using an ODBC connection.  Is SQR using the same connection or does it
> use
> > CLI or something else?  I access DB2 using DB2 connect software.
> >
> > Any tips greatly appreciated.
> >
> > Thanks, Steve.
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>


************************************************************************

If you have received this e-mail in error, please delete it and notify the
sender as soon as possible. The contents of this e-mail may be confidential
and the unauthorized use, copying, or dissemination of it and any
attachments to it, is prohibited.

Internet communications are not secure and Hyperion does not, therefore,
accept legal responsibility for the contents of this message nor for any
damage caused by viruses.  The views expressed here do not necessarily
represent those of Hyperion.

For more information about Hyperion, please visit our Web site at
www.hyperion.com

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users