I am facing a weird problem in SQL-Anywhere 10:

CREATE PROCEDURE "dba"."Proc1" ()
RESULT (total int)
BEGIN
  select sum(col1) from Table1;-- where col1 < 10;
END;
CREATE SERVICE Service1
  TYPE 'XML'
  AUTHORIZATION OFF
  USER dba
  AS CALL "dba"."Proc1" ();

The problem is that the Proc1 works always very fast, but the Service1 works only if I restrict the corresponding rows (for example if I open the comment 'where col1 < 10')

The problem looks to be related to the count of the corresponding rows from Table1, and not related to the result of sum(col1).

In other words, even if the value of each Table1.col1 is 0.0001 then I face the same problem if the (select count(col1) from Table1) is too big.

Is there any explanation and/or solution for this problem?

asked 05 Nov '19, 11:19

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

edited 05 Nov '19, 12:54

1

What happens when the service doesn't "work". I.e. do you get an error, or is it slow, or something else?

What do you mean by "too big"? If you are getting an INT overflow error then perhaps change the result type to BIGINT?

(05 Nov '19, 11:53) Mark Culp
Replies hidden

I dont get any error from the service, I simply wait forever for the reply of the service, but I get nothing!

Too big I mean > 1000.

Using BIGINT will not solve the problem because as I told, it regards the count of the rows and not the result of sum function.

As I mentioned before, the procedure alone works very good.

call dba.proc1(); -- even if the count is 5000

(05 Nov '19, 12:09) Baron

The complete code looks like this:

create table table1 ("col1" INT PRIMARY KEY DEFAULT AUTOINCREMENT not null, "col2" varchar(10));

begin

declare cntr int;

set cntr=1;

loop1:

while cntr < 1000 loop

insert into table1 (col2) values ('test');

set cntr = cntr+1;

end loop loop1;

end;

CREATE PROCEDURE "dba"."Proc1" ()

RESULT (total int)

BEGIN

select sum(col1) from Table1;-- where col1 < 10;

END;

CREATE SERVICE Service1

TYPE 'XML'

AUTHORIZATION OFF

USER dba

AS CALL "dba"."Proc1" ();

(06 Nov '19, 03:18) Baron
Replies hidden

here even changing the result (total) from INT to BIGINT doesnt change anything!

So, the proc1 works, and the service1 does not work (I dont receive any message, but it stays silent), and once I open the comment (where col1 < 10) then the service works also fine

(06 Nov '19, 03:20) Baron

Thank you for the example. I have executed it on my SA 17.0.10.5745. I don't see any delay:

Just in case, have you tried the latest version of SA? p.s. this is how I launch the DB server:

dbsrv17 -xs http(port=8080) test

(06 Nov '19, 03:40) Vlad

Thanks for the reply, Yes the DB is started with -xs so that the service works fine (where col1 < 10).

Even when I start Sybase Central with break point in proc1 then it stops working (F11, F5 does nothing)!

Could this problem be only in Sql-Anywhere 10?

I tried even to let proc1 call another procedure (proc2) which in turns does the iterative job (sum function), but the result is the same (as long as the call to proc1 is coming from a webservice).

(06 Nov '19, 04:15) Baron

Once I try to use a sub procedure (like proc2 above), then I get an error message (All threads are blocked)!

(06 Nov '19, 04:46) Baron

Do you happen to call the web service via a SQL Anywhere web client from a database running on the same database server?

(06 Nov '19, 04:49) Volker Barth

No, I am calling the webservice over Internet Explorer (or Curl)

(06 Nov '19, 04:57) Baron

Well, so you have faced a deadlock situation. Do you call the web service procedure while rows in the according table are inserted or modified (which would lead to a classic blocking situation unless you use isolation_level 0 or snapshot isolation)?

Or are you just running out of worker threads, so the multiprogramming level may need to be increased (via dbsrv10 -gn X)?

(06 Nov '19, 05:16) Volker Barth

The Database does nothing for the time when I call the webservice, and there are no any connections to the database!

I am starting the database with dbsrv10 -gt 8

(06 Nov '19, 05:21) Baron

I'm relating to the -gn option, not -gt.

(06 Nov '19, 05:28) Volker Barth
showing 2 of 12 show all flat view

The only solution which I could find is to write the sum(function) inside an event (which in turns calculates the sum and writes the value of sum in a table T2). Now within proc1 I trigger the event and read the value of sum from T2.

With this approach everything works fine (maybe because the sum function is not anymore related to the webservice and has different connection id).

permanent link

answered 06 Nov '19, 05:17

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

converted 06 Nov '19, 05:32

Comment Text Removed

Hm, I certainly can't believe that calculating a sum over 1000 int values should anyhow lock the server, I guess there must be another issue here... - just saying:)

(06 Nov '19, 05:42) Volker Barth
Replies hidden

the records were inserted fast enough on my PC. So I didn't hit the lock situation. I assume maybe the Sako's version of SA is old enough... I don't know. Have you tried reproducing the issue?

(06 Nov '19, 05:59) Vlad

Well, in my understanding the inserts don't happen during the procedure call but before so the time needed to insert should not matter at all for the calculation of the sum IMVHO... - and Sarkis has stated that there are no other connections at that time.

(06 Nov '19, 06:09) Volker Barth

According to my experiment, the problem arises only if the procedure is called over webservice!

(06 Nov '19, 06:41) Baron

Yes, the insert is not matter at all, I just wrote it to complete the code. The problem is in sum function as long as it is called over a webservice.

(06 Nov '19, 06:43) Baron
Your answer
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:

×64

question asked: 05 Nov '19, 11:19

question was seen: 906 times

last updated: 06 Nov '19, 06:43