String Parsing & a terrible Sproc

[RobS23]

Below is the bare bones of the Sproc I'm working with. Not only is there some nasty string parsing in there but the declared parameters are not helping the speed side of things either.

Can this be split in two? Say a UDF or calling Sproc that will pass the name, strength and pack parameters to the parse and union sproc?

I've got a stinking cold and I can't concentrate so any help would be great guy's!


CREATE PROCEDURE spProcessDrug
@DID varchar(255)
AS

declare @Name varchar(255),
@Strength varchar(50),
@Pack varchar(50)


SELECT @Name = BrandNameParse, @Strength = Strength_Value, @Pack = PckSize_Qty
FROM CT_Parsed
WHERE (DID = @DID)


SELECT DISTINCT RTRIM(DrugDescription) + ' ' + ReorderPackSize AS Detail
FROM ACTDataTable
WHERE (DrugDescription LIKE @Name + '%' AND DrugDescription LIKE '%' + @Strength + N'%' AND ReorderPackSize = @Pack)
UNION
SELECT drug_name + ' ' + drug_strength + ' ' + drug_form + ' ' + drug_packsize AS Detail
FROM TDS
GROUP BY drug_name, drug_strength, drug_form, drug_packsize
HAVING (drug_name LIKE @Name + '%' AND drug_strength LIKE @Strength + '%' AND drug_packsize LIKE @Pack + '%' )
GO

 

[nigelrivett]

Making the having clause a where clause should speed it up.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

Kinda gives me the willies when I think about filling my prescription. Like man, yah cud take, like one of deeze or like one of dose Just kidding Rob.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[RobS23]

The HAVING clause is just an artifact of the cut n' paste. This isn't a finalised version...that's why I'm after any comments - there's going to be double the Union at the end of it too!

 

[zhavic]

Also try to use

GROUP BY CAST( RTRIM(DrugDescription) + ' ' + ReorderPackSize AS char(100)) -- of course, change to fit maximum length

in the first part of your query instead of DISTINCT clause


SELECT CAST( RTRIM(DrugDescription) + ' ' + ReorderPackSize AS char(100)) AS Detail
FROM ACTDataTable
WHERE (DrugDescription LIKE @Name + '%' AND DrugDescription LIKE '%' + @Strength + N'%' AND ReorderPackSize = @Pack)
GROUP BY CAST( RTRIM(DrugDescription) + ' ' + ReorderPackSize AS char(100) )
UNION
SELECT ...


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[RobS23]

Cheers Zhavic,

I hadn't though of using cast - does it add a significant performance boost?

 

[zhavic]

No, CAST makes only the result of
RTRIM(DrugDescription) + ' ' + ReorderPackSize
the same length

In my expreriences, GROUP BY clause is much faster than DISTINCT, so I am not using DISTICT clause

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[RobS23]

This is so painfully slow and I'm running it on a testbed of around 750,000 records as opposed to Production 5 million. That I've completely re written it using inserts and then selecting from the insert tables. If your willing to advise on speed issues I'll happilyu repost the updates Sproc.

Strange about Group By I was under the (false) impression that Distinct was faster - Oh well live and learn etc

Cheers for your comments