Slow running query with where clause

[Gazza2]

Hi all,

Hope someone can help with a query that is running slowly if I enter a WHERE clause.

Basically I have a query that pulls data from three tables and has another field which checks a folder on a network drive to see if a document exists. This works fine and opens up instantaneously.

Here is the SQL from that query :

SELECT PO045.BinNo, PO045.PartNo, PO045.Description, PO045.Qty, PO045.Supplier, PO045.PONo, PO045.GRNo, PO045.InvoiceNo, PO045.DateRecieved, PO045.Active, TblClaimGroups.ClaimGroupID, TblClaimGroups.ClaimAmount, TblProducts.Claimable, Format([Qty]*[ClaimAmount],"Currency") AS LineTotal, IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])) AS InvoiceExists
FROM PO045 INNER JOIN (TblClaimGroups INNER JOIN TblProducts ON TblClaimGroups.[ClaimGroupID] = TblProducts.[ClaimGroupID]) ON PO045.BinNo = TblProducts.BinNo
WHERE (((PO045.Supplier)<>"Even" And (PO045.Supplier)<>"trent") AND ((PO045.Active)=True) AND ((TblProducts.Claimable)=True) AND ((IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])))="No")) OR (((IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])))="No Invoice Loaded"))
ORDER BY PO045.BinNo;

I also have another query which is almost exactly the same but has a where clause in the InvoiceExists field to filter for rows that have either "NO" Or "No Invoice Loaded" in the InvoiceExists field. When I add the WHERE clause the query takes 10 - 30 seconds to open. I think this is very slow( I may be wrong) as there is only 100 - 150 rows out of the 3000 in the table that get returned.

Here is the SQL from that query :

SELECT PO045.BinNo, PO045.PartNo, PO045.Description, PO045.Qty, PO045.Supplier, PO045.PONo, PO045.GRNo, PO045.InvoiceNo, PO045.DateRecieved, PO045.Active, TblClaimGroups.ClaimGroupID, TblClaimGroups.ClaimAmount, TblProducts.Claimable, Format([Qty]*[ClaimAmount],"Currency") AS LineTotal, IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])) AS InvoiceExists
FROM PO045 INNER JOIN (TblClaimGroups INNER JOIN TblProducts ON TblClaimGroups.[ClaimGroupID] = TblProducts.[ClaimGroupID]) ON PO045.BinNo = TblProducts.BinNo
WHERE (((PO045.Supplier)<>"Even" And (PO045.Supplier)<>"trent") AND ((PO045.Active)=True) AND ((TblProducts.Claimable)=True) AND ((IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])))="No" Or (IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])))="No Invoice Loaded"))
ORDER BY PO045.BinNo;

I have also tried basing the second Query on the first one with the WHERE clause but there is no difference in speed.

Thanks
Gareth

 

[CJ_London]

I can't see any difference between the two queries, can you highlight where they are different

 

[Gazza2]

Sorry Posted the wrong SQL for the first query.

Here it is :

SELECT PO045.BinNo, PO045.PartNo, PO045.Description, PO045.Qty, PO045.Supplier, PO045.PONo, PO045.GRNo, PO045.InvoiceNo, PO045.DateRecieved, PO045.Active, TblClaimGroups.ClaimGroupID, TblClaimGroups.ClaimAmount, TblProducts.Claimable, Format([Qty]*[ClaimAmount],"Currency") AS LineTotal, IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])) AS InvoiceExists
FROM PO045 INNER JOIN (TblClaimGroups INNER JOIN TblProducts ON TblClaimGroups.[ClaimGroupID] = TblProducts.[ClaimGroupID]) ON PO045.BinNo = TblProducts.BinNo
WHERE (((PO045.Supplier)<>"Even" And (PO045.Supplier)<>"trent") AND ((PO045.Active)=True) AND ((TblProducts.Claimable)=True))
ORDER BY PO045.BinNo;

 

[CJ_London]

SELECT PO045.BinNo, PO045.PartNo, PO045.Description, PO045.Qty, PO045.Supplier, PO045.PONo, PO045.GRNo, PO045.InvoiceNo, PO045.DateRecieved, PO045.Active, TblClaimGroups.ClaimGroupID, TblClaimGroups.ClaimAmount, TblProducts.Claimable, Format([Qty]*[ClaimAmount],"Currency") AS LineTotal, IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])) AS InvoiceExists

FROM PO045 INNER JOIN (TblClaimGroups INNER JOIN TblProducts ON TblClaimGroups.[ClaimGroupID] = TblProducts.[ClaimGroupID]) ON PO045.BinNo = TblProducts.BinNo

WHERE (((PO045.Supplier)<>"Even" And (PO045.Supplier)<>"trent") AND ((PO045.Active)=True) AND ((TblProducts.Claimable)=True))

ORDER BY PO045.BinNo;

SELECT PO045.BinNo, PO045.PartNo, PO045.Description, PO045.Qty, PO045.Supplier, PO045.PONo, PO045.GRNo, PO045.InvoiceNo, PO045.DateRecieved, PO045.Active, TblClaimGroups.ClaimGroupID, TblClaimGroups.ClaimAmount, TblProducts.Claimable, Format([Qty]*[ClaimAmount],"Currency") AS LineTotal, IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])) AS InvoiceExists

FROM PO045 INNER JOIN (TblClaimGroups INNER JOIN TblProducts ON TblClaimGroups.[ClaimGroupID] = TblProducts.[ClaimGroupID]) ON PO045.BinNo = TblProducts.BinNo

WHERE (((PO045.Supplier)<>"Even" And (PO045.Supplier)<>"trent") AND ((PO045.Active)=True) AND ((TblProducts.Claimable)=True) AND ((IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])))="No" Or (IIf(IsNull([InvoiceNo]),"No Invoice Loaded",CheckInvoiceExists([InvoiceNo])))="No Invoice Loaded"))

ORDER BY PO045.BinNo;

I suspect it is your CheckInvoiceExists UDF - why not just include the relevant table in your query with a left join?

Also you logic seems strange

If InvoiceNo is Null, it will assign 'No', it will never get to assigning 'No Invoice Loaded'

 

[Gazza2]

Hi CJ,

I suspect it is your CheckInvoiceExists UDF - why not just include the relevant table in your query with a left join?

This is what is driving me crazy, CheckInvoiceExists is a function that takes the value in the InvoiceNo field and checks to see if a file exists with that name. This works fine when I run the original query to.

Not sure what you mean by include the relevant table in your query.

Basically PO045 is a table that is used to import a csv file from another system(Contains between 3000 - 5000 rows) and then I have TblProducts which is all our products and TblClaimGroups which has data relating to what value we can claim back for a product.

I then Run the first query to pull all the products from PO045 that we can claim for. This gives me between 300 - 600 rows. CheckInvoiceExists checks all of the rows to see if the invoice exists or not and puts either YES, NO, or No Invoice Loaded into the InvoiceExists Column.

I then have the second query that shows all the rows that have either No or No Invoice Loaded in the InvoiceExists column of the original query.

Here is the CheckInvoice function :

Public Function CheckInvoiceExists(InvoiceNo As String) As String

Dim InvoiceNoSlash As String
Dim DirToCheck As String
Dim Extn As String

Extn = ".BMP"
InvoiceNoSlash = InvoiceNo
InvoiceNoSlash = Replace(InvoiceNoSlash, "/", "")

DirToCheck = "\\MyServer\MyFolder\" & InvoiceNoSlash

If InvoiceNo = "" Then
    CheckInvoiceExists = "No Invoice Loaded"
ElseIf Len(Dir(DirToCheck & Extn)) = 0 Then
        If Len(Dir(DirToCheck & "P1" & Extn)) = 0 Then
            CheckInvoiceExists = "NO"
            If Len(Dir(DirToCheck & "P2" & Extn)) = 0 Then
                CheckInvoiceExists = "NO"
            End If
        Else
            CheckInvoiceExists = "YES"
        End If
Else
    CheckInvoiceExists = "YES"
End If


End Function

Sorry For the long post.

Thanks
Gareth

 

[Simon_MT]

Store a flag InvoiceExists you only have to do it once.

Simon

 

[CJ_London]

I agree with Simon, you are running the function multiple times and by the nature of what it does, it will be slow. You should also be aware that it will run regardless of whether or not InvoiceNo is null - iif will evaluate both parts - see this link

http://stackoverflow.com/questions/...e-evaluated-what-situations-should-i-watch-ou

But if you insist on doing it this way, simplify your criteria

WHERE .... AND (CheckInvoiceExists([nz(InvoiceNo],""))<>"Yes")

using the nz function does away with the need for testing for null

and look at simplifying your UDF - you have unnecessary steps. For example

replace

InvoiceNoSlash = InvoiceNo
InvoiceNoSlash = Replace(InvoiceNoSlash, "/", "")
DirToCheck = "\\MyServer\MyFolder\" & InvoiceNoSlash

with

DirToCheck = "\\MyServer\MyFolder\" & Replace(InvoiceNo, "/", "")


remove

Extn = ".BMP"

and use

...& "P2.BMP"...

put this at the top of your code - no point doing the bits mentioned above if InvoiceNo=""

If InvoiceNo = "" Then
CheckInvoiceExists = "No Invoice Loaded"

the dir function is slow and you are using it 3 times. You can perhaps combine 2 easily using wild cards

Len(Dir(DirToCheck & "P?.BMP"))

 

[Gazza2]

Ok thanks very much for the help so far.

I have made the changes that you have suggested CJ_London.

Simon_MT :How and where would I store a flag for InvoiceExists?

I have just created a new query based on the PO045 table containing 1650 records and added an InvoiceExists field as below

InvoiceExists: CheckInvoiceExists(Nz([InvoiceNo],""))

This opens up instantly and displays all records.

If I add <>"YES" into the criteria field it slows to a crawl again.

So it looks like adding a flag would be the best solution but am unsure how to do this.

Sorry to keep asking and thanks for the help

Gareth

 

[Simon_MT]

On Current on the Stock Entry Screen I have this to handle Images

Function Originals_CurrentEntry()
    With CodeContextObject
        If IsNull(.[Orig Artist Ref]) Then
            Call SetPictureClear
        Else
            Call OriginalsFeatures
            Call GetPicture
            Call SetPicture
            Call SetPictureSize
            Call SetPictureFlag
            Call SetPictureWeb
            Call IsDirty
        End If
    End With
End Function

The bit you what is:

Function GetPictureExist()
        If Dir(GetPicturePath) <> Empty Then
            GetPictureExist = -1
        Else
            GetPictureExist = 0
        End If
End Function

And then test to look at the state of the flag and update if necessary:

Function SetPictureFlag()
    With CodeContextObject
        If GetPictureExist = False And .[Image] = -1 Then
            .[Image] = 0
        ElseIf GetPictureExist = True And .[Image] = 0 Then
            .[Image] = -1
        End If
    End With
End Function

Simon

 

[Gazza2]

Hi Simon,

I think I`m being really dumb here but I can`t see how or where I would implement your code so that it will only run once.

When I open up the Query that shows all the records that I need and the code checks each record to see if an image exists it opens up straight away without hesitating and displays all the records.

If I copy the query and add a where clause(Changed as suggested by CJ_London) the query runs and shows the data I need but it just runs slowly.

I have also tried basing the second query on the first query but that is just as slow.

So your suggestion of storing a flag seems to be the way to go but not sure where to store the flag. Would it be on the table?

Thanks
Gareth.

 

[Simon_MT]

Ok. Running a procedure or Function over and over again is inefficient. what you need to establish (on most ocassions) Once if the image exists. Rarely does the image disappear. So it is better to run the ImageExist on a singular Record. I display records on a continuous form and a double click produces a Dialogue box with the image. The reason is that on the Continous Form the image takes up too much space after all it is a list.

There is no ckecking of the Image on the Continuous Only the Entry / Enquiry and Image Dialogue Screen. This screen will display the image irrespective of the Image Exists flag.
Here are some examples:

Function Artists_ImagesOriginalsEntry()

    With CodeContextObject
        If IsNumeric(.[Orig Old Stock]) Then
            DoCmd.OpenForm "Originals Images Review", acNormal, "", "[Orig Old Stock] = " & .[Orig Old Stock], acFormReadOnly, acWindowNormal
        End If
    End With
End Function

Function Exhibitions_ImagesReview()
    With CodeContextObject
        If .[Page No] = 1 Then
            DoCmd.OpenForm " Originals Images Exhibitions", acNormal, "", "[Exhibition]= '" & .[Exhibition] & "' And [Orig Image]=Yes", acReadOnly, acWindowNormal
        ElseIf .[Page No] = 2 Then
            DoCmd.OpenForm "Prints Images Exhibitions", acNormal, "", "[Exhibition]= '" & .[Exhibition] & "' And [Print Image]=Yes", acReadOnly, acWindowNormal
        End If
    End With
End Function

Simon

 

[Gazza2]

Ok, I sort of understand where you are coming from now. If I have understood what you are saying is that the function will only run when the Invoice no is clicked and it will either display the image or tell you that it doesn't exist.

The problem is I need my form to only display records from the table that don`t have an image in the storage location or the InvoiceNo field is blank.

The reason for this is that I need to know what Invoices that I need to scan or which Invoices I need to get from the supplier.

I get that running a function or procedure is inefficient but what really stumped me is that on the table there is between 1000 and 3000 rows. So when I create a query on the whole table the function runs that many times and it opens up immediately.

Thanks very much for all the help so far

Gareth

 

[Simon_MT]

Functions are fine but running consecutively will be slow.

I have a Menu so I can fire off a Docmd.OpenForm with a filter to test whether or not the ImageFlag is set and the Invoice field is blank.

3,000 records isn't that bad but if it is a problem SELECT TOP 100 and do it in batches although I have used SELECT TOP 50000 on a 350,000 record file.

Simon

 

[Gazza2]

Hi Simon,

Thanks to your advise I have now sorted my problem.

What I ended up doing was adding another field to the table and when the data is imported into the table the function is run and fills in the data for all the records.

So thanks gain for all the advise

Gareth