Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques

32 - Use Stored Procedures And Triggers

Database-intensive application logic will sometimes run faster if it is contained within a SQL Anywhere stored procedure or trigger. One reason might be that SQL Anywhere's procedure language simply runs faster than the application programming language in use. Another reason is that less application-to-database communication is required when repeated SQL operations are embedded within a procedure that is only called once. A third reason is that once a procedure or trigger has been loaded and compiled it is kept in virtual memory and can be used concurrently by several applications or recursively by one application.

Figure 32A shows a PowerBuilder script that updates 1000 rows via PowerScript embedded UPDATE statements. Figure 32B shows a PowerBuilder script that accomplishes the same thing in one quarter the time by calling the stored procedure in Figure 32C.

Figure 32A - Slow PowerBuilder UPDATE Loop

integer ll_primary1 FOR ll_primary1 = 1 TO 1000 UPDATE test1 SET search1 = search1 + 1 WHERE primary1 = :ll_primary1 USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "UPDATE failed" ) RETURN END IF NEXT COMMIT USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "COMMIT failed" ) RETURN END IF

Figure 32B - Fast PowerBuilder Procedure Call

DECLARE test_update_procedure PROCEDURE FOR test_update USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "DECLARE failed" ) RETURN END IF EXECUTE test_update_procedure; IF ( SQLCA.SQLCode <> 0 ) & and ( SQLCA.SQLCode <> 100 ) THEN MessageBox ( "Error", "EXECUTE failed" ) RETURN END IF

Figure 32C - The UPDATE Loop In A Stored Procedure

CREATE PROCEDURE test_update() BEGIN DECLARE ll_primary1 INTEGER; SET ll_primary1 = 1; WHILE ll_primary1 <= 1000 LOOP UPDATE test1 SET search1 = search1 + 1 WHERE primary1 = ll_primary1; IF SQLCODE <> 0 THEN RETURN; END IF; SET ll_primary1 = ll_primary1 + 1; END LOOP COMMIT; IF SQLCODE <> 0 THEN RETURN; END IF; END

---

[Home]

[Table of Contents]

[Previous Section]

[Next Section]