Mysql Stored proc pass all values help

kingjely
kingjely used Ask the Experts™
on
Hi guys.

I have attached a stored procedure.
For store, brand, depart, subd, product, categ fabric, season and purchase parameters, If i want ALL VALUES,
Is there an easier faster way then, using REGEXP and passing it eg '[-0-9]'  in the call for everything.

is there a way to just NOT include the parameter, so everything is passed.

Like if BRAND = '' then pass it everything..

Hope that makes sense..
Kj

DELIMITER $$
DROP PROCEDURE IF EXISTS `BW` $$
CREATE PROCEDURE `BW`(IN STORE VARCHAR(2555),
                      IN BRAND VARCHAR(2555),
                      IN DEPART VARCHAR(2555),
                      IN SUBD VARCHAR(2555),
                      IN PRODUCT VARCHAR(2555),
                      IN CATEG VARCHAR(2555),
                      IN FABRIC VARCHAR(2555),
                      IN SEASON VARCHAR(2000),
                      IN PURCHASE VARCHAR(2000),
                      IN STYLE VARCHAR(255),
                      IN STARTDATE VARCHAR(255),
                      IN ENDDATE VARCHAR(255))

BEGIN

    SET @bws = CONCAT("

Select s.storename,
       s.storenumber,
       s.brandname,
       s.brandnumber,
       s.deptname,
       s.deptcode,
       s.subdname,
       s.subdcode,
       s.prodname,
       s.prodcode,
       s.catename,
       s.catecode,
       s.fabricname,
       s.fabricno,
       s.seasname,
       s.seascode,
       s.purchflagdescription,
       s.purchflagcode,
       s.stylecode,
       s.description,
       s.datefirstrecv,
       s.datelastrecv,
       sum(h.quantity) as SOLD,
       s.datelastsold,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype = 6
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode REGEXP    ",DEPART,"
and s.subdcode REGEXP    ",SUBD,"
and s.prodcode REGEXP    ",PRODUCT,"
and s.catecode REGEXP    ",CATEG,"
and s.fabricno REGEXP    ",FABRIC,"
and s.seascode REGEXP    ",SEASON,"
and s.purchflagcode REGEXP   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) as QTYRECV,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode REGEXP    ",DEPART,"
and s.subdcode REGEXP    ",SUBD,"
and s.prodcode REGEXP    ",PRODUCT,"
and s.catecode REGEXP    ",CATEG,"
and s.fabricno REGEXP    ",FABRIC,"
and s.seascode REGEXP    ",SEASON,"
and s.purchflagcode REGEXP   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) ONHAND




from history h INNER JOIN styleperformance s
ON (h.storenumber=s.storenumber
and h.stockcode=s.stockcode)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode RLIKE    ",DEPART,"
and s.subdcode RLIKE    ",SUBD,"
and s.prodcode RLIKE    ",PRODUCT,"
and s.catecode RLIKE    ",CATEG,"
and s.fabricno RLIKE    ",FABRIC,"
and s.seascode RLIKE    ",SEASON,"
and s.purchflagcode RLIKE   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE,"
group by s.storenumber, s.stockcode
ORDER BY SOLD DESC
LIMIT 20  ");

#SELECT @bws;
PREPARE b_w_s from @bws;
EXECUTE b_w_s;
DEALLOCATE PREPARE b_w_s;

END $$
DELIMITER;

CALL `BW`("1,2,9,10","1,2,3,4,5,6,7,8,9,10,11,12,13,14,15","'[-0-9]'","'[-0-9]'","'[-0-9]'","'[-0-9]'","'[-0-9a-z]'","'[-0-9a-z]'","'[-0-9a-z]'","'%'","'2010-06-01'","'2010-07-03'")

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Sure, for the 3 lines where you have

and s.brandnumber IN   (",BRAND,")

You could change that to

and s.storenumber IN   (",STORE,")
", CASE WHEN BRAND="" then "" else concat(" and s.brandnumber IN   (",BRAND,") "),
"and s.deptcode REGEXP    ",DEPART,"

etc

Author

Commented:
Hi cyber, your super quick !
 um yea, i don't quite get it, I have attached a little bit could you show me what i would need to do.

lets say, all these fields are strings, is it best to use IN, LIKE or RLIKE in my query.

In the call, the user would put in a string of numbers or letters, depending on which parameter,

eg STORE, is storenumber 1 to 40
eg BRAND, Is brandnumber  1 - 499
ect
CATEG , is categorycode 1-999
SEASON, is seacode, which are letters 0-100 or a-z,,  EH H2010  for hisummer 2010

whats the best way to write the query, so i either pass it all, or they can enter what they want

I know your smart, but if i need to explain that further let me know//

Thanks for your help..

from history h INNER JOIN styleperformance s
ON (h.storenumber=s.storenumber
and h.stockcode=s.stockcode)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode RLIKE    ",DEPART,"
and s.subdcode RLIKE    ",SUBD,"
and s.prodcode RLIKE    ",PRODUCT,"
and s.catecode RLIKE    ",CATEG,"
and s.fabricno RLIKE    ",FABRIC,"
and s.seascode RLIKE    ",SEASON,"
and s.purchflagcode RLIKE   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"

Open in new window

Author

Commented:
As you have given it, if i try to do what you said, i get an error at the line i put your answer into.






DELIMITER $$
DROP PROCEDURE IF EXISTS `BW` $$
CREATE PROCEDURE `BW`(IN STORE VARCHAR(2555),
                      IN BRAND VARCHAR(2555),
                      IN DEPART VARCHAR(2555),
                      IN SUBD VARCHAR(2555),
                      IN PRODUCT VARCHAR(2555),
                      IN CATEG VARCHAR(2555),
                      IN FABRIC VARCHAR(2555),
                      IN SEASON VARCHAR(2000),
                      IN PURCHASE VARCHAR(2000),
                      IN STYLE VARCHAR(255),
                      IN STARTDATE VARCHAR(255),
                      IN ENDDATE VARCHAR(255))

BEGIN

    SET @bws = CONCAT("

Select s.storename,
       s.storenumber,
       s.brandname,
       s.brandnumber,
       s.deptname,
       s.deptcode,
       s.subdname,
       s.subdcode,
       s.prodname,
       s.prodcode,
       s.catename,
       s.catecode,
       s.fabricname,
       s.fabricno,
       s.seasname,
       s.seascode,
       s.purchflagdescription,
       s.purchflagcode,
       s.stylecode,
       s.description,
       s.datefirstrecv,
       s.datelastrecv,
       sum(h.quantity) as SOLD,
       s.datelastsold,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype = 6

and s.storenumber IN   (",STORE,")
CASE WHEN BRAND="" then "" else concat(" and s.brandnumber IN   (",BRAND,") ")
and s.deptcode REGEXP    ",DEPART,"
and s.subdcode REGEXP    ",SUBD,"
and s.prodcode REGEXP    ",PRODUCT,"
and s.catecode REGEXP    ",CATEG,"
and s.fabricno REGEXP    ",FABRIC,"
and s.seascode REGEXP    ",SEASON,"
and s.purchflagcode REGEXP   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
##and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) as QTYRECV,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode REGEXP    ",DEPART,"
and s.subdcode REGEXP    ",SUBD,"
and s.prodcode REGEXP    ",PRODUCT,"
and s.catecode REGEXP    ",CATEG,"
and s.fabricno REGEXP    ",FABRIC,"
and s.seascode REGEXP    ",SEASON,"
and s.purchflagcode REGEXP   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
##and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) as ONHAND




from history h INNER JOIN styleperformance s
ON (h.storenumber=s.storenumber
and h.stockcode=s.stockcode)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode RLIKE    ",DEPART,"
and s.subdcode RLIKE    ",SUBD,"
and s.prodcode RLIKE    ",PRODUCT,"
and s.catecode RLIKE    ",CATEG,"
and s.fabricno RLIKE    ",FABRIC,"
and s.seascode RLIKE    ",SEASON,"
and s.purchflagcode RLIKE   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
##and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE,"
group by s.storenumber, s.stockcode
ORDER BY SOLD DESC
LIMIT 20  ");

#SELECT @bws;
PREPARE b_w_s from @bws;
EXECUTE b_w_s;
DEALLOCATE PREPARE b_w_s;

END $$
DELIMITER;

CALL `BW`("1,2,9,10","1,2,3,4,5,6,7,8,9,10,11,12,13,14,15","'[-0-9]'","'[-0-9]'","'[-0-9]'","'[-0-9]'","'[-0-9a-z]'","'[-0-9a-z]'","'[-0-9a-z]'","'%'","'2009-01-25'","'2010-07-03'")
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
this definately errors

and s.storenumber IN   (",STORE,")
", CASE WHEN BRAND="" then "" else concat(" and s.brandnumber IN   (",BRAND,") "),
and s.deptcode REGEXP    ",DEPART,"

so does this

CASE WHEN BRAND="" then "" else concat(" and s.brandnumber IN   (",BRAND,") ")
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
and s.storenumber IN   (",STORE,") ",
     CASE WHEN BRAND="" then "" else concat("and s.brandnumber IN   (",BRAND,")") END,
" and s.deptcode REGEXP    ",DEPART,"

Okay I missed an END.
Basically, what it means is, at the part where you normally add the string

and s.brandnumber IN   (",BRAND,")

To your  full SQL, it checks for whether Brand is "".  If it is blank, it adds nothing to the final query.  Looking at just the CASE line, it does this:

     CASE WHEN BRAND="" then
         ""
     else
         concat("and s.brandnumber IN   (",BRAND,")")
     END,

So it either adds "and s.brandnumber in (<brand>)" if the brand filter is not empty, or nothing at all (and therefore the entire sql does not filter on brand).

Let me know if this is unclear.
ste5anSenior Developer
Commented:
Use a simple OR condition to retrieve all records:

WHERE
(yourOldConstraint)
OR
(BRAND="")

mfG
--> stefan <--

Author

Commented:

which one of these 2 answers is more efficient? seems i have to do it for pretty much every parameter I have ?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Not having it at all is more efficient.  Having an OR condition has at least 3 downsides:

-- parameter sniffing, not sure if this affects mysql, but Sql Server is famous for it
This is when the sql engine plans the query and prepares a generic query to satisfy future executions of the same query.  But because the OR condition changes the data retrieval so dramatically, the generic plan cannot be the most optimal for all cases

-- more to parse. minor (insignificant) performance

-- incorrect plan
This is possible if you make the query too long or complex.  Having too many nodes to work through when compiling a plan is more prone to choosing the wrong plan.
ste5anSenior Developer

Commented:
While to docs do not really shed light upon this

http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html

a constant term should be evaluated only once and in conjunction with a working short-cut evaluation, my solution should work as fast as possible, even for more parameters.


mfG
--> stefan <--
ste5anSenior Developer

Commented:
When parameter sniffing occurs, then the simple solution works the best... but this depends on the actual case.
The fastest solution is to do this distinction of cases in your application, not in the sproc.


mfG
--> stefan <--
CREATE PROCEDURE `BW`(IN STORE VARCHAR(2555),
                      IN BRAND VARCHAR(2555),
                      IN DEPART VARCHAR(2555),
                      IN SUBD VARCHAR(2555),
                      IN PRODUCT VARCHAR(2555),
                      IN CATEG VARCHAR(2555),
                      IN FABRIC VARCHAR(2555),
                      IN SEASON VARCHAR(2000),
                      IN PURCHASE VARCHAR(2000),
                      IN STYLE VARCHAR(255),
                      IN STARTDATE VARCHAR(255),
                      IN ENDDATE VARCHAR(255))

BEGIN
  IF (STORE="") OR (BRAND="") OR .. THEN
    CALL BW_NO_PARAM ;  
  ELSE
    CALL BW_WITH_PARAM(..);
  END IF ;
END;

Open in new window

Author

Commented:
Hi coz cyber answered first i have done this... maybe i should re-write it, using OR and compare?
I dont really uunderstand what you mean stephan, im not that smart to understand, Would i include the SP aswell as your code?

DELIMITER $$
DROP PROCEDURE IF EXISTS `BW` $$
CREATE PROCEDURE `BW`(IN STORE VARCHAR(2555),
                      IN BRAND VARCHAR(2555),
                      IN DEPART VARCHAR(2555),
                      IN SUBD VARCHAR(2555),
                      IN PRODUCT VARCHAR(2555),
                      IN CATEG VARCHAR(2555),
                      IN FABRIC VARCHAR(2555),
                      IN SEASON VARCHAR(2000),
                      IN PURCHASE VARCHAR(2000),
                      IN STYLE VARCHAR(255),
                      IN STARTDATE VARCHAR(255),
                      IN ENDDATE VARCHAR(255))

BEGIN

    SET @bws = CONCAT("

Select s.storename,
       s.storenumber,
       s.brandname,
       s.brandnumber,
       s.deptname,
       s.deptcode,
       s.subdname,
       s.subdcode,
       s.prodname,
       s.prodcode,
       s.catename,
       s.catecode,
       s.fabricname,
       s.fabricno,
       s.seasname,
       s.seascode,
       s.purchflagdescription,
       s.purchflagcode,
       s.stylecode,
       s.description,
       s.datefirstrecv,
       s.datelastrecv,
       sum(h.quantity) as SOLD,
       s.datelastsold,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype = '6'
  ", CASE WHEN STORE    = "" THEN "" else concat("and s.storenumber   IN   (",STORE,")")    END, "
  ", CASE WHEN BRAND    = "" THEN "" else concat("and s.brandnumber   IN   (",BRAND,")")    END, "
  ", CASE WHEN DEPART   = "" THEN "" else concat("and s.deptcode      IN   (",DEPART,")")   END, "
  ", CASE WHEN SUBD     = "" THEN "" else concat("and s.subdcode      IN   (",SUBD,")")     END, "
  ", CASE WHEN PRODUCT  = "" THEN "" else concat("and s.prodcode      IN   (",PRODUCT,")")  END, "
  ", CASE WHEN CATEG    = "" THEN "" else concat("and s.catecode      IN   (",CATEG,")")    END, "
  ", CASE WHEN FABRIC   = "" THEN "" else concat("and s.fabricno      IN   (",FABRIC,")")   END, "
  ", CASE WHEN SEASON   = "" THEN "" else concat("and s.seascode      IN   (",SEASON,")")   END, "
  ", CASE WHEN PURCHASE = "" THEN "" else concat("and s.purchflagcode IN   (",PURCHASE,")") END, "
and s.stylecode LIKE     ",STYLE,"
##and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) as QTYRECV,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype < '4'
  ", CASE WHEN STORE    = "" THEN "" else concat("and s.storenumber   IN   (",STORE,")")    END, "
  ", CASE WHEN BRAND    = "" THEN "" else concat("and s.brandnumber   IN   (",BRAND,")")    END, "
  ", CASE WHEN DEPART   = "" THEN "" else concat("and s.deptcode      IN   (",DEPART,")")   END, "
  ", CASE WHEN SUBD     = "" THEN "" else concat("and s.subdcode      IN   (",SUBD,")")     END, "
  ", CASE WHEN PRODUCT  = "" THEN "" else concat("and s.prodcode      IN   (",PRODUCT,")")  END, "
  ", CASE WHEN CATEG    = "" THEN "" else concat("and s.catecode      IN   (",CATEG,")")    END, "
  ", CASE WHEN FABRIC   = "" THEN "" else concat("and s.fabricno      IN   (",FABRIC,")")   END, "
  ", CASE WHEN SEASON   = "" THEN "" else concat("and s.seascode      IN   (",SEASON,")")   END, "
  ", CASE WHEN PURCHASE = "" THEN "" else concat("and s.purchflagcode IN   (",PURCHASE,")") END, "
and s.stylecode LIKE     ",STYLE,"
##and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) as ONHAND




from history h INNER JOIN styleperformance s
ON (h.storenumber=s.storenumber
and h.stockcode=s.stockcode)
and h.subtranstype < '4'
  ", CASE WHEN STORE    = "" THEN "" else concat("and s.storenumber   IN   (",STORE,")")     END, "
  ", CASE WHEN BRAND    = "" THEN "" else concat("and s.brandnumber   IN   (",BRAND,")")     END, "
  ", CASE WHEN DEPART   = "" THEN "" else concat("and s.deptcode      IN   (",DEPART,")")    END, "
  ", CASE WHEN SUBD     = "" THEN "" else concat("and s.subdcode      IN   (",SUBD,")")      END, "
  ", CASE WHEN PRODUCT  = "" THEN "" else concat("and s.prodcode      IN   (",PRODUCT,")")   END, "
  ", CASE WHEN CATEG    = "" THEN "" else concat("and s.catecode      IN   (",CATEG,")")     END, "
  ", CASE WHEN FABRIC   = "" THEN "" else concat("and s.fabricno      IN   (",FABRIC,")")    END, "
  ", CASE WHEN SEASON   = "" THEN "" else concat("and s.seascode      IN   (",SEASON,")")    END, "
  ", CASE WHEN PURCHASE = "" THEN "" else concat("and s.purchflagcode IN   (",PURCHASE,")")  END, "
and s.stylecode LIKE     ",STYLE,"
##and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE,"
group by s.storenumber, s.stockcode
ORDER BY SOLD DESC
LIMIT 20  ");

#SELECT @bws;
PREPARE b_w_s from @bws;
EXECUTE b_w_s;
DEALLOCATE PREPARE b_w_s;

END $$
DELIMITER;

CALL `BW`("1,2,9,10","1,2,3,4,5","","","","","","","","'%'","'2009-01-25'","'2010-07-03'")

Author

Commented:
HI Stefan/cyber

Is this what the or statement would look like

and s.storenumber IN  (",STORE,") OR STORE = ""

Author

Commented:
If i use above, i get error <=====

(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype = '6'
and s.storenumber IN  (2) OR STORE = "  <=====  error
  and s.brandnumber   IN   (1)
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
If you'll excuse me, I won't even venture there because I don't think it will work..

Author

Commented:
so stefans answer
 ID: 33142120
will not work..

I was just going to see which one was faster mr Kiwi,

Just running the way i have used, (your answer) takes over 20 secs, to come back with limit 10 for just 1 month of data.

Its a topN report,ie BEST/WORST SELLERS report, im doing.
when i get it right i need to use the SP in crystal reports.

The current sql hangs on anything over 2 months of data, and this report needs to be run for up to 5 years to see the best ever seller, if a shop has been open for 5 years.. I cant get 5 weeks with my current query..

Author

Commented:
Also, as you can see from my call, is only running for 1 shop(shop 2), and brand 1 ( Ripcurl.)
could be upt to 44 shops, and 499 brands.

also in th call,
ORDER BY = SOLD ( is ordered by stocksold,)
REPORTTYPE =  DESC, means its 'BESTSELLER'
and TOPN = 10, is TOP 10 BEST SELLER REPORT, ordered by Stock SOLD..

Author

Commented:
sorry this is my call

CALL `BW`("2","1","","","","","","","","'%'","'2010-06-25'","'2010-07-03'","s.storenumber, s.stockcode","SOLD","DESC","10")
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
LIMIT doesn't help here, because

group by s.storenumber, s.stockcode
ORDER BY SOLD DESC

ensures that you need to completely process all the data and grouping to be able to aggregate the
SOLD (sum(h.quantity)) column to return the first X ordered by SOLD.

Author

Commented:
Yea i know what your saying, but i dont have much choice, if its eg: A TOP 10 BEST SELLER REPORT,
It has to process everything and just return top 10, I think thats what your saying.

Do i have any other choices to make it faster?

Author

Commented:
THanks,

Ill ask another question to continue..

Cheers guys

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial