Solved

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

Posted on 2011-02-14
11
345 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Bevos
  • 6
  • 4
11 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 34892283
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34900091
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?

0
 

Author Comment

by:Bevos
ID: 34901344
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
0
 

Author Comment

by:Bevos
ID: 34901356
The data types of all fields there are text, and I will be dealing with maximum 10,000 records per table (imported, imported2)
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 34901808
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
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:Bevos
ID: 34902143
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?
0
 

Author Comment

by:Bevos
ID: 34902157
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



0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 34909249
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
0
 

Author Comment

by:Bevos
ID: 34909434
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
0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 34911058
Okay, my parenthesis placement was off. False was being passed to the CodesDifferent field, so every field was being flagged as different because the field did not equal -1. This SQL calls the function correctly and returns only the mismatched pair of records (Call Number = 11550):
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));

Open in new window


In this version, I've changed the calls to Nz because "" is the default second parameter for strings. It just makes the code a little easier to read.

The original code was calling CodesDifferent (a non-trivial function) more than 10,000,000 times (with 10,000 records in each table). Having the join between tables means CodesDifferent only gets called within rows of matching Call Numbers (<10,000 times). Should be a big boost in performance!

pT72

0
 

Author Comment

by:Bevos
ID: 34911106
This is awesome. Thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how the fundamental information of how to create a table.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now