Link to home
Create AccountLog in
Avatar of Bevos
Bevos

asked on

Access 2007, SQL: Access lags/crashes when generating large report, optimizing SQL code

Hello, I have a database which contains 2 forms for users to enter data.  The entries in these forms are linked on a unique ID and three fields are compared with a SQL query.  The query finds discordant record entries and passes these results to a report.  However, with large datasets (numbering several thousand entries) Access seems more likely to crash than to present me with the output I'm interested in.  The code for the SQL query is as follows:

SELECT Imported.[Call Number], Imported.[Reviewed Item], Imported2.[Reviewed Item], Imported.[Reprint Edition], Imported2.[Reprint Edition], Imported.Notes, Imported2.Notes, Imported.[Modified By], Imported2.[Modified By]
FROM Imported, Imported2
WHERE (((Imported.[Call Number])=[Imported2].[Call Number]) AND ((CodesDifferent(Nz([Imported].[Reprint Edition],""),Nz([Imported2].[Reprint Edition],"")))<>False)) OR (((Imported.[Call Number])=[Imported2].[Call Number]) AND ((CodesDifferent(Nz([Imported].[Reviewed Item],""),Nz([Imported2].[Reviewed Item],"")))<>False)) OR (((Imported.[Call Number])=[Imported2].[Call Number]) AND ((CodesDifferent(Nz([Imported].[Notes],""),Nz([Imported2].[Notes],"")))<>False));

This code works well, but when handling a large amount of different reviewed records it crashes.  Is there a way to maybe segment the results as first 50 records etc so that I could browse these in a report?  Or how does an Access/SQL expert recommend I change things to get this working more smoothly?  Even opening the query will cause a great deal of system lag.  I am new to SQL and Access so if this isn't possible please let me know that as wel :)

Thanks so much for all the help,
Bevo
Avatar of pteranodon72
pteranodon72
Flag of United States of America image

Bevo,

You don't have any joins in the query, which means that SQL must pull all n rows from Imported and all m rows from Imported2, giving n*m records. CodesDifferent gets called three times for every row, even if they don't match CallNumbers.  You definitely want to use an inner join between the tables:

SELECT Imported.[Call Number], Imported.[Reviewed Item], Imported2.[Reviewed Item], Imported.[Reprint Edition], Imported2.[Reprint Edition], Imported.Notes, Imported2.Notes, Imported.[Modified By], Imported2.[Modified By]
FROM Imported INNER JOIN Imported2 ON Imported.[Call Number] = Imported2.[Call Number]
WHERE CodesDifferent(Nz([Imported].[Reprint Edition],""),Nz([Imported2].[Reprint Edition],"")<>False OR CodesDifferent(Nz([Imported].[Reviewed Item],""),Nz([Imported2].[Reviewed Item],"")<>False OR CodesDifferent(Nz([Imported].[Notes],""),Nz([Imported2].[Notes],"")<>False;

That lets you take all the comparisons out of the WHERE clause.

Make sure that you have an index defined on [Call Number] on each table.

How does this change improve the fuctioning of just the query?

pT72
Avatar of Jeffrey Coachman
Obviously if you are dealing with "Millions" of records, this may be an issue.
Can you give us some idea of how many records are in the Raw data (before the query filters)

Remember, you also have a fair amount of nesting and NZ() functions there.

Can you also state the dataypes of these fields?

Avatar of Bevos
Bevos

ASKER

Hi pterandon, I am getting a syntax error when I try this... I made the index variables as you mentioned but it still will not work.

Thanks,
Bevo
Avatar of Bevos

ASKER

The data types of all fields there are text, and I will be dealing with maximum 10,000 records per table (imported, imported2)
Bevo,
Sorry about the syntax error (note to self -- no air code!) Try:

SELECT Imported.[Call Number], Imported2.[Call Number], Imported.[Reviewed Item], Imported2.[Reviewed Item], Imported.[Reprint Edition], Imported2.[Reprint Edition], Imported.Notes, Imported2.Notes, Imported.[Modified By], Imported2.[Modified By]
FROM Imported INNER JOIN Imported2 ON Imported.[Call Number] = Imported2.[Call Number]
WHERE (CodesDifferent(Nz([Imported].[Reprint Edition],""),Nz([Imported2].[Reprint Edition],"")<>False)) OR (CodesDifferent(Nz([Imported].[Reviewed Item],""),Nz([Imported2].[Reviewed Item],"")<>False)) OR (CodesDifferent(Nz([Imported].[Notes],""),Nz([Imported2].[Notes],"")<>False));

pt72
Avatar of Bevos

ASKER

Hi again pt72, thanks for the code.  I no longer receive a syntax error.  However, it is no longer functioning as intended.  It is not displaying discordant records any longer and simply listing all records.  Any thoughts?
Avatar of Bevos

ASKER

Here is the modCodesDifferent:

Option Explicit
Option Compare Database
Option Base 0

Public Function CodesDifferent(pstrCodes1 As String, _
                               pstrCodes2 As String) As Boolean
    Dim astrCodes() As String
    Dim colCodes As Collection
    Dim lngCode As Long
    Dim strCode1 As String
    Dim strCode2 As String

    astrCodes = Split(pstrCodes1, ",")
    Set colCodes = New Collection
    For lngCode = 0 To UBound(astrCodes)
        strCode1 = Trim(astrCodes(lngCode))
        If strCode1 <> "" Then
            colCodes.Add strCode1, strCode1
        End If
    Next

    CodesDifferent = False
    astrCodes = Split(pstrCodes2, ",")
    For lngCode = 0 To UBound(astrCodes)
        strCode2 = Trim(astrCodes(lngCode))
        If strCode2 <> "" Then
            On Error Resume Next  ' Ignore missing item
            strCode1 = ""
            strCode1 = colCodes(strCode2)
            On Error GoTo 0
            If strCode1 = "" Then
                CodesDifferent = True
                Exit For
            Else
                colCodes.Remove strCode2
            End If
        End If
    Next

    If colCodes.Count > 0 Then
        CodesDifferent = True
    End If
    Set colCodes = Nothing

End Function



Hmm. I don't have a good idea for the data thats in your tables. Can you post a stripped-down database with some or all of your records (some discordant, some matching)?

What is in [Reviewed Item], [Reprint Edition], [Notes] that requires using the separate function for comparison, rather than:

WHERE (Nz(Imported.[Reviewed Item]) <> Nz(Imported2.[Reviewed Item])) OR (Nz(Imported.[Reprint Edition]) <> Nz(Imported2.[Reprint Edition])) OR (Nz(Imported.[Notes]) <> Nz(Imported2.[Notes]))
?

pT72
Avatar of Bevos

ASKER

Hello again pT72!  I'm not sure about your question because I'm new to SQL, but I've attached a stripped down version of the database here.  The query in question is qryDifferentReviews.  

Thanks again,
Bevo
Example-Copy.accdb
ASKER CERTIFIED SOLUTION
Avatar of pteranodon72
pteranodon72
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Bevos

ASKER

This is awesome. Thanks!