June 30, 2010 at 12:01 pm
select distinct
equipment.FUNCT_LOC as 'shFunctLoc',
STUFF(orderTrans.EQUIPMENT,1,PATINDEX('%[^0]%',orderTrans.EQUIPMENT)-1,'') as 'shEquipment',
equipment.zserialnm as 'SerialNumber'
into #EquipmentTemp
FROM usdatlpc64msd03.BWInfospoke.dbo.ServiceOrderMaster as orderMaster
inner join usdatlpc64msd03.BWInfospoke.dbo.ServiceOrdersTransactional as orderTrans
on orderTrans.CS_ORDER = orderMaster.CS_ORDER
left join usdatlpc64msd03.BWInfospoke.dbo.EquipmentMaster as equipment
on equipment.EQUIPMENT = orderMaster.EQUIPMENT
and (
equipment.datefrom is null and orderTrans.ZGSTRP < equipment.dateto
or
orderTrans.ZGSTRP between equipment.datefrom and equipment.dateto
)
June 30, 2010 at 12:20 pm
The stuff function can be pretty resource intensive combined with a distinct in the same query this could tak some time if you are dealing with a large number of records or if you are dealing with a large amount of data. for example if you have a couple varchar(max) field that are filled to the brim then you may have a large amount of data. Take a look at the execution plan and see what is happening. If you see a bunch of table scans then you may have some indexing to do.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 1:57 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply