Datablade List Forum

RE: Informix Web Datablade speed

Posted By: Donald Payne
Date: Wednesday, 23 July 2003, at 4:09 p.m.

In Response To: RE: Informix Web Datablade speed (jonathan.little@phones4u.co.uk)

You're right, running a SQL query thru Web DataBlade <?MISQL> tag is
always slower than through a client app, sometimes 10x.
I agree with previous writers about (1) Web Blade 4.13+ faster than 4.11;
(2) stored procedure faster than dynamic SQL.

If you still need dynamic SQL, some ideas:

- Try to minimize the interpretation processing work done by the Web Blade
between the <?MISQL> and <?/MISQL> tags. Sometimes you can push that work
into the SQL statement itself. For example, IDS 9.20+ has enhancements
that can do work that you previously had to do in procedural Web Blade
code:

- Functions: UPPER, LOWER, INITCAP, REPLACE, SUBSTR, LPAD, RPAD,
NVL, DECODE, TO_CHAR, TO_DATE
- UNION operator in CREATE VIEW statement
- CASE expression
Can replace code in Web Blade:
$(UPPER), $(LOWER), $(REPLACE), $(SUBSTR), $(ISNULL), $(CONCAT),
$(IF), $(+), $(FIX), etc., etc.

The ideal / extreme case is

<?MISQL SQL="$sql">$1
<?/MISQL>

- Caching of Web Blade code: if you have snippets of code that are
executed in several places, you could write them once in a Web Blade
user-defined (dynamic) tag. This has the side benefit that you can turn
on caching of user-defined tags (In the App Dev Guide version 4.13, see
chapter 9 "Using Dynamic Tags in AppPages", section "Dynamic Tag
WebExplode() Variables", variable MI_WEBTAGSCACHE on page 9-8; In the
Admin Guide version 4.13, see Chapter 9 "Improving Performance", section
"AppPage Caching", subsection "Global Cache For Dynamic Tags and
User-Defined Routine Tags" on page 9-5). Though the tag cache doesn't
directly make <?MISQL> tags execute faster, it saves the overhead (esp.
I/O) of webExplode() having to SELECT the tag from the wbtags table before
exploding it. Web Blade now has a Global Tag cache: one cache of dynamic
tags per database, instead of one per user session. Should save memory in
IDS.

- If you have <?MISQL> tags that merely invokes a SQL function, e.g.
<?MISQL SQL="SELECT CURRENT FROM SysTables WHERE tabId =
1;">$1<?/MISQL>
then you can save the parsing time by creating a UDR tag. For details,
see the Web Blade App Dev Guide, chapter 9 "Using Dynamic Tags in
AppPages". In the above case the UDR would be an SPL function, something
like:

CREATE FUNCTION now()
RETURNING DATETIME YEAR TO SECOND
;
RETURN CURRENT;
END FUNCTION;

You would create the UDR tag using DDW. The tag would be invoked
something like
<?now>
[Note: In the 4.13 App Dev Guide, the syntax diagram on page 10-6 for
passing parameters to the tag disagrees with the example on page 10-7. I
believe the example is correct.]

- Also note: about the SQL statement cache in IDS 9.20+: I don't believe
that SQL executed in a UDR (i.e. <?MISQL> tags processed by WebExplode())
are cached.

Thanks and Regards,
- Don

Donald E. Payne -- Sr. IT Specialist
OLTP Practice, Data Management Services, Software Group, IBM
phone / fax / pager 1-212-745-4380 (internal 243-4380)
mobile office: New York, NY
mailing address: 13 Myrtle Ave., Huntington, NY 11743-3506
Certified IBM Informix Professional
http://www.ibm.com/software/data




"jonathan.li...." <jonathan.little@phones4u.co.uk>
Sent by: forum.subscriber@iiug.org
07/23/2003 07:25 AM


To: datablade-list@iiug.org
cc:
Subject: RE: Informix Web Datablade speed [36]



Carin,

We found using stored procedures called from the Web Pages in the Informix
database were 10x faster than ASP pages.

Are you coding stored procedures and executing them from the web page or
entering select MISQL statements within the web page?



Jonathan

-----Original Message-----
From: CARIN PRETORIUS [mailto:carin@iscw.agric.za]
Sent: 23 July 2003 11:37
To: datablade-list@iiug.org
Subject: Infomix Web Datablade speed [35]


We use the following:

Hardware: Sun Enterprise 450
RAM: 1GB
CPU Processors: 2 x 250 MHz
Hard Drives: 4.2 GB internal
Network: 2 x 100BT
Operating System: Sun Solaris version 2.7
Database: IBM Informix version 9.2.1 uc4
Blades: Informix Web Datablade version web.4.11.uc2
Excalibur Text Search
GIS: ArcInfo version 8.0
ArcView
Spatial Analyst
Spatial Database: SDE version 4
Web Server: I-Planet version 4.0
Map Server: ArcIMS version 3.0

I recently did a test and found that the exact same query in ASP and JSP
are
much faster that the same query using web datablade. What can be the
problem as all my development up to now have been in web datablade and I
need to find a way to correct and upgrade the speed?

Please help!


*************************************************************
This email, and any attachment, is confidential. If you have
received it in error, please delete it from your system.

Do not use or disclose the information in any way, and notify
the sender immediately.

The contents of this message may contain personal views which
are not the views of Phones4U Ltd or any other company within
the Caudwell Group, unless specifically stated.

You may not disclose any information contained herein unless
disclosure is specifically allowed or the information is
publicly available.
*************************************************************

Messages In This Thread

• Infomix Web Datablade speed
  CARIN PRETORIUS -- Wednesday, 23 July 2003, at 6:37 a.m.
  • RE: Informix Web Datablade speed
    jonathan.little@phones4u.co.uk -- Wednesday, 23 July 2003, at 7:25 a.m.
    • RE: Informix Web Datablade speed
      Donald Payne -- Wednesday, 23 July 2003, at 4:09 p.m.
  • Re: Infomix Web Datablade speed
    paul@oninit.com -- Wednesday, 23 July 2003, at 8:02 a.m.

Datablade List Forum is maintained by Administrator with WebBBS 5.12.