I.T. Discussion Community!
-Collapse +Expand
Search Paradox Group:

-Collapse +Expand Paradox To/From
-Collapse +Expand Paradox Store

Prestwood eMagazine

April Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardQBE & SQL Topic   Print This     

Performance of SQL Passthru Query in a loop

Performance of SQL Passthru Query in a loop in QBE & SQL topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: Performance   Performance of   Performance of SQL   Pass Query  
Arrow Box Company
-- USA


I have a need to execute a SQL Passthru query(SQL Server) inside a loop. The loop number will be hundreds per day. In the example below, this loop of 100 takes about 6 seconds. Using SQL Profiler, I can see the stored procedure itself executes in 0 milliseconds. It appears to me that all of the processing time is spent executing the objectPal method 'executeSQL'. Example code below. Do you have any ideas/tips on improving this performance?



method pushButton(var eventInfo Event)
    sqlSQL SQL
    db Database
    dynAlias DynArray[] AnyType
    tc TCursor
    cnt SmallInt
    LoadTagUnitID LongInt

    dynAlias["USER NAME"] = "USERNAME"
    dynAlias["PASSWORD"] = "PASSWORD"
    db.open (":ArrowSys:", dynAlias)

    for cnt from 1 to 100
        sqlSQL.readFromString("execute spParaBox_LoadTagUnitInsert 195195, " + String(cnt) + ", 100;")

        executeSQL(db, sqlSQL, tc)
        LoadTagUnitID = tc."LoadTagUnitID"

 Posted 81 days ago (Thread Starter)
Comment Quote
About GaryG -Collapse +Expand
Visit Profile
Membership pending.
Member subscribes to this thread but email is NOT VERIFIED.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #18376, 1 reply
Thread Started 1/20/2021 8:39:21 AM
View Counter=36
Location=-- USA 
Joined=32 months ago   MB Posts=17  
Most Recent Post
Myrtle Beach, SC USA

I don't think there's any way to make a significant change, other than "fastest machine you have".. you can do 1000 loops in a minute.. that seems quite reasonable

Steven Green
Senior Software Engineer
Paradox Support Specialist DOS/WIN

Prestwood IT Solutions
8421 Auburn Blvd, Suite 256, Citrus Heights, CA 95610
Office: 916-726-5675 | Fax: 916-726-5676 | http://www.prestwood.com
 Posted 81 days ago
Comment Quote
About Steven.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #18377 (Level 2) and Parent is 18376
Reply Posted 1/20/2021 9:07:01 AM
Location=Myrtle Beach, SC USA 
Joined=55 months ago   MB Posts=461  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.

Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1111A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #18376 Counter
Since 1/20/2021

Follow PrestwoodBoards on: 

©1995-2021 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech