FiveTech Software tech support forums

James
Possibly but i am not that good at sql. Any ideas wher eot look

Thanks
Peter

Peter,

You should use AND and OR in your SQL statement and your SQL server will optimize it to solve it as fast as possible

Peter,

>select * from skippric
where site_id = mSiteID and sp_custno = mcustacc and uniq_nbr = mUniwnbr and sp_contype = mContainer and sp_waste = mWaste and
sp_effdate<=mDate

Isn't the above going to return a recordset? Are all possible matches in this recordset? If not, then can you loosen up the query to include all possible matches?

>but if this fails then it should test for all other combinations until there is nothing left to test.

What do you mean by other combinations? When is a record acceptable?

James

The result should only return one record which should be the best match

Peter,

>sp_effdate<=mDate

But when you are using something like the above, it would seem there could be multiple records with that criteria.

How do you define a best match?

What is an acceptable match, and what isn't? I.E. which criteria are musts, and which are not?

I am having a hard time grasping what you are doing. I have done some complex pricing schemes before but they were all either matching or not matching; I have never done a best match pricing.

James

I have created the following stored procedure which works but is just as slow as 32 seeks

Any ideas

Use iwscompa
go

IF OBJECT_ID ('usp_find_price') IS NOT NULL
DROP PROCEDURE usp_find_price
go

CREATE PROCEDURE usp_find_price
@mSite numeric(2),
@mCustType varchar(10),
@mCustNbr varchar(10),
@mUniqNbr varchar(10),
@mContainer varchar(10),
@mWaste varchar(10),
@mArea varchar(10),
@mDisposer varchar(10),
@mEffective datetime,
@result numeric (10)
AS
/*1*/
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
/*2*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*3*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*4*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*5*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*6*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*7*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*8*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
(uniq_nbr = @mUniqNbr or uniq_nbr is null) and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

IF @result = 0
/*9*/
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*10*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*11*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*12*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*13*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*14*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*15*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*16*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custno = @mCustNbr or sp_custno is null) and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*17*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*18*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*19*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*20*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*21*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*22*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*23*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*24*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
(sp_custype = @mCustType or sp_custype is null) and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*25*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*26*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_region = @mArea or sp_region is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*27*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*28*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_waste = @mWaste or sp_waste is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*29*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_region = @mArea or sp_region is null) and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*30*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0
/*31*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
(sp_dispsit = @mDisposer or sp_dispsit is null) and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
ELSE Return 0

/*32*/
IF @result = 0
SET @result = (SELECT sr_recno from skippric
where site_id = @msite and
sp_contain = @mContainer and
sp_effdate <= @mEffective and
(sp_deldate >= @mEffective or sp_deldate is null))
RETURN @result


Hi all
I have recoded the function using arrays to store the seek definition and then a series of IF/THEN conditions to test each of the seek definitions prior to actually seeking in the table.

This has resulted in an 8 fold increase in speed and is now as fast in sql as it was in dbf (and of course it is now 8 times faster in dbf)

Many thanks for all of your help and pointing me in the direction of arrays

Peter

You're welcome. Glad it works for you.