Results 1 to 15 of 15
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INNER JOIN very very slow (2000)

    Have you defined indexes on the linking fields in each of the tables?

    Joins can be slow if there are no indexes on the linking fields and if the number of records is large.

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    no indexed for every table:-(
    example?
    I know the way to make index but dont know in wich fileds.... in this case?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INNER JOIN very very slow (2000)

    Edited by HansV to remove password and IP address from code

    i have this sql to open a sql but is very very slow is possible to have a good performance when open rset?

    SSQL = "Select * FROM (SPORTELLI INNER JOIN DATI ON SPORTELLI.SPORT = DATI.PROVA2) " & _
    "INNER JOIN AREA_TERR ON SPORTELLI.REGIONE = AREA_TERR.COD_AREA ORDER BY AREA_TERR.COD_AREA, DATI.PROVA3"


    RSSQL2.CursorLocation = adUseClient
    RSSQL2.Open SSQL, CNSQL1, adOpenForwardOnly, adLockReadOnly

    note: i want only read record

    use this conn for sql Express:

    Public Sub APRI_CONNESSIONI_SQL1()

    On Error GoTo errore
    'CNSQL1.CursorLocation = adUseClient
    'CONNESSIONE A TREVIEW
    'Set CNSQL1 = New ADODB.Connection
    CNSQL1.Open "Provider=SQLOLEDB.1;Password=***;Persist Security Info=True;User ID=sa;Initial Catalog=****;Data Source=***SQLEXPRESS"

    'CNSQL1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:****.mdb;"

    Exit Sub 'esce dall'esecuzione dell'istanza,
    'senno il codice successivo sarebbe
    'stato eseguito
    errore:
    MsgBox "errore numero: " & CStr(Err.Number) 'CStr serve a convertire
    'un valore in stringa
    MsgBox "descrizione: " & Err.Description
    MsgBox "sorgente dell'errore: " & Err.Source

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INNER JOIN very very slow (2000)

    You have

    INNER JOIN DATI ON SPORTELLI.SPORT = DATI.PROVA2

    and

    INNER JOIN AREA_TERR ON SPORTELLI.REGIONE = AREA_TERR.COD_AREA

    so you must create an index in the SPORTELLI table on SPORT and on REGIONE, in the DATI table on PROVA2 and in the AREA_TERR table on COD_AREA.

    Also, you have

    ORDER BY AREA_TERR.COD_AREA, DATI.PROVA3

    So you must create an index in the DATI table on PROVA3.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    ... Dubt.
    but is requyred only to make idex or join filed to filed with relation???

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: INNER JOIN very very slow (2000)

    If you have joined the tables and Enforced Referential Integrity, then Access handles the indexes.

    If you didn't, then you would have to index the the connecting field in the child table. Also, you probably want indexes on the ORDER BY fields.

    But I'm a little confused, is the backend database an SQL database or an Access .mdb database?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    SQL database (sql express)

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    resolved about this tips!
    Tks.

    but is needed to thsi cursor:

    Before to open rset
    RSSQL2.CursorLocation = adUseClient

    Before to open connection
    CNSQL1.CursorLocation = adUseClient

    ????

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    Hans indexed all field you suggest me but have the same prob ... vrey very slow.
    For test i execute the same query in dialogbox Execute Query in Sql Server Mangement Express and the query is a Lightning!!!! 1 or secs...
    Why????

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INNER JOIN very very slow (2000)

    Perhaps you can store it as a view in the SQL Server database and open a recordset on the view?

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: INNER JOIN very very slow (2000)

    Since this is an SQL backend, and I believe you said this query was read-only, then why not use a pass-through query?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    "....then why not use a pass-through query"????
    example of pass-through query, in a few words.
    Tks.

    and, if i call from my code a stored procedure directly on the server instance i have a benefit about time of eleboration?
    ???

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INNER JOIN very very slow (2000)


  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INNER JOIN very very slow (2000)

    I can use the same with my sql express instance?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INNER JOIN very very slow (2000)

    I suppose so. I don't have SQL Server Express myself.

Posting Permissions

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