Very Computer

Hi, in order to generate a very heavy report, i'm populating two temp
tables like:

SELECT ....
FROM A, B
INTO Temp MyTable1

SELECT ....
FROM MyTable1, C, D
INTO Temp FinalTable

If i run the statements directly from dbAccess, the process takes
around 10 minutes (i'm talking of a 35GB database) but when i run it
from a C/S application, after 1 hour 21 minutes the process isn't over
and finally raises a: "Could not do a physical-order read to fetch
next row".

The C/S reports worked perfectly until last monday when the server had
some problems.

What should i look for and what can be the problem? Can it be a
Informix-Connect problem due to it only fails on C/S applications?
Thanks a lot

First of all I would recommend that you add the "with no log"
clause. This ensures that the inserts into the temporary
tables will not be logged. Make sure that you have configured
at least one temporary dbspace ("T" flag in "onstat -d"), better
would be 2 or more temp. dbspaces (allowing IFMX to fragment the
temp. table). Remember to change your $ONCONFIG DBSPACETEMP
parameter after adding additional temp. dbspaces.

Second you should check the isolation level of your client application
and the "set lock mode to wait <sec>" settings.
Have you used the same settings in you your "dbaccess"-test ?

I doubt that the above 2 select's are all what the application is
doing. Is the application not selecting the rows from "FinalTable" ?
If yes, the network might also be a possible bottleneck.

You might download the "lockwt" utility from my website for
analyzing lock conflicts:

http://www.herber-consulting.de/cgi-bin/MainDriver.pl?action=IfmxUtil

Best regards

Eric
--
IT-Consulting Herber
WWW:   http://www.herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

Quote:> First of all I would recommend that you add the "with no log"
> clause.

I'm not a very experienced Informix user, is the clause write at the
end of the statement?

Quote:> Second you should check the isolation level of your client application
> and the "set lock mode to wait <sec>" settings.
> Have you used the same settings in you your "dbaccess"-test ?

I'm usign the default installation settings right now. But the odd
thing is that all worked perfectly until last friday. The report took
the same time on dbAccess and C/S; that's why i think that something
is wrong with the server because the clients hadn't been changed.

Quote:> I doubt that the above 2 select's are all what the application is
> doing. Is the application not selecting the rows from "FinalTable" ?

Yep, i'm retrieving the data. But on my test i'm only creating both
temp tables and not reading the final table in order to isolate the
problem.

Quote:> You might download the "lockwt" utility from my website for
> analyzing lock conflicts

I'll do it... but the lock problems shouldn't be the same on dbAccess
and C/S? Does the Informix-Connect (on the server) has a particular
configuration?

Thanks again for your help

The syntax for selecting into temp tables is:

select * from <original_table>
into temp <temp_table> with no log;

So you have to specify the "with no log" at
the end of the statement.

Make sure that you ran 'updstat statistics'.

Are the statements prepared at the client side ?

If you are really performing the same queries on the
client- and server-side (dbaccess), this big difference
in execution time is not normal.
But I doubt that the queries are exactly the
same.
Maybe the queries are first prepared on the client
and some parts of the query have question marks
('?') as placeholders which will be later filled thru
host variables.
So the optimizer might decicde to choose a different
execution plan as if you are directly specifying the
values in your dbaccess query.

You might add an "set explain on" to the query on the
client side and compare the 'sqexplain.out' with
your dbaccess query. This should give you a clue.

HTH.

Best regards

Eric
--
IT-Consulting Herber
WWW:   http://www.herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

Quote:> Are the statements prepared at the client side ?

Yep, they're parameterized queries.

Quote:> Maybe the queries are first prepared on the client
> and some parts of the query have question marks
> ('?') as placeholders which will be later filled thru
> host variables.

Nop, they're parameterized queries but all the values are provided for
the client application.

Quote:> So the optimizer might decicde to choose a different
> execution plan as if you are directly specifying the
> values in your dbaccess query.

I already changed the queries and send them without any parameters
(writing the SQL sentence as a single string and concatenating the
values directly into the string) in order to isolate the parameterized
differences. The result is the same, the query executed from the C/S
application never ends (already using the with no log too) and the
same queries (the application exports them to a file) executes in a
few minutes on dbAccess.

I'm completely lost  :(

Quote:

> I already changed the queries and send them without any parameters
> (writing the SQL sentence as a single string and concatenating the
> values directly into the string) in order to isolate the parameterized
> differences. The result is the same, the query executed from the C/S
> application never ends (already using the with no log too) and the
> same queries (the application exports them to a file) executes in a
> few minutes on dbAccess.

> I'm completely lost  :(

 Check PC is listed in hosts file on server. This could be a DNS-type
problem.

As others have said stick 'with no log' on the temp tables.  This
should reduce your 10 minute dbaccess session quite a bit

Have you up'd FET_BUF_SIZE or is it still at the default?

Also how much of the data is being passed back to the C/S app, can
you do more processing on the server end and not send so much data
back to the PC for processing.  I've seen 18 minute C/S reports
drop to a number a of second just by moving some the processing
back into stored procedures.  

Are you the same isolation level in both cases.

--
Paul Watson             #          
Oninit Ltd              # Growing old is mandatory
Tel: +44 1436 672201    # Growing up is optional
Fax: +44 1436 678693    #
Mob: +44 7818 003457    #
www.oninit.com          #

Post the 'sqexplain.out' from both queries.
This should give us a clue.

--

Best regards

Eric
--
IT-Consulting Herber
WWW:   http://www.herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

Well, i finally found the problem and it was my error. Nothing to do
with Informix.
In the SQL sentence i was using, one of the closing parenthesys was
misplaced, and changed all the semantic of the Query. Moving the
parenthesys to the right position solved all the problem.

Thanks a lot for all your advices and help!

Quote:>> Are the statements prepared at the client side ?

>Yep, they're parameterized queries.

>> Maybe the queries are first prepared on the client
>> and some parts of the query have question marks
>> ('?') as placeholders which will be later filled thru
>> host variables.

>Nop, they're parameterized queries but all the values are provided for
>the client application.

>> So the optimizer might decicde to choose a different
>> execution plan as if you are directly specifying the
>> values in your dbaccess query.

>I already changed the queries and send them without any parameters
>(writing the SQL sentence as a single string and concatenating the
>values directly into the string) in order to isolate the parameterized
>differences. The result is the same, the query executed from the C/S
>application never ends (already using the with no log too) and the
>same queries (the application exports them to a file) executes in a
>few minutes on dbAccess.

>I'm completely lost  :(

Is the C/S application doing anything with the information as it comes
back? Maybe it's a data dependant problem, and the application is stuck
in its own compute (or similar) bound loop, and it just seems like it's
the query taking forever?

--