Very odd performance problem

Very odd performance problem

Post by Guillermo Casta?o » Sun, 09 Feb 2003 01:19:38



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

 
 
 

Very odd performance problem

Post by Eric Herbe » Sun, 09 Feb 2003 01:40:54



> 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
***********************************************

 
 
 

Very odd performance problem

Post by Guillermo Casta?o » Sun, 09 Feb 2003 23:43:32


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

 
 
 

Very odd performance problem

Post by Eric Herbe » Mon, 10 Feb 2003 18:16:44



>>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?

>>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.

>>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.

>>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
***********************************************

 
 
 

Very odd performance problem

Post by Guillermo Casta?o » Tue, 11 Feb 2003 07:14:37


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  :(

 
 
 

Very odd performance problem

Post by David William » Tue, 11 Feb 2003 10:52:37




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.
 
 
 

Very odd performance problem

Post by Paul Watso » Tue, 11 Feb 2003 16:18:16


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.


> 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

--
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          #
 
 
 

Very odd performance problem

Post by Eric Herbe » Tue, 11 Feb 2003 19:18:41



>>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  :(

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
***********************************************

 
 
 

Very odd performance problem

Post by Guillermo Casta?o » Tue, 11 Feb 2003 22:07:01


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!

 
 
 

Very odd performance problem

Post by Andy Lennar » Tue, 11 Feb 2003 21:21:03




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?

--

 
 
 

1. Odd performance problem

Hi,

I'm running SQL Server 7 SP3 on Windows 2000, using an ODBC-driven
client application.

I start Enterprise Manager on the local machine running SQL Server and I
navigate until it is showing me the list of tables for my database. Then
I execute my application in another window on the same machine. It
works, and performance is good.

Then I close Enterprise Manager, and performance degrades significantly,
for no apparent reason. If I restart Enterprise Manager and navigate to
the table display, performance returns to normal.

Starting the application first doesn't change the behavior. I tried
rebooting several times.

Has anyone seen this before?

2. TLog Backup Failure

3. Odd Performance Problem

4. change database name

5. Odd view performance

6. AutoNumbering???

7. Odd Performance Issue

8. Database Server Spec. Help!

9. Odd performance dropoff inside a cursor

10. odd performance

11. Odd Performance Spikes in ASE 11.9.2

12. Online 7.11 Select Performance - Odd Results

13. SQL Server Performance Problem - Good query performance, bad update performance