Solved Using NZ in a query (1 Viewer)

[KitaYama]

I'm working on a SELECT statement that filters a query made of two inner joined tables. The query returns more than a million and half records without any filter.
Both tables are linked tables from sql server.

When I open the query, it's very fast. I can see all the records in a blink of an eye.
When I add filters to this query, it's still very fast. No lag time at all.

My Problem :
The moment I add NZ(UnitPrice,0)>0 as a filter, it takes God knows how long to show the result. I wasn't patient enough to wait and I had to force Access to shut down.
If I change my filter to UnitPrice=0 or UnitPrice>0 the query still is perfect.

UnitPrice contains null values for almost half of the records.

Is there any way to have a high perfomance and still use NZ in a query?

SELECT
    B_ID, OrderPK, OrderNo, Delivery
FROM
    qryOrders AS Q
WHERE
    q.Delivery<#2023/06/01# AND
    q.OrderedFromFK=2 AND
    Nz(q.UnitPrice,0)=0 AND
    q.Deleted=False
ORDER BY
    Delivery ASC, Shipping ASC

Thanks

 

[Josef P.]

Try:

WHERE
    q.Delivery<#2023/06/01# AND
    q.OrderedFromFK=2 AND
    (q.UnitPrice=0 OR q.UnitPrice IS NULL) AND
    q.Deleted=False

This allows the use of an index for UnitPrice. (SQL server can work.)

 

[KitaYama]

I wish I had asked sooner. I spent half a day to find a way. But never this one.
How you guys manage to be so clever?

I really appreciate your help.

 

[ebs17]

This allows the use of an index for UnitPrice

Nz(q.UnitPrice, 0) ... is a calculation on the table field. Index usage is not possible with a calculated field.

Since index use can often enable high performance, you should use unhandled table fields in the query formulation as much as possible. Of course, index use also includes having suitable indices.
A simple rule with big effects.

 

[NauticalGent]

How you guys manage to be so clever?

Been wondering that since I joined in 2014. You would think some of that cleverness would have rubbed off on me by now...

Tomorrow is another day...

 

[KitaYama]

that cleverness would have rubbed off on me by now

I'm much different with most of you. Not the slightest talent for sql.
When I'm working on some sql statements, I always think:

Am I testing this query, or is it testing me?

 

[Galaxiom]

Nz(q.UnitPrice, 0) ... is a calculation on the table field. Index usage is not possible with a calculated field.

It is actually even worse than that. Nz() is a VBA function and not part of the set of capabilities for the database engine. As such the engine has to call to VBA for every record.

In fact call from other programs like Excel to an Access query that uses Nz() doesn't work properly because Nz() is not an engine function. One needs to use Iif() instead.

The database engine does have many of its own functions which perform reasonably well but still should be avoided because they prevent use of any index as pointed out by others

Read about sargable queries for the explanation.

 

[NauticalGent]

Read about sargable queries for the explanation.

Learn something new every day!

 

[Galaxiom]

When I'm working on some sql statements, I always think:

Am I testing this query, or is it testing me?

We are all at that point in at least some parts of our work. I bet you can knock up the bread and butter queries quite well.

The difference is the level where we work. I write monster queries hundreds of line long using blends of T-SQL, ODBC and pass through queries in native Universe SQL. It is like writing a story using multiple different languages simultaneously in the same sentence.

It does my head in some days but is very rewarding when it all comes together.

 

[Galaxiom]

Learn something new every day!

I've been coming here for over thirteen years because I still learn new things.

 

[Minty]

I am currently refactoring Access queries that self-reference a calculated field within the same query, to work in T-SQL as it doesn't allow that. The one I'm now doing has something as innocent as
Calc_TotalPrice: Sum([Calc_TotLabour])+[Calc_LabInflation]+[Calc_LabOHeadMarkup]+[Calc_LabProfitMarkup]+[Calc_LabDiscount]+Sum([Calc_MaterialCost])+[Calc_MatInflation]+[Calc_MatOHeadMarkup]+[Calc_MatProfitMarkup]+[Calc_MatDiscount]
Which actually equates to :

 Sum([Calc_TotLabour])+(((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation])+(((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation]))/100)*[Sec_OHeadMarkup] )+
( ((Sum([Calc_TotLabour])+(((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation])+(((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation]))/100)*[Sec_OHeadMarkup] ))/100)*[Sec_ProfitMarkup])+( IIf(([Sec_BuilderDiscountYN] <> 0),((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation])+(((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation]))/100)*[Sec_OHeadMarkup] )+
( ((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation])+(((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation]))/100)*[Sec_OHeadMarkup] ))/100)*[Sec_ProfitMarkup]))/100)*[Est_BuilderDiscount],0))+Sum([Calc_MaterialCost])+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation]+
(((Sum(Calc_MaterialCost)+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation])/100)*[Sec_OHeadMarkup])+
( IsNull(( ((Sum([Calc_MaterialCost])+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation]+(((Sum(Calc_MaterialCost)+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation])/100)*[Sec_OHeadMarkup]))/100)*[Sec_ProfitMarkup] ),0)+IsNull(( ((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation])+(((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation]))/100)*[Sec_OHeadMarkup] ))/100)*[Sec_ProfitMarkup]),0))+(IIf(([Sec_BuilderDiscountYN] <> 0),((Sum([Calc_MaterialCost])+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation]+(((Sum(Calc_MaterialCost)+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation])/100)*[Sec_OHeadMarkup])+
( IsNull(( ((Sum([Calc_MaterialCost])+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation]+(((Sum(Calc_MaterialCost)+((Sum([Calc_MaterialCost]))/100)*[Sec_MatInflation])/100)*[Sec_OHeadMarkup]))/100)*[Sec_ProfitMarkup] ),0)+IsNull(( ((Sum([Calc_TotLabour])+
( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation])+(((Sum([Calc_TotLabour])+( ((Sum([Calc_TotLabour]))/100)*[Sec_LabInflation]))/100)*[Sec_OHeadMarkup] ))/100)*[Sec_ProfitMarkup]),0)))/100)*[Est_BuilderDiscount],0)) AS Calc_TotalPrice,

I didn't write this initially I'm slowly converting it from an Access BE to an Azure SQL server, and had I realised the depth of self-referencing/nesting in some of the queries I would have had a different plan of attack. In fact, I still might...

I've also just noticed
IIf(IsNull([Sec_SellPrice],0)> 0,[Sec_SellPrice],[Calc_TotalPrice]) AS Calc_MaxPrice,

Which means another outer query or repeating the whole of the above...

 

[Galaxiom]

I've also just noticed
IIf(IsNull([Sec_SellPrice],0)> 0,[Sec_SellPrice],[Calc_TotalPrice]) AS Calc_MaxPrice,

COALESCE() is usually a better function for dealing with Nulls. Access doesn't have an equivalent.

Your SQL needs to be formatted for readability. I have no idea what it does.

Don't be afraid to use a lot of extra lines in a query and use the tab to indent the layers. A single open bracket on a line and a matching close bracket alone in the same column further down often makes a query more readable.

 

[cheekybuddha]

COALESCE() is usually a better function for dealing with Nulls. Access doesn't have an equivalent.

You can always create your own:

Function Coalesce(ParamArray vals()) As Variant

  Const ERR_ARGS As Integer = 450  ' Wrong number of arguments or invalid property assignment
  Dim ret As Variant, v As Variant

  If UBound(vals) < 0 Then
    Err.Raise ERR_ARGS
  End If
  For Each v In vals
    If Not IsNull(v) Then 
      ret = v
      Exit For
    End If
  Next
  Coalesce = ret

End Function

 

[Pat Hartman]

The problem with creating your own is that it doesn't get processed by the server since the server doesn't support VBA. The query without the UDF gets sent to the server and Access processes the UDF when the results are returned from the server.

 

[Minty]

COALESCE() is usually a better function for dealing with Nulls. Access doesn't have an equivalent.

Your SQL needs to be formatted for readability. I have no idea what it does.

Don't be afraid to use a lot of extra lines in a query and use the tab to indent the layers. A single open bracket on a line and a matching close bracket alone in the same column further down often makes a query more readable.

I have reformatted it extensively, it's up to about 200 lines once I have finished with it.

I'm not sure if IsNull is more efficient than Coalesce or not?
Certainly Coalesce is more useful as it can handle a number of checks in one go.