Thanks for that, now I'm clear.

Yeah, I think the first query you posted should do that.

As to performance, make sure the s and p tables have an index on...

Let me try to explain what i need better.  I want all shipments, regardless of whether they have matching wsPKG or wsPKGlin records.  I want to join wsPKG records to...

• This reply was modified 3 years, 4 months ago by  Jackie Lowery.

If I understand your requirements correctly, the code below will do what you want.

As to performance, make sure the s and p tables have an index on Shipment_No and...

Might this work?

select s.Shipment_No, p.pkg_no, l.ord_no  -- You need to get ord_no from either s or p instead
from wsPKGShipment s
left join wsPKG p
ON s.Shipment_No =...

After rewriting the query and adding new indexes:

Total Cpu -  1 min 7 sec -> .488 sec

Total Logical Reads - Over 78M -> 150K.

Very impressive improvements.  Thanks guys !!

The ID is included to make sure the key is unique.  Unique indexes process better in SQL Server.  All key columns from the clustered index are automatically included in...

I suggest creating the following indexes to support the query.  If you'd like to adjust other indexes on the tables also, just let me know.

CREATE UNIQUE...

            
July 21, 2021 at 2:14 pm
            
#3912474

            
            
                                                
        

Results attached.

I can't offer index recommendations without seeing the DDL for the tables, including all current index definitions.  The most critical index is the clustered index, so that should be...

• This reply was modified 3 years, 5 months ago by  Jackie Lowery.

Thanks guys.  The coalesce solution is what I needed.  Phils solution would work, except I want to return an empty string if there's less than 2 characters.

I guess i should say the goal here is to do a :

left(column1,len(column1)-2)

and I'm trying to account for Null or Empty fields so the left function doesn't...

I didn't know of the new persisting sample percent feature.  Thanks for that info.  I found a query that shows the date statistics were updated and days since last update. ...

• This reply was modified 3 years, 9 months ago by  Jackie Lowery.

I may not have had to rebuild them, but doing a full sample on the statistics definitely fixed the slowness.

Should I have to do a full sample on...

I may not have had to rebuild them, but doing a full sample on the statistics definitely fixed the slowness.

Should I have to do a full sample on the statistics...

• This reply was modified 4 years ago by  Jackie Lowery.