very slow with join

[sal21]

My open recordset:

Code: Select all

 Set RST0 = New ADODB.Recordset
    With RST0
        .CursorType = adOpenKeyset
        ' .CursorLocation = adUseClient '
        .LockType = adLockOptimistic
    End With

    Set CMD = New ADODB.Command
    With CMD
        .CommandTimeout = 0
        .CommandText = SQL
        .ActiveConnection = CONN
        .CommandType = adCmdText
        .Prepared = True
        DoEvents
        Set RST0 = .Execute
        Set CMD = Nothing
    End With

my conn:

Code: Select all

    Set CONN = New ADODB.Connection
    With CONN
        .CommandTimeout = 1000
        .ConnectionTimeout = 1000
        .CursorLocation = adUseServer
        .Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
              "Data Source=C:\REPORT_L0928\L0928_TEST.mdb;"
    End With

my sql:

If Not Me.SPORT3.Text = "TUTTI" Then
SQL = "SELECT L0928_SI.COD, CODICI.DESCR_COD, COUNT(L0928_SI.COD) AS CONTA FROM L0928_SI INNER JOIN CODICI ON L0928_SI.COD = CODICI.COD WHERE L0928_SI.DATA_ESECUZIONE BETWEEN #" & Format(Me.DATE4.Text, "MM/DD/YYYY") & "# AND #" & Format(Me.DATE5.Text, "MM/DD/YYYY") & "# AND L0928_SI.SPORT='" & Me.SPORT3.Text & "' GROUP BY L0928_SI.COD, CODICI.DESCR_COD ORDER BY L0928_SI.COD, COUNT(L0928_SI.COD)"
Else
SQL = "SELECT L0928_SI.COD, CODICI.DESCR_COD, COUNT(L0928_SI.COD) AS CONTA FROM L0928_SI INNER JOIN CODICI ON L0928_SI.COD = CODICI.COD WHERE L0928_SI.DATA_ESECUZIONE BETWEEN #" & Format(Me.DATE4.Text, "MM/DD/YYYY") & "# AND #" & Format(Me.DATE5.Text, "MM/DD/YYYY") & "# GROUP BY L0928_SI.COD, CODICI.DESCR_COD ORDER BY L0928_SI.COD, COUNT(L0928_SI.COD)"
End If

in this case Me.SPORT3.Text = "TUTTI"

note:
All fields with where clausole are indexed in table are indexed

[HansV]

How many records do L0928_SI and CODICI contain?

[sal21]

How many records do L0928_SI and CODICI contain?

CODICI approx 124
L0928_SI approx 1.700.xxx

[HansV]

With more than half a million records in L0928_SI, it is not surprising that the query is slow. And Access cannot optimize it because it is not a stored query...

[sal21]

With more than half a million records in L0928_SI, it is not surprising that the query is slow. And Access cannot optimize it because it is not a stored query...

ok but based the cursor and property when i open the CONN and RST are correct?

[HansV]

As far as I can tell, yes.

[Rui]

Not sure it will make an impact (depends on the optimizer), but these expressions end up being function calls:

Format(Me.DATE4.Text, "MM/DD/YYYY")

Using functions in where clauses is problematic as it can cause performance issues. It seems to me that these values are know before you write the SQL, so determine the string values, assign them to variables and use the variables to build the SQL statements instead. It may help, but I cannot give assurances that it will.

[HansV]

Sal concatenates the result of the Format function into the SQL string. The SQL string passed to the CMD object contains fixed dates.

[Rui]

You are right, Hans, of course.

[sal21]

Sal concatenates the result of the Format function into the SQL string. The SQL string passed to the CMD object contains fixed dates.

Not completlyy understand.. this point

Have an example to clearm me about my code posted?
Tks.

[sal21]

Not sure it will make an impact (depends on the optimizer), but these expressions end up being function calls:

Format(Me.DATE4.Text, "MM/DD/YYYY")

Using functions in where clauses is problematic as it can cause performance issues. It seems to me that these values are know before you write the SQL, so determine the string values, assign them to variables and use the variables to build the SQL statements instead. It may help, but I cannot give assurances that it will.

if i have understand instead to use function format in query, i ned to use:

Date1=Format(Me.DATE4.Text, "MM/DD/YYYY")

and put in query Date1 instead Format(Me.DATE4.Text, "MM/DD/YYYY")

or not?

[Rui]

Hans is right, I didn't see it correctly. You do not need to change your code.

[sal21]

With more than half a million records in L0928_SI, it is not surprising that the query is slow. And Access cannot optimize it because it is not a stored query...

Hans in my offcie have installed a last version of Mysql wath you think if ai transform Acce Db into MySql server to pseed up and have advanyage about "stored query..." ????

[HansV]

I really don't know, but it won't hurt to try it.