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