Results 1 to 5 of 5

Thread: SQL ...speed up the opening

  1. Jun 26th, 2003, 05:28 AM #1
    Mage
    Mage is offline

    Thread Starter
    Addicted Member Mage's Avatar
    Join Date
    Jan 2003
    Location
    London- Org SA
    Posts
    134

    Question SQL ...speed up the opening

    VB Code:
    1. sSQL = "SELECT DISTINCT "

    2. sSQL = sSQL & "QTempCredit.Credit_Note_Number, "

    3. sSQL = sSQL & "QTempCredit.Employer_Ref, "

    4. sSQL = sSQL & "QTempCredit.Client_Ref, "

    5. sSQL = sSQL & "QTempCredit.Client_Name, "

    6. sSQL = sSQL & "QTempCredit.Personnel_Ref, "

    7. sSQL = sSQL & "QTempCredit.Dept_Description, "

    8. sSQL = sSQL & "QTempCredit.Division_Description, "

    9. sSQL = sSQL & "QTempCredit.Timesheet_Number, "

    10. sSQL = sSQL & "QTempCredit.Credit_Note_Date, "

    11. sSQL = sSQL & "QPSTran.[Acctg Date], "

    12. sSQL = sSQL & "QTempCredit.Details, "

    13. sSQL = sSQL & "QTempCredit.Description, "

    14. sSQL = sSQL & "QTempCredit.Credit_Note_Net, "

    15. sSQL = sSQL & "QTempCredit.Credit_Note_VAT, "

    16. sSQL = sSQL & "QTempCredit.Credit_Note_Total, "

    17. sSQL = sSQL & "QTempCredit.Page, "

    18. sSQL = sSQL & "QTempCredit.Line "

    19. sSQL = sSQL & "FROM QPSTran "

    20. sSQL = sSQL & "INNER JOIN QTempCredit ON (QPSTran.Customer = QTempCredit.Client_Ref) "

    21. sSQL = sSQL & "AND (QPSTran.[Item ID] = QTempCredit.Credit_Note_Number) "

    22. sSQL = sSQL & (QTempCredit.Credit_Note_Date >= '20030501' sSQL = sSQL & AND QTempCredit.Credit_Note_Date <= '20030626') AND (QTempCredit.Client_Ref = 'GAME02')

    23. sSQL = sSQL & "ORDER BY QTempCredit.Credit_Note_Number, QTempCredit.Page, QTempCredit.Line"

    24.  
    25. rstCrNote.Open sSQL, cnSQL, adOpenForwardOnly, adLockReadOnly

    I am using ADO to connect to an Access Table which has a couple of linked tables in it (the reason is there is Tables coming from 2 different SQL Databases) QTempCredit is a Query as is QPSTran.
    the problem is it is taking so long to open the recordset. Am i missing something is there someway to speed this up???

    Thanks
    in advance
    Reply With Quote Reply With Quote
  2. Jun 26th, 2003, 05:34 AM #2
    john_murphy
    john_murphy is offline
    Hyperactive Member
    Join Date
    Nov 1999
    Location
    Galway, Ireland
    Posts
    316
    The only way you could really speed this up is by looking at your index's
    Slan
    Reply With Quote Reply With Quote
  3. Jun 26th, 2003, 05:40 AM #3
    Mage
    Mage is offline

    Thread Starter
    Addicted Member Mage's Avatar
    Join Date
    Jan 2003
    Location
    London- Org SA
    Posts
    134
    Thanks, but i cant really change the indexs as the databases belong to the accounts systems which where bought externally, (the one DB whowever built the database should be drawn and quatered.. ) I thought maybe i had skipped something or was over looking something .... thanks
    Reply With Quote Reply With Quote
  4. Jun 26th, 2003, 08:41 AM #4
    London75
    London75 is offline
    Lively Member
    Join Date
    Apr 2002
    Location
    Manchester,UK
    Posts
    94
    To put it in context, how many records are in each table and how long is it taking?
    Reply With Quote Reply With Quote
  6. Jun 26th, 2003, 10:07 AM #5
    brucevde
    brucevde is offline
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758
    I would try reversing the table links. Its obvious your main table/query is the QTempCredit. All your selection criterions are based on this table.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width