Thanks JLS , thats exactly what i wanted.

using that, i still have the table scans. if i only use

and (txSubtype > 'BACKORDER'

or txSubtype < 'BACKORDER'

then the scans go away.Strange.

I am going to read up on sargable...

i dont want to eliminate them, i need the ones with the nulls

if i take out

AND coalesce(txSubtype,'') <> 'BACKORDER'

no records are returned. thats why i am using the coalesce. Most of the records i need have a null in that...

i updated the stats, and rebuilt the indexes. Still the same large difference between the estimated and actual, and still have the index scans. Very strange

CREATE TABLE [dbo].[transactions](

[txID] [int] IDENTITY(1,1) NOT NULL,

[storeNumber] [int] NULL,

[txType] [varchar](10) NULL,

[txStartDate] [datetime] NULL,

[txEndDate] [datetime] NULL,

[txReferrer] [varchar](50) NULL,

[txStatus] [varchar](50) NULL,

[cuID] [int] NULL,

[adrIDShip] [int] NULL,

[adrIDBill] [int] NULL,

[returnfor] [int] NULL,

[txParentID] [int] NULL,

[txRef] [int]...

how do i upload the table definition, it says does not allow .sql as attachments.

Is that not good to use nolocks? I use them alot on queries that i do...

oh right sorry about that.

actually that wont work, if shipweight2 and shipweight3 are both null it will update them both. I dont want that

Well i am getting a lot more data then just what i posted like upc,price,image url,category, etc (about 16 fields). The query is bringing back about 4,000 kits (with an...

Wow you are amazing. Thank you. Went from over 20 minutes to 34 Seconds.

oh sorry, i thought you just cleaned up my post so others could use it. I will try it now

Thanks for doing that. I am going to try building a function to see if it is faster that way. Right now this query takes about 20 minutes to run...

Groups Table

gid gleaderid itemid grank

46768 ...

The cross apply is a lot faster thanks. but i am actually using a lot more fields and my group by looks like

GROUP BY

i.id,i.title + ' ' +...