Results 1 to 4 of 4

Thread: speed up query

  1. #1
    Join Date
    Apr 2004
    Location
    Bucharest, Romania
    Posts
    16

    Question Unanswered: speed up query

    Hi,

    I have an Access 2003 db, with few read only linked tables (ODBC). Based on this tables I have a maketable query.
    My problem is that running this query it takes around 20 minutes.
    There is a way to connect to the tables and run my query faster? Maybe ADO or DAO?

    Thanks,
    Daniel

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    If you are running your query directly in your Access DB (not an application that connects to your Access DB) that is the fastest possible way.

    If you are connecting to your DB from an outside application DAO is faster with Jet Engines (MS Access).

    Post your query to see if there is a way to optimize it.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You could try creating an odbc passthrough query, which will have all its processing done on the server which is bound to be faster than doing it on your desktop. Then building the create table on top of that....

    Regards

  4. #4
    Join Date
    Apr 2004
    Location
    Bucharest, Romania
    Posts
    16
    Quote Originally Posted by namliam
    You could try creating an odbc passthrough query, which will have all its processing done on the server which is bound to be faster than doing it on your desktop. Then building the create table on top of that....

    Regards
    I like your ideea. I've manage to create the passtrugh query, but how I make the table? My code:

    Private Sub Command14_Click()
    Dim dbsCurrent As Database
    Dim qdfPassThrough As QueryDef

    Set dbsCurrent = CurrentDb

    Set qdfPassThrough = _
    dbsCurrent.CreateQueryDef("PasTrugh")
    qdfPassThrough.Connect = _
    "ODBC;DATABASE=DBS;UID=RAP;PWD=;DSN="
    qdfPassThrough.SQL = "SELECT ([PC01001]) AS [Numar comanda], dbo_PL01TE00.PL01001, dbo_PL01TE00.PL01002 AS Furnizor, dbo_PC03TE00.PC03005 AS [cod produs], dbo_SC01TE00.SC01002 AS Produs, dbo_PC01TE00.PC01018 AS [Comanda Factura], dbo_PC01TE00.PC01046 AS Buyer,([PC03010]) AS Cantitate, ([PC03008]) AS Pret, ([PC03010]*[PC03008]) AS Valoare, dbo_PC01TE00.PC01016 AS [Data sosire], dbo_PL01TE00.PL01001, qryIntraECStatistics.SC27005 AS ModTransport, dbo_SYCDTE00.SYCD009 AS Moneda, dbo_PC01TE00.PC01015 AS [Data incarcare], dbo_PC01TE00.PC01023, dbo_PC01TE00.PC01010, dbo_PC17TE00.PC17005, dbo_PC17TE00.PC17006, dbo_PC01TE00.PC01002, qryIntraECStatistics.SC27003 AS cond_livrare" & _
    " FROM (((((dbo_PC01TE00 INNER JOIN dbo_PC03TE00 ON dbo_PC01TE00.PC01001 = dbo_PC03TE00.PC03001) INNER JOIN dbo_SC01TE00 ON dbo_PC03TE00.PC03005 = dbo_SC01TE00.SC01001) INNER JOIN dbo_PL01TE00 ON dbo_PC01TE00.PC01003 = dbo_PL01TE00.PL01001) LEFT JOIN qryIntraECStatistics ON dbo_PC03TE00.PC03001 = qryIntraECStatistics.SC27002) INNER JOIN dbo_SYCDTE00 ON dbo_PC03TE00.PC03056 = dbo_SYCDTE00.SYCD001) INNER JOIN dbo_PC17TE00 ON dbo_PC01TE00.PC01001 = dbo_PC17TE00.PC17001" & _
    " GROUP BY Val([PC01001]), dbo_PL01TE00.PL01001, dbo_PL01TE00.PL01002, dbo_PC03TE00.PC03005, dbo_SC01TE00.SC01002, dbo_PC01TE00.PC01018, dbo_PC01TE00.PC01046, Val([PC03010]), Val([PC03008]), Val([PC03010]*[PC03008]), dbo_PC01TE00.PC01016, dbo_PL01TE00.PL01001, qryIntraECStatistics.SC27005, dbo_SYCDTE00.SYCD009, dbo_PC01TE00.PC01015, dbo_PC01TE00.PC01023, dbo_PC01TE00.PC01010, dbo_PC17TE00.PC17005, dbo_PC17TE00.PC17006, dbo_PC01TE00.PC01002, qryIntraECStatistics.SC27003" & _
    " HAVING (((dbo_PL01TE00.PL01001)<>'555') AND ((qryIntraECStatistics.SC27005)='3' Or (qryIntraECStatistics.SC27005)='1' Or (qryIntraECStatistics.SC27005)='9') AND ((dbo_PC01TE00.PC01010)='0') AND ((dbo_PC17TE00.PC17005)='Container#:') AND ((dbo_PC01TE00.PC01002)=1));"
    qdfPassThrough.ReturnsRecords = True

    dbsCurrent.Close

    End Sub

Posting Permissions

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