Hi,

I have a stored procedure which runs quite slowly when a lot of users are executing it at the same time.

I suspect it may be being caused by read locks.

Is there any way to see what read locks there are when running a stored procedure so that I can see if I can improve it?

Also do the tips in this link (from infoworld.com) apply to Advantage?

10 more do's and don'ts

Regards

Mike

asked 14 Oct '14, 01:21

Mike's gravatar image

Mike
970747794
accept rate: 12%


I don't think this is possible with ADS.

link

answered 14 Oct '14, 01:32

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

OK. That's a shame.

Thanks for letting me know.

(14 Oct '14, 03:06) Mike

Hi Mike;

There is a built in store procedure that returns a cursor with all locks information:

sp_mgGetAllLocksAllTablesAllUsers()

Perhaps that's what you are looking for. Below is the code to create the stored procedure:

   oAdsQuery:cSql += ;
  "CREATE PROCEDURE sp_mgGetAllLocksAllTablesAllUsers ( \n" + ;
  "     TableName CICHAR ( 255 ) OUTPUT, \n" + ;
  "     RecNumber INTEGER OUTPUT,        \n " + ;
  "      UserName CICHAR ( 50 ) OUTPUT,  \n" + ;
  "     IPAddress CICHAR ( 30 ) OUTPUT,  \n" + ;
  "DictionaryUser CICHAR ( 50 ) OUTPUT ) \n" + ;
  "         BEGIN                        \n " + ;
  "       DECLARE cTbls CURSOR AS EXECUTE PROCEDURE sp_mgGetAllTables(); \n" + ;
  "       DECLARE cLocks CURSOR;         \n " +;
  "       DECLARE cUser CURSOR;          \n " + ;
  "                                      \n" + ;
  "          OPEN cTbls;                 \n" + ;
  "                                      \n" + ;
  "WHILE FETCH cTbls DO                  \n" +;
  "   OPEN cLocks AS EXECUTE PROCEDURE sp_mgGetAllLocks(cTbls.TableName); \n"+;
  "   WHILE FETCH cLocks DO              \n"+;
  "      OPEN cUser as EXECUTE PROCEDURE sp_mgGetLockOwner(cTbls.TableName, cLocks.LockedRecNo); \n"+;
  "      WHILE FETCH cUser DO            \n"+;
  "         INSERT INTO __output VALUES (cTbls.TableName, cLocks.LockedRecNo, cUser.UserName, cUser.Address, cUser.DictionaryUser); \n"+;
  "      END WHILE;                      \n"+;
  "      CLOSE cUser;                    \n"+;
  "   END WHILE;                         \n"+;
  "   CLOSE cLocks;                      \n"+;
  "END WHILE;                            \n"+;
  "CLOSE cTbls;                          \n"+;
  "END;                                  \n"
link

answered 14 Oct '14, 06:04

Reinaldo's gravatar image

Reinaldo
816202346
accept rate: 6%

edited 14 Oct '14, 06:18

Does this include read locks i.e. locks created from a cursor within a transaction?

(14 Oct '14, 06:13) Mike

I just realized this is not a built-in sp. I will edit my post to include the source to the store procedure.

(14 Oct '14, 06:17) Reinaldo

And, unless I'm being mislead by the documentation, yes this will return all locks.

(14 Oct '14, 06:21) Reinaldo

only write locks...read locks are internal

(14 Oct '14, 06:39) Joachim Duerr

Hi,

What about the tips in the link. Do they apply to ADS?

(16 Oct '14, 01:05) Mike
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
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

Tags:

×172
×40

Asked: 14 Oct '14, 01:21

Seen: 2,174 times

Last updated: 16 Oct '14, 01:05

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.