RE: BDL Performance Testing

Richard,

There could also be issues with your code - in Informix complex queries
(many tables and joins) were best split into smaller distinct queries for
best performance whilst looping through data.  In SQL Server it is the
reverse, we had to re-engineer most of our critical code to process the
queries as complex queries - SQL Server works best this way !  Another tip
is to prepare as many of your queries as possible, the ODI works faster that
way and makes SQL Server query execution far more efficient.

Sorry I didn't include this in the previous replay.

Darren

-----Original Message-----
From: Richard Turner [mailto:[email protected]]
Sent: 24 February 2005 20:16
To: 4Js User Group
Subject: [fourjs-users] BDL Performance Testing

Hi All,

Does anyone know of a tool or utility I can use to view the database costs a
4GL encounters when the program is executed against a SQL Server database?

On Informix systems, I would normally use a "set explain" command in the 4GL
then, once I executed the program, I can go back and view the ascii file
sqexplain.out.  This was very handy since it recorded all SQL statements
made by the 4GL and it would state whether it did a sequential search, used
an index etc.  It would also give you an estimated database cost for that
sql statement.

I would like to, at minimum find a way to determine if indexes are being
used or not.  Basically, I have a very large program that runs quickly on an
Informix database BUT, when I run the same code against SQL Server, it
crawls at a snails pace.  Equally as bad, it seems to be eating up most of
the CPU cycles on the server so most everything else grinds to a halt too.
I suspect a sequential table scan is happening but can't quite put my finger
on it.  I've briefly experimented with FGLSQLDEBUG but it doesn't seem to
give me the index or cost information I need.

Any ideas would be much appreciated.

Thanks,


Richard


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************