Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-14
11
Medium Priority
?
363 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

604 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