Solved

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

Posted on 2011-02-14
11
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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