HELP! Direct execution of SQL in Powerbuilder vs using stored procedures

I'm debugging a powerbuilder process for my company but I unfortunately
don't have much pb experience...

Here's the facts:

	Powerbuilder 5.0
	Microsoft SQL Server 6.5 on Windows NT 3.51

The program opens a cursor on a dataset and then loops through the rows.
For each row it must interrogate the data and then perform some appropriate
action upon the database. The dataset is approx 500,000 rows and the job is
taking a very long time (60 hours +) to complete. My task is to reduce the
execution time. 

I've noticed that the code is calling a function for each iteration of the
loop which does a SELECT statement. In the VB world, you'd get better
performance by creating a prepared statement which creates a temporary
stored procedure and then you'd requery it with different parameters.
Otherwise, the SELECT statement would be re-compiled each time you ran it.
Does the same situation hold true for Powerbuilder? Should I create a
stored procedure to do the select and then declare a  PROCEDURE variable
for the sp?


2nd question:
	At the bottom of each loop there is a COMMIT statement but I don't see
some sort of begin transaction statement in the loop. Does PB implicitly
create a transaction that is terminated with a COMMIT statement?

Any comments would be greatly appreciated
0
Adam
8/27/1997 3:36:21 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

1 Replies
620 Views

Similar Articles

[PageSpeed] 31

Adam Jawer wrote:

> I'm debugging a powerbuilder process for my company but I
> unfortunately
> don't have much pb experience...
>
> Here's the facts:
>
>         Powerbuilder 5.0
>         Microsoft SQL Server 6.5 on Windows NT 3.51
>
> The program opens a cursor on a dataset and then loops through the
> rows.
> For each row it must interrogate the data and then perform some
> appropriate
> action upon the database. The dataset is approx 500,000 rows and the
> job is
> taking a very long time (60 hours +) to complete. My task is to reduce
> the
> execution time.
>
> I've noticed that the code is calling a function for each iteration of
> the
> loop which does a SELECT statement. In the VB world, you'd get better
> performance by creating a prepared statement which creates a temporary
>
> stored procedure and then you'd requery it with different parameters.
> Otherwise, the SELECT statement would be re-compiled each time you ran
> it.
> Does the same situation hold true for Powerbuilder? Should I create a
> stored procedure to do the select and then declare a  PROCEDURE
> variable
> for the sp?
>
> 2nd question:
>         At the bottom of each loop there is a COMMIT statement but I
> don't see
> some sort of begin transaction statement in the loop. Does PB
> implicitly
> create a transaction that is terminated with a COMMIT statement?
>
> Any comments would be greatly appreciated

Question 1:

Are you connecting to MS SQL Server via ODBC or the native driver?  If
you are using ODBC, then you can take advantage of the SQL Cache
parameter as long as you have DisableBind=0 (which is the default).
SQLCache=n will 'cache' n statements in PowerBuilder, so the SELECT
statement will be only need to be prepared once (assuming you have
SQLCache set to a number that won't cause the SELECT to be 'pushed' out
of the cache).

Question 2:
If you have AutoCommit=0 (which is the default), PowerBuilder
automatically issues a Begin Trans at connection and following each
COMMIT/ROLLBACK issued in PowerScript.  You can toggle AutoCommit=0/1
during your application as you desire.

Jim O'Neil
Powersoft Technical Support
joneil@powersoft.com


0
Jim
8/28/1997 12:48:47 AM
Reply:

Similar Artilces:

help about ---store date in sql database using stored procedure
hi,friends i need your help. i want to store a date into sqlserver database using stored procedure. when i run app. it will give this error....... ---------------------------------- Server Error in '/aspnet/espms' Application. -------------------------------------------------------------------------------- String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Det...

MS SQL Stored Procedure not executing properly from Powerbuilder
Hi I have written a stored procedure in MS-SQL 7.0 which creates a copy of existing database. When I execute the procedure from query analyser, the procedure gets executed succesfully and a new database is created using the parameters supplied to it. I am calling this stored procedure from my PowerBuilder 7.0 application. Here also, procedure gets executed without generating any error. But the database does not get created. It is not visible thru SQL Enterprise Manager. Therefore I can connect to this Database. Interesting thing is that data files get created by procedure. Am...

MS SQL Stored Procedure not executing properly from Powerbuilder
Hi I have written a stored procedure in MS-SQL 7.0 which creates a copy of existing database. When I execute the procedure from query analyser, the procedure gets executed succesfully and a new database is created using the parameters supplied to it. I am calling this stored procedure from my PowerBuilder 7.0 application. Here also, procedure gets executed without generating any error. But the database does not get created. It is not visible thru SQL Enterprise Manager. Therefore I can connect to this Database. Interesting thing is that data files get created by procedure. Am...

help about ---store date in database using stored procedure
hi,friends i m new for stored procedure i need your help. i want to store a date into sqlserver database using stored procedure. when i run app. it will give this error....... ---------------------------------- Server Error in '/aspnet/espms' Application. -------------------------------------------------------------------------------- String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originate...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

Execute Stored Procedure Y asynchronously from Stored Proc X using SQL Server 2000
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.Thanks & Regards,Padam Kumar Tripathipadam_india@yahoo.com,padam_india@hotmail.comhttp://padam.8k.com When you say that you want to return from the SQL server execution.Really all of the Execution has to be completed before that Can ...

Storing images in SQL database vs storing images in seperate folder and using URL image path
Hi allCan you store images in a sql database and if so, does it store the images as an image file or does it store it in a binary format reducing the size of the image. If it can be stored in an sql database as a image or a binary format what are the advantages/disadvantages or is it better to store the url in the database and then bind the image holder to the url link in the database. Hi, These two ways are both ok. My suggestion is to store images into sql database if there are no much more images, otherwise, it's better store them into file system because you can store them no mat...

problem in executing the stored procedure from powerbuilder
i have a stored procedure in sybase as follows. create procedure p_tes_update_open_seats @class_id int, @enroll_cnt int, @ret_value int output as Declare @capacity int, @open_seats int Select @capacity = capacity From classes Where class_id = @class_id Select @open_seats = @capacity - @enroll_cnt Update classes Set open_seats = @open_seats Where class_id = @class_id if @@error !=0 Begin Select @ret_value = -1 End else Begin Select @ret_value = 1 End i wanted to execute the above procedure, so i wrote like the code as follows in powerbuilde...

Executing oracle stored procedures from powerbuilder
Hi all We have a thin-client application written in powerbuilder with all the processing (add,update,delete etc) in Sybase stored procedures. We are currently porting this app to Oracle 8.1.6. We use the following syntax to execute stored procs for sybase Declare instance variables: DECLARE ptKitAdd PROCEDURE FOR @RC = p_KitAdd_p @OrgId = :OrgId, @ProductId = :ProductId, @KitID = :is_kitid, @Note = :is_notename, @Letter = :is_letter, @paper = :is_paper, @inTS = :id_TS ; In script for add button: ...

Execute stored procedure via Powerbuilder
Hi, when I do "Select * From User_procedures;" I get all my stored procedures in a list-view. Then when I want to execute a stored procedure from that list : "EXECUTE SP_POST_ACTION_LIST_MESSAGES;" I get an error : Line 3 - ORA-20003 Does anyone have a clue why I'm getting this and how I can execute that stored procedure ? Hey, Now when I open the database via Powerbuilder, you can have a look at the "Procedures & Functions". I can't find my stored procedure in that list. Executing a stored procedure from that list is no problem, so ...

sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...

Executing oracle stored procedure in powerbuilder
Hi all We have a thin-client application written in powerbuilder with all the processing (add,update,delete etc) in Sybase stored procedures. We are currently porting this app to Oracle 8.1.6. We use the following syntax to execute stored procs for sybase Declare instance variables: DECLARE ptKitAdd PROCEDURE FOR @RC = p_KitAdd_p @OrgId = :OrgId, @ProductId = :ProductId, @KitID = :is_kitid, @Note = :is_notename, @Letter = :is_letter, @paper = :is_paper, @inTS = :id_TS ; In script for add button: ...

Store procedure vs execute SQL
Hello, Is there any difference to between SLQ string in the code and call execute query and call a stored procedure and execute the query that way concerning speed, effectiveness, reliability, …? Thanks, Jim. Yes, there is Stored proc is like a compiled code and thus it's faster! But if its a simple query, then it dosn't show much difference!Sreedharhttp://www.w3coder.orgweblog http://weblogs.asp.net/skoganti Thanks for the reply.That is what I was thinking, however I was told both is the same actually. If myTable is really big, will “Stored Procedure” code have any advantage on “SQL ...

Executing oracle stored procedure from powerbuilder
Hi all We have a thin-client application written in powerbuilder with all the processing (add,update,delete etc) in Sybase stored procedures. We are currently porting this app to Oracle 8.1.6. We use the following syntax to execute stored procs for sybase Declare instance variables: DECLARE ptKitAdd PROCEDURE FOR @RC = p_KitAdd_p @OrgId = :OrgId, @ProductId = :ProductId, @KitID = :is_kitid, @Note = :is_notename, @Letter = :is_letter, @paper = :is_paper, @inTS = :id_TS ; In script for add button: ...

Web resources about - HELP! Direct execution of SQL in Powerbuilder vs using stored procedures - sybase.powerbuilder.database

PowerBuilder - Wikipedia, the free encyclopedia
PowerBuilder is an integrated development environment owned by Sybase , a division of SAP . It has been in use since 1991, peaking around 1998 ...

Sybase PowerBuilder tool readied for Microsoft's .Net
... Win32 or newer style .Net development After several years of work, Sybase is ready to deliver on the final step in its plan to move PowerBuilder ...

Jim O'Neil (@jimoneil) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Open Directory - Computers: Programming: Languages
about dmoz - dmoz blog - report abuse/spam - help the entire directory only in Programming/Languages Description Top : Computers : Programming ...

The HP Booth Staff (Goons) Owe Me an Apology
... when you might need to use it. For instance, when I was in the WinRunner world there were plugins for driving terminal emulators and powerbuilder ...

Contact Us - Sybase Inc
Thanks for visiting the 主页 section of Sybase.com. Here you will find information about Contact Us. For more information about Business Intelligence, ...

热门搜索 - 我的异常网
... 我的异常网 » 热门搜索 sdpnet2 移动【A111】其它用户原因鉴权失败,是什么意思啊 http:// 192.168.1.102:22578 本网站服务器位于美国,受美国法律保护 MiniUI js 破解 http://192.168.1.103:8080/ powerbuilder ...

Mobile app security: Always keep the back door locked
The best way to keep mobile apps safe is to secure the services they connect to.

10 Tech Skills That Will Instantly Net You A $100,000+ Salary
... that let's developers run Java applications. Java is highly popular language for writing web apps and custom enterprise apps. No. 8: PowerBuilder ...

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Resources last updated: 1/19/2016 3:00:25 PM