The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

This is a followup to SA12 UDF in select list evaluation time based on a simple reproducible sent to me by ODL_Sybase (thank you!)


The presence of a COMPUTE constraint containing a SELECT FROM another table seems to cause bad query performance starting after SQL Anywhere version 9: version 10 crashes, versions 11 through 16 run slower possibly because a UDF call is evaluated many times instead of once.

( the "seems to" and "possibly because" are weasel phrases to cover my ignorance of how the query optimizer works... as in "firing a bullet into someone's chest seems to cause death, possibly because of all the damage done" :)

alt text

Here is the reproducible code, output shown first (code slightly modified to run in all versions)...

Execution time: 0.612 seconds
@@VERSION,with Computed Column,without Computed Column
'9.0.2.3951',1,1

crash dump in V10...
VERSION=10.0.1.3579
FILENAME=C:\ProgramData\SQL Anywhere 10\diagnostics\SA10_20140228_051543_6952.crash_log

Execution time: 3.215 seconds
@@VERSION,with Computed Column,without Computed Column
'11.0.1.2960',13001,1

Execution time: 2.099 seconds
@@VERSION,with Computed Column,without Computed Column
'12.0.1.3298',13001,1

Execution time: 2.211 seconds
@@VERSION,with Computed Column,without Computed Column
'16.0.0.1691',13001,1

-----

-- important; we use it for setting scrollbars; without it computed column doesn't matter
set option row_counts = 'On';

BEGIN
   drop table "DBA"."testPoints";
   EXCEPTION WHEN OTHERS THEN
END;

-- referenced table
CREATE TABLE "DBA"."testPoints" (
    "point" tinyint NOT NULL,
    "points" numeric(10, 4) NULL,
    PRIMARY KEY ( "point" )
);

insert testPoints values(0, 1);
insert testPoints values(1, 10);
insert testPoints values(2, 100);
insert testPoints values(3, 1000);

BEGIN
   drop table "DBA"."testProducts";
   EXCEPTION WHEN OTHERS THEN
END;

-- main product table
CREATE TABLE "DBA"."testProducts" (
    "id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "Name" CHAR(100) NULL,
    "price" "money" NULL,
    "pointff" "money" NULL COMPUTE( (select points from testPoints where point = (id & 4)) ),
    PRIMARY KEY ( "id" )
);

-- called UDF
BEGIN
   drop FUNCTION "DBA"."testGetPrice"; 
   EXCEPTION WHEN OTHERS THEN
END;

create FUNCTION "DBA"."testGetPrice"( productId integer)
RETURNS money
DETERMINISTIC
BEGIN
    DECLARE res INTEGER;
    set calls = calls + 1;
    select price into res from testProducts where id = productId;
    if productId & 1 = 0 then 
        set res = res + 1
    end if;
    RETURN res;
END
;

-- testing cursor behavior
BEGIN
   drop FUNCTION "DBA"."testCursorForDefinedData"; 
   EXCEPTION WHEN OTHERS THEN
END;

create FUNCTION "DBA"."testCursorForDefinedData"(  )
RETURNS INTEGER
DETERMINISTIC
BEGIN
    DECLARE res INTEGER;
    set res = -1;
    set calls = 0;
    for myC as testC DYNAMIC SCROLL CURSOR FOR 
        select name, testGetPrice(id) from testProducts order by name DO 
        if calls > 1 THEN 
            return calls
        ELSE 
            return 0;
        end if
    end for;
    RETURN res;
END;

-- UDF call counter
BEGIN
    create variable calls integer;
   EXCEPTION WHEN OTHERS THEN
END;

-- result store
BEGIN
    create variable "with Computed Column" integer;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
    create variable "without Computed Column" integer;
   EXCEPTION WHEN OTHERS THEN
END;

-- fill product table; maxRec - # records to insert
BEGIN
   drop FUNCTION "DBA"."testCursorForVariousData"; 
   EXCEPTION WHEN OTHERS THEN
END;

create PROCEDURE "DBA"."testCursorForVariousData"( in maxRec integer)
BEGIN
    declare i integer;
    truncate table testProducts;
        set i = 0;
        while i < maxRec loop
            insert testProducts(id, name, price) values(default, 'p' || i, i);
            set i = i + 1
        end loop;
        commit;
        if testCursorForDefinedData() > 0 then
            return calls
        end if;
    return 0;
END;

// let's test bad behavior
call testCursorForVariousData( 13000);

set "with Computed Column" = calls;

-- drop computed column
set calls = 0;
ALTER TABLE "DBA"."testProducts" DROP "pointff";

-- test proper behavior
call testCursorForVariousData( 13000);

set "without Computed Column" = calls;

select @@VERSION, "with Computed Column", "without Computed Column";

asked 28 Feb '14, 06:24

Breck%20Carter's gravatar image

Breck Carter
26.6k418576824
accept rate: 21%

edited 28 Feb '14, 06:47

FWIW, your v10 build is real old, with v10.0.4181 (one of the last ones released), the code does not crash the server...

(01 Mar '14, 07:09) Volker Barth
Replies hidden

Please go ahead and edit the question, show what 10.0.4181 returns... thanks!

(01 Mar '14, 08:15) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×2

question asked: 28 Feb '14, 06:24

question was seen: 1,672 times

last updated: 01 Mar '14, 08:15