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

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

February 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  
GaryG
Arrow Box Company
-- USA

Hello,

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?

Thanks,

Gary

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

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

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

        executeSQL(db, sqlSQL, tc)
        LoadTagUnitID = tc."LoadTagUnitID"
        tc.close()
    endFor
endMethod

 Posted 38 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=29
Location=-- USA 
Joined=30 months ago   MB Posts=17  
Most Recent Post
Moderator
Steven.G
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 38 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=53 months ago   MB Posts=458  
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 = P1277A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #18376 Counter
29
Since 1/20/2021

Follow PrestwoodBoards on: 


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