?
Solved

Stored Procedure Slow through Powerbuilder

Posted on 2010-08-30
4
Medium Priority
?
1,051 Views
Last Modified: 2013-12-26
I have a stored procedure that executes within 3 minutes from the SQL Server 2005 console but when I call the same procedure from within my Powerbuilder App it takes 40 minutes.

Are there any things to look for as to why there would be such a huge difference in run times?

Thanks,
0
Comment
Question by:byteboy11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 14

Expert Comment

by:sandeep_patel
ID: 33568482
Is there something you call the procedure in a loop from powerbuilder?
Are you calling procedure from datawindow?
What drivers you are using for database connection?
It would be better if you paste the code here about how you call it from both sql console and powerbuilder for comparison...

Regards,
Sandeep
0
 

Author Comment

by:byteboy11
ID: 33568977
There is no looping in the PB side code.
Not calling from a datawindow just calling directly
Using ODBC drivers


This is the PB Code(1 hour to run):
DECLARE sp_import_my_data PROCEDURE FOR
sp_import_my_data :ai_do_insert,:il_emp_id,:is_data_desc,:li_override
USING SQLCA ;
EXECUTE sp_import_my_data ;


This is how I'm running it from the sql 2005 query analyizer window(3min to run):
exec sp_import_my_data 1,'1','test import1',0
0
 
LVL 14

Accepted Solution

by:
sandeep_patel earned 2000 total points
ID: 33646902
Instead of ODBC, try to use SQL Server Native Drivers. Also in ODBC profile confirm 'Trace ODBC API Calls' is not checked on 'Options' tab or not mentioned in your SQLCA connection profile.

Is this the only procedure takes long time or the same behaviour with any procedure call?

Regards,
Sandeep
0
 

Author Closing Comment

by:byteboy11
ID: 33961479
good answer
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question