Solved

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

Posted on 2011-02-14
11
349 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: "HAVING CASE" Clause 1 23
SQL SELECT query help 7 34
always on switch back after failover 2 32
TSQL query to generate xml 4 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

776 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