RE: BDL Performance Testing

Some more ideas:-

- Ensure statistics are up to date on all tables.  Out of data stats will
cause indexes to be ignored or used incorrectly.
- For SQL statements which are executed repetitively (ie those within
FOREACH or WHILE loops), PREPARE them first then OPEN, FETCH or EXECUTE them
as needed.  This will mean that they do not need to be re-compiled time
after time.  This can make a huge difference to performance.
- You can always force use of an index with the (INDEX=...) hint in SELECTS,
but these must be PREPAREd or in an SQL/END SQL block.
- SQL Profiler is an invaluable tool for cheking query performance.  You can
even add the execution plan event to the output to see which indexes are
being used.

The MS-SQL ODI guide has a few more hints about adapting code to MS-SQL.

Tim

-----Original Message-----
From: Darren Goddard [mailto:[email protected]] 
Sent: 25 February 2005 09:13
To: [email protected]
Subject: RE: [fourjs-users] BDL Performance Testing


Hi Richard,

You can use SQL Profiler from the Tools menu option in Enterprise Manager.
This allows you to create a trace of the activity on the SQL Server.  This
captures all the activity so you will have to filter out your application
from the results.  If you see something that takes longer than you would
like, you can also re-produce your queries in Query Analyser with a little
preparation.  You can then check the execution plan for the statement and
fine tune indexes etc as required.

HTH

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