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

8 - Think Sets

A relational database system like SQL Anywhere usually works best when your application processes several rows of data at once (set oriented) rather than one row at a time (record oriented). When you think in terms of sets rather than records your solutions are often shorter, faster, and easier to understand and maintain.

Figure 8A shows a record oriented solution to the request "Find all the employees in department 5 and give them a $10,000 salary increase." This PowerBuilder script is 47 lines long and takes almost 8 seconds to execute, whereas the set oriented script in Figure 8B is 1/4 the length and runs in less than two tenths of a second.

Figure 8A - Slow Record Oriented FETCH And UPDATE Loop

long ll_emp_id, ll_dept_id, ll_salary DECLARE test1_cursor CURSOR FOR SELECT emp_id, dept_id, salary FROM test1 USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "DECLARE failed" ) RETURN END IF OPEN test1_cursor; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "OPEN failed" ) RETURN END IF FETCH test1_cursor INTO :ll_emp_id, :ll_dept_id, :ll_salary; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "First FETCH failed" ) RETURN END IF DO WHILE SQLCA.SQLCode = 0 IF ll_dept_id = 5 THEN ll_salary = ll_salary + 10000 UPDATE test1 SET salary = :ll_salary WHERE CURRENT OF test1_cursor; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "UPDATE failed" ) RETURN END IF END IF FETCH test1_cursor INTO :ll_emp_id, :ll_dept_id, :ll_salary; IF ( SQLCA.SQLCode <> 0 ) & AND ( SQLCA.SQLCode <> 100 ) THEN MessageBox ( "Error", "Later FETCH failed" ) RETURN END IF LOOP COMMIT USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "COMMIT failed" ) END IF

Figure 8B - Fast Set Oriented UPDATE Statement

UPDATE test1 SET salary = salary + 10000 WHERE dept_id = 5 USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "UPDATE failed" ) END IF COMMIT USING SQLCA; IF SQLCA.SQLCode <> 0 THEN MessageBox ( "Error", "Commit failed" ) END IF

---

[Home]

[Table of Contents]

[Previous Section]

[Next Section]