That bad ey? :o
I guess SELECT DISTINCT would be better?
> How can I optimize this query, its soooooo slow, not to
> mention seems to kill my SQL server for other processing?
> I have all the proper fields indexed. There is about 700k
> records in this table. The date range is only 1 week.
> Would sub queries help perhaps? I think the NOT IN is part
> of the problem. Is it better to just a have a ton of <>
> instead???
>
> <cfset remove_list =
> "'CUST','DEOH','DMSC','EXPT','FA','FTZA','GNH','HAND','HCIP','
> HDCY','IN','JU
> NK','NXTD','OCCS','OCEC','OCEL','OT','TT','UNK','ZYZZ','ZZCP',
> 'ZZCS','RYDD',
> 'SMSP'">
>
> <CFQUERY NAME="GetCarr" DATASOURCE="blah">
> SELECT Carrier FROM TheTable WHERE
> ShipDate BETWEEN #datefrom# AND #dateto#
> AND CCOde = 'ABC123'
> AND TrackNo IS NOT NULL
> AND TrackNo <> ''
> AND Carrier NOT IN(#preservesinglequotes(remove_list)#)
> AND SourceLoc <> 'OGOG'
> GROUP BY Carrier ORDER BY Carrier
> </CFQUERY>
For starters, you can remove the GROUP BY clause.
Dave Watts, CTO, Fig Leaf Software
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
