NEED TO GO MUCH FASTER
Uncle Rico
NEED
IT FASTER THANKS.
POSTING HERE B-COZ I WAS TOLD THAT THE REAL SQL GURU'S HANG AROUND IN
THIS GROUP
CREATE PROCEDURE SP_ORDERSUBMISSION @SENDRECORD BIT, @TYPE CHAR(3) AS
SET NOCOUNT ON
DECLARE @STR_USER_ID CHAR(6)
DECLARE @STR_ORDERREGION CHAR(1)
DECLARE @STR_ORDEROFFICE CHAR(4)
DECLARE @STR_MINIMUM_DATE CHAR(10)
DECLARE @STR_MAXIMUM_DATE CHAR(10)
DECLARE @STR_CDSM CHAR(50)
DECLARE @STR_MISTAKE CHAR(100)
DECLARE @INT_DSUM INT
DECLARE @INT_TAXSUM INT
DECLARE @INT_TAXCNT INT
DECLARE @STR_ACTCH CHAR(6)
DECLARE @STR_COSTCNTR CHAR(9)
DECLARE @STR_LOCCODE CHAR(6)
DECLARE @STR_ORDERNUM CHAR(7)
DECLARE @STR_EC CHAR(1)
DECLARE @STR_ORDERNOTMADE CHAR(27)
DECLARE @STR_SECCODE CHAR(29)
DECLARE @STR_TAX CHAR(4)
DECLARE @INT_SEQUENCE INT
DECLARE @STR_ORDERSCH CHAR(4)
DECLARE @DEC_ORDERLEN DECIMAL(6,2)
DECLARE @INT_ORDERID INT
DECLARE @STR_ORDERDATE CHAR(10)
DECLARE @STR_ORDERINFO CHAR(5)
DECLARE @STR_ORDERREMOTE CHAR(4)
DECLARE @STR_ORDERRSN CHAR(6)
DECLARE @INT_COUNT INT
DECLARE @SMALLINT_OVERNIGHT SMALLINT
DECLARE @SMALLINT_FEES SMALLINT
DECLARE @DECIMAL_HOLIDAY DECIMAL(6,2)
DECLARE @STR_ORDERSUBDATE1 CHAR(14)
DECLARE @STR_ORDERSUBDATE2 CHAR(14)
DECLARE @STR_ORDERTYPE CHAR(6)
DECLARE @STR_COSTCOD CHAR(4)
DECLARE @STR_COST CHAR(1)
DECLARE @INT_CNT INT
DECLARE @DECIMAL_MAXORDER DECIMAL(6,2)
DECLARE @STR_NUM CHAR(2)
DECLARE @STR_FILENAME CHAR(21)
DECLARE @SMALLINT_WEEK SMALLINT
DECLARE @SMALLINT_YEAR SMALLINT
DECLARE @STR_METHOD CHAR(1)
DECLARE @INT_EXTRA INT
SELECT @INT_CNT=COUNT(*) FROM db_ORDERS..tbl_LOCKEDORDERS WHERE
LOCKED='ORDER_SUBMISSION'
IF @INT_CNT=0
BEGIN
INSERT INTO db_ORDERS..tbl_LOCKEDORDERS VALUES
('ORDER_SUBMISSION', GETDATE())
END
ELSE
BEGIN
RETURN
END
SELECT @STR_ORDERSUBDATE1=CONVERT(CHAR(4),DATEPART(YY,GETDATE())) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,GETDATE()))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,GETDATE()))),2)
SELECT @STR_ORDERSUBDATE2=CONVERT(CHAR(4),DATEPART(YY,DATEADD(SS,
2,GETDATE()))) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MM,DATEADD(SS,
2,GETDATE())))),
2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(DD,DATEADD(SS,
2,GETDATE())))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(HH,DATEADD(SS,
2,GETDATE())))),
2) + RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(MI,DATEADD(SS,
2,GETDATE())))),2) +
RIGHT('0'+RTRIM(CONVERT(CHAR(2),DATEPART(SS,DATEADD(SS,
2,GETDATE())))),
2)
SELECT @STR_TAX = ' '
SELECT @STR_ORDERNOTMADE = 'Order cannot be sent'
CREATE TABLE #ORDEREXC (INT_ORDERID INT, ORDERDATE char
(10),SYSTEM_COD char (5),CODE_TYPE char (1),DVALUE float,RSN char
(12),STR_UPDATED char (14),SUBMTD char (14),STR_CSTCD char (4) )
CREATE TABLE #ORDERSCH (INT_ORDERID INT, ORDERDATE CHAR(10), SCHLEN
DECIMAL(6,2), NITE SMALLINT, NPST SMALLINT, MHOL DECIMAL(6,2))
CREATE TABLE #ORDERNAHI (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #ORDERNONS (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #ORDERSMAP (SECONDARY CHAR(6), RECTYPE CHAR(6), COST
CHAR(1))
CREATE TABLE #ORDERNONSSCH (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #ORDERNONSSCH2 (INT_ORDERID INT, WK SMALLINT, YR
SMALLINT)
CREATE TABLE #ORDERNONSEXCSCH (INT_ORDERID INT)
CREATE TABLE #ORDERNONSEXC (INT_ORDERID INT, COL CHAR(10))
CREATE TABLE #NONRINT_ORDERID (INT_ORDERID INT)
CREATE TABLE #KEY (REGION CHAR(1), INT_ORDERID INT)
SELECT
@STR_NUM=LEFT('0'+LTRIM(CONVERT(CHAR(2),CONVERT(INT,ISNULL(MAX(SUBSTRING(SE
NTNAME,
15,2)),'00')) + 1)),2) FROM db_ORDERS..tbl_ORDERSTATUS WHERE
SENTNAME!
='' AND SUBSTRING(SENTNAME,15,2)>='01' AND SUBSTRING(SENTNAME,
15,2)<='99'
IF @STR_NUM<'21' OR @STR_NUM>'50' SELECT
@STR_FILENAME='ordr.ifa'+RTRIM(LTRIM(@STR_NUM))+'x.495'
IF @STR_NUM>'20' AND @STR_NUM<'51' SELECT
@STR_FILENAME='mistk.c5l'+RTRIM(LTRIM(@STR_NUM))+'x.495'
UPDATE db_ORDERS..tbl_ORDERSTATUS SET SENTNAME=@STR_FILENAME WHERE
SENTNAME='' AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE OFFICE='ORDR')
IF @@ROWCOUNT=0
BEGIN
RETURN
END
INSERT INTO #KEY SELECT DISTINCT REGION, INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS WHERE SENTNAME=@STR_FILENAME
SELECT @STR_MINIMUM_DATE=MIN(ORDERDATE),
@STR_MAXIMUM_DATE=MAX(ORDERDATE) FROM db_ORDERS..tbl_ORDERSTATUS
WHERE
SENTNAME=@STR_FILENAME
CREATE INDEX D ON #KEY (INT_ORDERID, REGION)
SELECT @INT_SEQUENCE=SEQNUM FROM db_ORDERS..tbl_ORDERCUSTLOCK WHERE
OFFICE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
IF @@ROWCOUNT=0
BEGIN
SET @INT_SEQUENCE=1
END
SET @ERRORMESSAGE='Check status'
SELECT @INT_CNT=COUNT(*) FROM #KEY
IF @INT_CNT=0 RETURN
DECLARE MAXSCH CURSOR FOR SELECT REGION, INT_ORDERID FROM #KEY ORDER
BY INT_ORDERID
OPEN MAXSCH
FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @STR_ORDEROFFICE=OFFICE FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
REGION=@STR_ORDERREGION
SELECT @DECIMAL_MAXORDER = 240
SELECT @DECIMAL_MAXORDER = MAXINCR FROM
db_ORDERS..tbl_ORDERMAXED WHERE CODE=@STR_ORDEROFFICE AND
REGION=@STR_ORDERREGION
IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER =
MAXINCR,
@STR_ORDEROFFICE=@STR_FILENAME FROM db_ORDERS..tbl_ORDERMAXED WHERE
CODE=UPPER(SUBSTRING(@STR_FILENAME,13,4))
IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER =
MAXINCR,
@STR_ORDEROFFICE='ORDR' FROM db_ORDERS..tbl_ORDERMAXED WHERE
CODE='ORDR' AND REGION=@STR_ORDERREGION
IF @DECIMAL_MAXORDER = 240 SELECT @DECIMAL_MAXORDER = 120,
@STR_ORDEROFFICE='MISSING'
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET STOP_TIME=START_TIME
+@DECIMAL_MAXORDER+BREAK2_STOP, STR_UPDATED='2', SUBMTD='0' WHERE
STOP_TIME-START_TIME-BREAK2_STOP>@DECIMAL_MAXORDER AND
INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
ORDERDATE IN (SELECT ORDERDATE FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
REGION=@STR_ORDERREGION AND INT_ORDERID=@INT_ORDERID)
SELECT @INT_CNT=@@ROWCOUNT
IF @INT_CNT>0 PRINT CONVERT(CHAR(12),GETDATE(),114) + ' -
LIMIT ' + RTRIM(CONVERT(CHAR(12),@INT_CNT)) + ' ORDERS TO ' +
RTRIM(CONVERT(CHAR(10),@DECIMAL_MAXORDER/12)) + ' HR FOR ' +
CONVERT(CHAR(10),@INT_ORDERID) + ' IN THE OFFICE ' + @STR_ORDEROFFICE
FETCH NEXT FROM MAXSCH INTO @STR_ORDERREGION, @INT_ORDERID
END
DEALLOCATE MAXSCH
DECLARE EXCRND CURSOR FOR
SELECT REGION, INT_ORDERID, ORDERDATE, SYSTEM_COD AS CDE, DVALUE
FROM db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ROUND(DVALUE,0)!=DVALUE AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE
WHERE RMETHOD!='')
ORDER BY INT_ORDERID, ORDERDATE
OPEN EXCRND
FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
@STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @STR_METHOD=RMETHOD, @INT_EXTRA=REXTRA FROM
db_ORDERS..tbl_ORDERCODE WHERE SYSTEM_COD=@STR_ORDERREMOTE AND
REGION=@STR_ORDERREGION
IF @STR_METHOD='O'
BEGIN
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
DVALUE=ROUND(DVALUE,@INT_EXTRA) WHERE REGION=@STR_ORDERREGION AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
END
IF @STR_METHOD='C'
BEGIN
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
DVALUE=CEILING(DVALUE) WHERE REGION=@STR_ORDERREGION AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
END
IF @STR_METHOD='M'
BEGIN
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
DVALUE=FLOOR(DVALUE) WHERE REGION=@STR_ORDERREGION AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD=@STR_ORDERREMOTE AND DVALUE=@STR_ORDERINFO
END
FETCH NEXT FROM EXCRND INTO @STR_ORDERREGION, @INT_ORDERID,
@STR_ORDERDATE, @STR_ORDERREMOTE, @STR_ORDERINFO
END
DEALLOCATE EXCRND
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='SUM OF ORDERS FOR THIS
DAY GREATER THAN EXCEPTION LENGTH'
WHERE SYSTEM_COD='TTTT' AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE
AND
C.REGION=CTEEXC.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
(SELECT REGION, INT_ORDERID, ORDERDATE, SUM(DVALUE) AS
DAT FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE
E.ORDERDATE=CTEEXC.ORDERDATE AND E.REGION=CTEEXC.REGION AND
(SYSTEM_COD='TTTT' OR SYSTEM_COD IN (SELECT
SYSTEM_COD FROM CTCODE AS C WHERE REGAAB='T' AND C.REGION=E.REGION))
GROUP BY REGION, INT_ORDERID, ORDERDATE
HAVING SUM(DVALUE)>(SELECT (STOP_TIME-START_TIME-
BREAK2_STOP)*5/60.0 FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
S.ORDERDATE=E.ORDERDATE AND S.INT_ORDERID=E.INT_ORDERID AND
S.REGION=E.REGION)
) AS BS
)
INSERT INTO #ORDERNAHI SELECT INT_ORDERID, ORDERDATE FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='BILL' AND
INT_ORDERID
IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION)
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
PATINDEX(UPPER('%ORDERS ARE NOT SENT%'),UPPER(ERR))>0
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=SYSTEM_COD + ' ORDERS
ARE NOT SENT' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=C.REGION) AND
SYSTEM_COD IN (SELECT SYSTEM_COD FROM db_ORDERS..tbl_ORDERCODE
WHERE
RECTYPE='MISSING' AND CTCODE.REGION=CTEEXC.REGION)
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
PATINDEX(UPPER('%WILL NOT BE SENT CAUSE OF DUPLICATE ORDER
%'),UPPER(ERR))>0
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='WILL NOT BE SENT CAUSE
OF
DUPLICATE ORDER' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
CTEEXC.ORDERDATE=CTESCH.ORDERDATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=db_ORDERS..tbl_ORDERSCHEDULE.REGION
AND db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE>=@STR_MINIMUM_DATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE<=@STR_MAXIMUM_DATE AND
db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID>999999 AND
PATINDEX(UPPER('%ORDERS ARE NOT SENT
%'),UPPER(db_ORDERS..tbl_ORDEREXCEPTIONS.ERR))>0)
CREATE TABLE #ORDERSCHHRS (REGION CHAR(1), INT_ORDERID INT, FP BIT,
MX
DECIMAL(5, 2), HRS DECIMAL(5, 2), WK TINYINT, YR SMALLINT)
CREATE TABLE #DATA2 (REGION CHAR(1), INT_ORDERID INT, ISFULLTIME BIT,
MAXHOURS DECIMAL(5,2))
INSERT INTO #DATA2 SELECT DISTINCT REGION, INT_ORDERID, ISFULLTIME,
MAXHOURS FROM db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID IN
(SELECT INT_ORDERID FROM #KEY)
DECLARE SETUPSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)),
DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
INT_ORDERID
OPEN SETUPSCH
FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
INSERT INTO #ORDERSCHHRS
SELECT DISTINCT S.REGION, S.INT_ORDERID, D.ISFULLTIME,
D.MAXHOURS, SUM((S.STOP_TIME-S.START_TIME-
S.BREAK2_STOP)*5.0/60),DATEPART(WW,DATEADD(DD,-
DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE))
FROM db_ORDERS..tbl_ORDERSCHEDULE AS S, #DATA2 AS D
WHERE DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
DATEPART(DW,S.ORDERDATE)+1,S.ORDERDATE))=@SMALLINT_YEAR AND
S.REGION=@STR_ORDERREGION AND D.REGION=@STR_ORDERREGION AND
D.INT_ORDERID=@INT_ORDERID AND S.INT_ORDERID=@INT_ORDERID
GROUP BY S.REGION, S.INT_ORDERID, D.ISFULLTIME,
D.MAXHOURS,DATEPART(WW,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE)), DATEPART(YY,DATEADD(DD,-DATEPART(DW,S.ORDERDATE)
+1,S.ORDERDATE))
FETCH NEXT FROM SETUPSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
END
DEALLOCATE SETUPSCH
CREATE INDEX D2 ON #ORDERSCHHRS (HRS, MX, INT_ORDERID, REGION, WK,
YR)
DECLARE CLRSCH CURSOR FOR SELECT DISTINCT INT_ORDERID, REGION,
DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)),
DATEPART(YY,DATEADD(DD,-DATEPART(DW,ORDERDATE)+1,ORDERDATE)) FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME ORDER BY
INT_ORDERID
OPEN CLRSCH
FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='' WHERE
(PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED%'),UPPER(ERR))>0 OR
PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0) AND
INT_ORDERID=@INT_ORDERID AND REGION=@STR_ORDERREGION AND
DATEPART(WW,DATEADD(DD,-DATEPART(DW,ORDERDATE)
+1,ORDERDATE))=@SMALLINT_WEEK AND DATEPART(YY,DATEADD(DD,-
DATEPART(DW,ORDERDATE)+1,ORDERDATE))=@SMALLINT_YEAR
FETCH NEXT FROM CLRSCH INTO @INT_ORDERID, @STR_ORDERREGION,
@SMALLINT_WEEK, @SMALLINT_YEAR
END
DEALLOCATE CLRSCH
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='13', ERR='MAX ORDERS
EXCEEDED'
WHERE
INT_ORDERID IN (SELECT C.INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS AS S WHERE
HRS>MX AND CTESCH.REGION=S.REGION AND
DATEPART(WW,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.WK AND
DATEPART(YY,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=S.YR)
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='12', ERR='NOT ENOUGH ORDERS
SCHEDULED' WHERE
INT_ORDERID IN (SELECT C.INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERSCHHRS WHERE FP=1 AND
HRS<MX AND DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
+1,CTESCH.ORDERDATE))=WK AND DATEPART(YY,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)+1,CTESCH.ORDERDATE))=YR) AND
INT_ORDERID NOT IN
(SELECT INT_ORDERID FROM #ORDERNAHI AS E WHERE
DATEPART(WW,DATEADD(DD,-DATEPART(DW,CTESCH.ORDERDATE)
+1,CTESCH.ORDERDATE))=DATEPART(WW,DATEADD(DD,-DATEPART(DW,E.COL)
+1,E.COL)) AND
DATEPART(YY,DATEADD(DD,-
DATEPART(DW,CTESCH.ORDERDATE)
+1,CTESCH.ORDERDATE))=DATEPART(YY,DATEADD(DD,-DATEPART(DW,E.COL)
+1,E.COL)))
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='MULTIPLE ORDERS' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS SS WHERE SENTNAME=@STR_FILENAME AND SS.ORDERDATE=CTESCH.ORDERDATE
AND SS.REGION=CTESCH.REGION) AND
CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN
( SELECT CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM
db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS SS WHERE SENTNAME=@STR_FILENAME AND
SS.ORDERDATE=S.ORDERDATE AND SS.REGION=S.REGION)
GROUP BY CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION HAVING
COUNT(*)>1)
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='INVALID ORDER SCHEDULE'
WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND
db_ORDERS..tbl_ORDERSCHEDULE.ORDERDATE=C.ORDERDATE AND
db_ORDERS..tbl_ORDERSCHEDULE.REGION=C.REGION) AND
(STOP_TIME-START_TIME-BREAK2_STOP)/12.0 NOT IN (SELECT RTHRS FROM
db_ORDERS..tbl_ORDERRECORD)
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='' WHERE ERR='MISSING
ORDER' AND
CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION IN (SELECT
CONVERT(CHAR(10),INT_ORDERID)+ORDERDATE+REGION FROM #KEY)
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='MISSING ORDER'
WHERE (SELECT ISNULL(SUM(DVALUE),0) FROM
db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND
SYSTEM_COD='DDDL')>0 AND
(SELECT ISNULL(SUM(DVALUE),0) FROM
db_ORDERS..tbl_ORDEREXCEPTIONS AS E2 WHERE
db_ORDERS..tbl_ORDEREXCEPTIONS.REGION=E2.REGION AND
db_ORDERS..tbl_ORDEREXCEPTIONS.INT_ORDERID=E2.INT_ORDERID AND
db_ORDERS..tbl_ORDEREXCEPTIONS.ORDERDATE=E2.ORDERDATE AND SYSTEM_COD
IN ('MISA','KIPA'))=0
AND SYSTEM_COD='DDDL' AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999 AND INT_ORDERID
IN
(SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
SENTNAME=@STR_FILENAME AND S.ORDERDATE=CTEEXC.ORDERDATE AND
S.REGION=CTEEXC.REGION)
SELECT ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
AS E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS DSUM,
ISNULL((SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS
AS E1 WHERE SYSTEM_COD IN ('MISA','KIPA') AND
E1.ORDERDATE=S.ORDERDATE
AND E1.INT_ORDERID=S.INT_ORDERID AND E1.REGION=S.REGION),0) AS EHSUM,
ORDERDATE, INT_ORDERID
INTO #EHPROB
FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
SENTNAME=@STR_FILENAME
DELETE FROM #EHPROB WHERE DSUM<=EHSUM
INSERT INTO #EHPROB
SELECT 0, 0, ORDERDATE, INT_ORDERID
FROM db_ORDERS..tbl_ORDERSTATUS AS S WHERE
(SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS
E1 WHERE SYSTEM_COD IN ('DDDL') AND E1.ORDERDATE=S.ORDERDATE AND
E1.INT_ORDERID=S.INT_ORDERID)> 0 AND
(SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E1
WHERE SYSTEM_COD IN ('MISA','KIPA') AND E1.ORDERDATE=S.ORDERDATE AND
E1.INT_ORDERID=S.INT_ORDERID)=0
AND SENTNAME=@STR_FILENAME
INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #EHPROB
INSERT INTO #ORDERSMAP SELECT DISTINCT SECONDARY, RECTYPE, COST FROM
db_ORDERS..tbl_ORDERMAP WHERE REGION=@STR_ORDERREGION
CREATE INDEX RecLoop ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
INSERT INTO #ORDERNONSSCH SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
db_ORDERS..tbl_ORDERSCHEDULE WHERE
( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%SUM OF ORDERS FOR THIS DAY GREATER THAN
SCHEDULE ALLOWS%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))>0 ) AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID
FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)
INSERT INTO #ORDERNONSSCH2 SELECT DISTINCT INT_ORDERID,
DATEPART(WW,ORDERDATE), DATEPART(YY,ORDERDATE) FROM
db_ORDERS..tbl_ORDERSCHEDULE WHERE
( PATINDEX(UPPER('%NOT ENOUGH ORDERS SCHEDULED
%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%INVALID ORDER SCHEDULE%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%MISSING ORDER%'),UPPER(ERR))>0 ) AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID
FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION)
INSERT INTO #ORDERNONSEXC SELECT DISTINCT INT_ORDERID, ORDERDATE FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE
( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%'+@ERRORMESSAGE+'%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%MISSING ORDER%' ),UPPER(ERR))>0
OR PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))>0 AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE) AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID
FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
INSERT INTO #ORDERNONS SELECT INT_ORDERID,ORDERDATE FROM
db_ORDERS..tbl_ORDEREXCEPTIONS
WHERE SYSTEM_COD IN (SELECT DISTINCT SECONDARY FROM
db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
REGION=@STR_ORDERREGION ) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND
INT_ORDERID>999999 AND INT_ORDERID IN (SELECT INT_ORDERID
FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION)
INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM #KEY
WHERE
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSCHEDULE WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND INT_ORDERID>999999) AND
INT_ORDERID>999999
INSERT INTO #ORDERNONSEXCSCH SELECT DISTINCT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS WHERE SYSTEM_COD='UMSL' AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE
AND
C.REGION=CTEEXC.REGION)
CREATE CLUSTERED INDEX K1 ON #KEY (INT_ORDERID)
CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH (INT_ORDERID,COL)
CREATE CLUSTERED INDEX S1 ON #ORDERNONSSCH2 (INT_ORDERID)
CREATE CLUSTERED INDEX S1 ON #ORDERNONSEXCSCH (INT_ORDERID)
CREATE CLUSTERED INDEX E1 ON #ORDERNONSEXC (INT_ORDERID,COL)
CREATE INDEX iNOSUB ON #ORDERNONS (INT_ORDERID,COL)
CREATE INDEX iSECONDARYMAP ON #ORDERSMAP (SECONDARY, RECTYPE, COST)
CREATE INDEX iNONR ON #NONRINT_ORDERID (INT_ORDERID)
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='', STR_UPDATED='15',
SUBMTD='0' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE
AND
C.REGION=CTESCH.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDEREXCEPTIONS AS E WHERE
E.ORDERDATE=CTESCH.ORDERDATE
AND E.REGION=CTESCH.REGION AND STR_UPDATED>SUBMTD AND
LEN(RTRIM(LTRIM(SYSTEM_COD)))>0) AND
INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
#ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTESCH.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
INT_ORDERID NOT IN (SELECT #ORDERNONSSCH.INT_ORDERID FROM
#ORDERNONSSCH WHERE #ORDERNONSSCH.COL=CTESCH.ORDERDATE)
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD='17' WHERE INT_ORDERID>999999
AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE
AND
C.REGION=CTEEXC.REGION) AND
( PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))>0
OR PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))>0 )
AND ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
STR_UPDATED=@STR_ORDERSUBDATE1, SUBMTD=@STR_ORDERSUBDATE2 WHERE
REGION=@STR_ORDERREGION AND INT_ORDERID>999999 AND INT_ORDERID IN
(SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND
PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))>0
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='', STR_UPDATED='15',
SUBMTD='0' WHERE
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE
AND
C.REGION=CTEEXC.REGION) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD AND
INT_ORDERID>999999 AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND S.ORDERDATE=CTEEXC.ORDERDATE AND
S.REGION=CTEEXC.REGION AND INT_ORDERID IN (SELECT INT_ORDERID FROM
#KEY)) AND
INT_ORDERID NOT IN (SELECT #ORDERNONSEXC.INT_ORDERID FROM
#ORDERNONSEXC WHERE #ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
#ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND
LEN(RTRIM(LTRIM(SYSTEM_COD)))>0 AND LTRIM(RTRIM(SUBMTD)) NOT IN
('16','17')
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18',
STR_UPDATED='0', ERR=LTRIM(RTRIM(SYSTEM_COD)) + ' ' +
@STR_ORDERNOTMADE WHERE (SYSTEM_COD IN (SELECT DISTINCT SECONDARY
FROM
db_ORDERS..tbl_ORDERMAP WHERE RECTYPE='MISSING' AND
REGION=CTEEXC.REGION ) OR SYSTEM_COD='BILL') AND
REGION=@STR_ORDERREGION AND SUBMTD<STR_UPDATED AND INT_ORDERID>999999
AND INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS
AS C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE
AND
C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET SUBMTD='18',
STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
#ORDERNONS.COL=CTEEXC.ORDERDATE) AND INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET SUBMTD='18', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNONS WHERE
#ORDERNONS.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS
C WHERE SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTESCH.ORDERDATE AND
C.REGION=CTESCH.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
IF @@ROWCOUNT>0
BEGIN
CREATE INDEX iMHIR ON #ORDERNAHI (INT_ORDERID, COL)
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR='UMSL EXISTS FOR
ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
#ORDERNAHI.COL=CTEEXC.ORDERDATE) AND SYSTEM_COD!='BILL' AND
INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTEEXC.ORDERDATE AND C.REGION=CTEEXC.REGION) AND
ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET ERR='UMSL EXISTS FOR
ORDER, ORDER NOT SENT TO BACKUP', SUBMTD='19', STR_UPDATED='0'
WHERE INT_ORDERID IN (SELECT INT_ORDERID FROM #ORDERNAHI WHERE
#ORDERNAHI.COL=CTESCH.ORDERDATE) AND INT_ORDERID>999999 AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=CTESCH.ORDERDATE AND C.REGION=CTESCH.REGION) AND
ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE
END
INSERT INTO #ORDEREXC SELECT INT_ORDERID, LTRIM(RTRIM(ORDERDATE)) AS
ORDERDATE, SYSTEM_COD, CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD,
STR_CSTCD FROM db_ORDERS..tbl_ORDEREXCEPTIONS
WHERE INT_ORDERID>999999 AND STR_UPDATED>SUBMTD AND
PATINDEX(UPPER('%Invalid Elink Code%'),UPPER(ERR))=0 AND
PATINDEX(UPPER('%Unmapped RT Code%'),UPPER(ERR))=0 AND SYSTEM_COD!=''
AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
#ORDERNONSEXC.COL=CTEEXC.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS NS
WHERE NS.WK=DATEPART(WW,CTEEXC.ORDERDATE) AND
NS.YR=DATEPART(WW,CTEEXC.ORDERDATE)) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
#ORDERNONSSCH.COL=CTEEXC.ORDERDATE) AND INT_ORDERID IN (SELECT
INT_ORDERID FROM db_ORDERS..tbl_ORDERSTATUS AS C WHERE
SENTNAME=@STR_FILENAME AND C.ORDERDATE=CTEEXC.ORDERDATE AND
C.REGION=CTEEXC.REGION) AND ORDERDATE>=@STR_MINIMUM_DATE AND
ORDERDATE<=@STR_MAXIMUM_DATE AND
PATINDEX(UPPER('%ORDER CANNOT BE SUBMITTED DUE TO MULTIPLE
ORDERS%'),UPPER(ERR))=0
AND PATINDEX(UPPER('%ORDER CANNOT BE SENT TO BACKUP
%'),UPPER(ERR))=0
AND PATINDEX(UPPER('%INVALID SYSTEM CODE%'),UPPER(ERR))=0
AND PATINDEX(UPPER('%SYSTEM CODE LINK INVALID%'),UPPER(ERR))=0
ORDER BY ORDERDATE
INSERT INTO #ORDERSCH SELECT DISTINCT INT_ORDERID,
LTRIM(RTRIM(ORDERDATE)) AS ORDERDATE, (STOP_TIME-START_TIME-
BREAK2_STOP)*5.0/60 AS SCHLEN,
(SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
E.REGION=S.REGION AND SYSTEM_COD='NDAY') AS NITE,
(SELECT COUNT(*) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
E.REGION=S.REGION AND SYSTEM_COD='SPTN') AS NSPT,
(SELECT SUM(DVALUE) FROM db_ORDERS..tbl_ORDEREXCEPTIONS AS E
WHERE E.INT_ORDERID=S.INT_ORDERID AND E.ORDERDATE=S.ORDERDATE AND
E.REGION=S.REGION AND SYSTEM_COD='CRSM') AS MHOL
FROM db_ORDERS..tbl_ORDERSCHEDULE AS S WHERE STR_UPDATED>SUBMTD
AND INT_ORDERID>999999 AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXC WHERE
#ORDERNONSEXC.COL=S.ORDERDATE) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH2 AS
NS
WHERE NS.WK=DATEPART(WW,S.ORDERDATE) AND
NS.YR=DATEPART(YY,S.ORDERDATE)) AND
INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSSCH WHERE
#ORDERNONSSCH.COL=S.ORDERDATE) AND
INT_ORDERID IN (SELECT INT_ORDERID FROM
db_ORDERS..tbl_ORDERSTATUS AS C WHERE SENTNAME=@STR_FILENAME AND
C.ORDERDATE=S.ORDERDATE AND C.REGION=S.REGION) AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE AND
PATINDEX(UPPER('%MINIMUM ORDERS NOT MET%'),UPPER(ERR))=0 AND
PATINDEX(UPPER('%MAX ORDERS EXCEEDED%'),UPPER(ERR))=0
ORDER BY INT_ORDERID, ORDERDATE
CREATE INDEX iEXC1a ON #ORDEREXC (INT_ORDERID, ORDERDATE, SYSTEM_COD,
CODE_TYPE, DVALUE, RSN, STR_UPDATED, SUBMTD, STR_CSTCD)
CREATE INDEX iEXC1b ON #ORDEREXC (SYSTEM_COD, INT_ORDERID, ORDERDATE,
DVALUE)
CREATE INDEX iSCHa ON #ORDERSCH (INT_ORDERID, ORDERDATE, SCHLEN)
DECLARE EMPS CURSOR FOR SELECT DISTINCT (INT_ORDERID/2)+1134 FROM
#KEY
WHERE INT_ORDERID NOT IN (SELECT INT_ORDERID FROM #ORDERNONSEXCSCH)
ORDER BY INT_ORDERID
OPEN EMPS
FETCH NEXT FROM EMPS INTO @INT_ORDERID
TRUNCATE TABLE db_ORDERS..tbl_ORDERDUMPRAW
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'HEDR0000' +
RIGHT('0000'+RTRIM(CONVERT(CHAR(4),@INT_SEQUENCE)),4) +
CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(6),GETDATE(),
108),':','')
SELECT @INT_COUNT=1
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @STR_SECCODE = 'x '+CONVERT(CHAR(9),@INT_ORDERID)+' jk
9.216.12'
SELECT @STR_USER_ID='INVALID'
SELECT @STR_USER_ID=STR_USER_ID FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
LTRIM(RTRIM(STR_USER_ID))!=' ' AND STR_USER_ID IS NOT NULL
IF @STR_USER_ID='INVALID'
BEGIN
END
ELSE
BEGIN
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
'JKUL0001 ' + RIGHT('00000000' +
LTRIM(RTRIM(CONVERT(CHAR(9),@INT_ORDERID))),9) + ' ' +
SPACE(80) + 'CAP' + @STR_SECCODE
SELECT @STR_ORDEROFFICE=MAX(OFFICE) FROM
db_ORDERS..tbl_ORDERCUSTOMERS WHERE INT_ORDERID=@INT_ORDERID AND
REGION=@STR_ORDERREGION
SELECT @INT_COUNT=@INT_COUNT+1
DECLARE SCHS CURSOR FOR SELECT ORDERDATE, SCHLEN, NITE,
NPST, MHOL FROM #ORDERSCH
WHERE
INT_ORDERID=@INT_ORDERID
AND ORDERDATE NOT IN (SELECT ORDERDATE FROM #ORDEREXC WHERE
INT_ORDERID=@INT_ORDERID AND SYSTEM_COD='BILL' AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE) AND
ORDERDATE>=@STR_MINIMUM_DATE AND ORDERDATE<=@STR_MAXIMUM_DATE ORDER
BY
ORDERDATE
OPEN SCHS
FETCH NEXT FROM SCHS INTO @STR_ORDERDATE, @DEC_ORDERLEN,
@SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
SELECT @INT_CNT=COUNT(*) FROM #NONRINT_ORDERID
WHERE INT_ORDERID=@INT_ORDERID
IF @INT_CNT>0 OR @SENDRECORD=1
BEGIN
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW
SELECT 'RKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @SMALLINT_OVERNIGHT>0 SELECT
@SMALLINT_OVERNIGHT=1
IF @SMALLINT_FEES>0 SELECT @SMALLINT_FEES=1
IF @DECIMAL_HOLIDAY>0 SELECT
@STR_ORDERSCH=SECONDARYHRS FROM db_ORDERS..tbl_ORDERRECORD WHERE
RTHRS=@DECIMAL_HOLIDAY AND NDIFF=@SMALLINT_OVERNIGHT AND
CARFARE=@SMALLINT_FEES
ELSE SELECT @STR_ORDERSCH=SECONDARYHRS FROM
db_ORDERS..tbl_ORDERRECORD WHERE RTHRS=@DEC_ORDERLEN AND
NDIFF=@SMALLINT_OVERNIGHT AND CARFARE=@SMALLINT_FEES
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT
'ZKUL0001' + REPLACE(@STR_ORDERDATE,'-','') + '01' + @STR_ORDERSCH +
' ' + @STR_TAX + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
DECLARE EXCS CURSOR FOR
SELECT E.ORDERDATE, M.SECONDARY,
LTRIM(RTRIM(M.RECTYPE)),
CONVERT(CHAR(6),CONVERT(DECIMAL(6,2),E.DVALUE)), LEFT(E.RSN,6),
E.STR_CSTCD, M.COST
FROM #ORDEREXC AS E, #ORDERSMAP AS M
WHERE E.INT_ORDERID=@INT_ORDERID AND
E.SYSTEM_COD=M.SECONDARY AND E.ORDERDATE=@STR_ORDERDATE AND
E.SYSTEM_COD NOT LIKE '%?%'
ORDER BY E.ORDERDATE, M.RECTYPE, M.COST
OPEN EXCS
FETCH NEXT FROM EXCS INTO @STR_ORDERDATE,
@STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
@STR_COSTCOD, @STR_COST
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
IF @STR_ORDERTYPE = 'JKUL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'JKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
@STR_ORDERRSN + SPACE(15) + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'RKUL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
@STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'ZKUL'
BEGIN
SELECT @STR_ACTCH=ACT_CHG,
@STR_COSTCNTR=COST_CNTR, @STR_LOCCODE=LOC_CODE,
@STR_ORDERNUM=PROJ_NUM, @STR_EC=CONVERT(CHAR(1),ENV_CODE) FROM
db_ORDERS..tbl_ORDERCOST WHERE DIST_COD=@STR_COSTCOD AND
OFFICE=@STR_ORDEROFFICE
IF @STR_COST='*' AND
@@ROWCOUNT>0
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
@STR_ACTCH + @STR_COSTCNTR + @STR_LOCCODE + SPACE(6) + @STR_ORDERNUM
+
@STR_EC + SPACE(111) + @STR_TAX + @STR_SECCODE
SELECT
@INT_COUNT=@INT_COUNT+1
END
ELSE
BEGIN
IF @STR_ORDERREMOTE !=
'DDDL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
SPACE(146) + @STR_TAX + @STR_SECCODE
SELECT
@INT_COUNT=@INT_COUNT+1
END
ELSE
BEGIN
SELECT
@INT_TAXSUM=SUM(DVALUE) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE
SELECT
@INT_TAXCNT=COUNT(*) FROM #ORDEREXC WHERE (SYSTEM_COD='MISA' OR
SYSTEM_COD='KIPA') AND INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE
SELECT
@INT_DSUM=SUM(DVALUE) FROM #ORDEREXC WHERE SYSTEM_COD='DDDL' AND
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE
IF
@INT_DSUM<=@INT_TAXSUM AND @INT_TAXCNT>0
BEGIN
INSERT
INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'ZKUL0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + @STR_ORDERINFO +
SPACE(146) + @STR_TAX + @STR_SECCODE
SELECT
@INT_COUNT=@INT_COUNT+1
END
ELSE
BEGIN
IF
@INT_DSUM>@INT_TAXSUM SELECT @STR_MISTAKE = 'ORDER IS TOO SHORT'
ELSE
SELECT @STR_MISTAKE = 'ORDER MUST HAVE EXCEPTION ON SAME DAY'
UPDATE
db_ORDERS..tbl_ORDEREXCEPTIONS SET ERR=@STR_MISTAKE WHERE
INT_ORDERID=@INT_ORDERID AND ORDERDATE=@STR_ORDERDATE AND
SYSTEM_COD='DDDL'
END
END
END
END
IF @STR_ORDERTYPE = 'QKUL' OR
@STR_ORDERTYPE = 'ZKUL'
BEGIN
IF @STR_ORDERTYPE = 'ERECM'
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE + SPACE(13) +
RIGHT(SPACE(7)+RTRIM(ROUND(@STR_ORDERINFO,0)),7) + SPACE(35) +
@STR_TAX + @STR_SECCODE
ELSE INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
RIGHT(SPACE(13)+RTRIM(ROUND(@STR_ORDERINFO,0))+'.00',13) + SPACE(7) +
SPACE(35) + @STR_TAX + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'WKUL'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'EREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + @STR_ORDERREMOTE +
RIGHT(SPACE(13)+@STR_ORDERINFO,13) + SPACE(35) + @STR_TAX +
@STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'PLAXC'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
@STR_ORDERREMOTE + SPACE(10) + REPLACE(@STR_ORDERDATE,'-','') +
LEFT(LTRIM(RTRIM(ROUND(@STR_ORDERINFO,0)))+SPACE(5),5) + SPACE(58) +
@STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
IF @STR_ORDERTYPE = 'UKLQQ'
BEGIN
INSERT INTO
db_ORDERS..tbl_ORDERDUMPRAW SELECT 'RREC0001' +
REPLACE(@STR_ORDERDATE,'-','') + REPLACE(@STR_ORDERDATE,'-','') +
@STR_ORDERREMOTE + RIGHT(SPACE(10)+@STR_ORDERINFO,10) +
REPLACE(@STR_ORDERDATE,'-','') + SPACE(63) + @STR_SECCODE
SELECT @INT_COUNT=@INT_COUNT+1
END
FETCH NEXT FROM EXCS INTO
@STR_ORDERDATE,
@STR_ORDERREMOTE, @STR_ORDERTYPE, @STR_ORDERINFO, @STR_ORDERRSN,
@STR_COSTCOD, @STR_COST
END
DEALLOCATE EXCS
UPDATE db_ORDERS..tbl_ORDERSCHEDULE SET
SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE
UPDATE db_ORDERS..tbl_ORDEREXCEPTIONS SET
SUBMTD=@STR_ORDERSUBDATE2 WHERE INT_ORDERID=@INT_ORDERID AND
ORDERDATE=@STR_ORDERDATE AND SYSTEM_COD>'' AND SYSTEM_COD NOT LIKE '?
%'
FETCH NEXT FROM SCHS INTO @STR_ORDERDATE,
@DEC_ORDERLEN, @SMALLINT_OVERNIGHT, @SMALLINT_FEES, @DECIMAL_HOLIDAY
END
DEALLOCATE SCHS
END
FETCH NEXT FROM EMPS INTO @INT_ORDERID
END
DEALLOCATE EMPS
INSERT INTO db_ORDERS..tbl_ORDERDUMPRAW SELECT 'TRLR' +
RIGHT('00000000' + LTRIM(RTRIM(CONVERT(CHAR(8),@INT_COUNT+1))),8)
DELETE db_ORDERS..tbl_LOCKEDORDERS WHERE LOCKED = 'ORDER_SUBMISSION'
GO
David Portas
> HI IS THERE A WAY TO GET MY PROC FASTER IT RUNS OK NOW BUT I JUST
> NEED
> IT FASTER THANKS.
>
<code sample snipped>
The answer is undoubtedly yes, it can go much faster. I haven't
attempted to decipher all the logic but the code looks like a poor
quality procedural solution that could probably be done much more
simply without all those cursors and UPDATEs. I suggest you throw away
this code and get someone with more experience than the original
author to re-write it from spec.
If you think you can distill the problem into something compact enough
to post here then please include DDL (CREATE TABLE statements), sample
data (INSERT statements) and show what end result you want. That way
maybe someone can help you out.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Roy Harvey
comp.databases.ms-sqlserver were right on the mark. This is a huge
mess. It could take days to decipher what is going on in that huge
procedure. Just reformating it so that I could read it would probably
take me the better part of an hour. THERE IS NOT A SINGLE COMMENT
EXPLAINING WHAT IS GOING ON! (Note the use of all upper-case for
emphasis, rather than the entire message being that way.)
Transact-SQL is a poor language for writing procedural code. Good
performance in T-SQL results from writing relational code, not
procedural. It is impossible to predict with certainty that this
procedure could be written relationally - since nobody knows what it
is doing - but that would be the most productive path to take. However
it would be a very LONG path by all appearances.
Roy Harvey
Beacon Falls, CT
SQL Menace
>
> read more »
Wow man, I need to stretch my finger after I used the wheel on my
mouse for about 5 minutes trying to read the whole proc
Whatever was said before still stands, without DDL and DML there is no
chance anyone will/can/could help you
Why don't you use dates as dates instead of chars
Can't wait for the Celko answer on this one (I know he will say
something about the dates for sure )
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
SQL Menace
> I ALWAYS STORE MY DATES AS CHAR(10), IT MAKES IT MUCH EASIER TO GET
> THE DATES INTO THE COLUMN
Please read The ultimate guide to the datetime datatypes by Tibor
Karaszi to understand dates in SQL Server (http://www.karaszi.com/
SQLServer/info_datetime.asp)
There is no need to store dates as chars
Aaron Bertrand [SQL Server MVP]
> THE DATES INTO THE COLUMN
And I keep square tires in my trunk because they fit better.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
Uncle Rico
DATABASE PLZ HELP?
Aaron Bertrand [SQL Server MVP]
it off?
Next, you will need to provide the information that others have asked for.
DDL (that means create table scripts), some sample data, desired results.
See http://www.aspfaq.com/5006
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Uncle Rico" <Uncle....@gmail.com> wrote in message
news:1182262360.6...@o11g2000prd.googlegroups.com...
@remove_mebisit.nl Arno Brinkman
>I ALWAYS STORE MY DATES AS CHAR(10), IT MAKES IT MUCH EASIER TO GET THE DATES INTO THE COLUMN
Easier into the column with different kind of formats :), but very difficult to do something with it.
Although a date field would be better as a datetime field.
Regards,
Arno Brinkman
ABVisie
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database with many powerful SQL features:
http://www.firebirdsql.org
http://www.firebirdsql.info
General database developer support:
http://www.databasedevelopmentforum.com
Support list for Interbase and Firebird users:
firebird...@yahoogroups.com
Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info
Shuurai
<ten....@dnartreb.noraa> wrote:
> >I ALWAYS STORE MY DATES AS CHAR(10), IT MAKES IT MUCH EASIER TO GET
> > THE DATES INTO THE COLUMN
>
> And I keep square tires in my trunk because they fit better.
C'mon, give the guy a break... we've all had a date go in at just the
wrong angle, get stuck, and then bring the whole system to a halt...
Uncle Rico
<ten....@dnartreb.noraa> wrote:
> First, STOP YELLING. Do you know where your ALL CAPS key is? Can you turn
> it off?
>
> Next, you will need to provide the information that others have asked for.
> DDL (that means create table scripts), some sample data, desired results.
>
> news:1182262360.6...@o11g2000prd.googlegroups.com...
>
>
>
> >I WANT MY PROC TO RUN FASTER CAN ANYONE WHO KNOWS SOMETHING ABOUT
>
> - Show quoted text -
MY MESSAGE IS IN ALL CAPS CAUSE THIS IS URGENT.
I TYPE MY SQL IN ALL CAPS SO I DON'T WEAR OUT MY SHIFT KEY
WHY DO YOU HAVE TO COMMENT ON MY CODE? I'M HERE ONLY TO GET MY CODE
TO RUN FASTER I DIDN'T POST FOR YOU ALL TO CRITICIZE ME. IF YOU DON'T
KNOW HOW TO GET IT TO RUN FASTER, PLEASE, DON'T REPLY AT ALL.
Andrew J. Kelly
caps altogether. Aaron has told you what to do if you want more help.
Ignoring his request won't help your situation.
--
Andrew J. Kelly SQL MVP
"Uncle Rico" <Uncle....@gmail.com> wrote in message
news:1182265611....@g4g2000hsf.googlegroups.com...
Alex Kuznetsov
I would suggest rewriting this code from scratch by a skilled DBD. I
think it is so poorly written that trying to optimize it with minor
changes would be a waste of time.
Tony Rogerson
for their time.
Remember, we answer problems in our own time; seriously - stop yelling - i
really doubt you'll get any useful answer until you do.....
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Uncle Rico" <Uncle....@gmail.com> wrote in message
news:1182265611....@g4g2000hsf.googlegroups.com...
Aaron Bertrand [SQL Server MVP]
Everybody's problem is "urgent." Why is yours more important than anyone
else's? Does typing in ALL CAPS really make it more obvious that your
problem is urgent, compared to speaking a little more calmly, e.g. "I have
an urgent problem..."
> WHY DO YOU HAVE TO COMMENT ON MY CODE?
When did I comment on your code?
--
Aaron Bertrand
Ion Popescu
> WHY DO YOU HAVE TO COMMENT ON MY CODE?
> I'M HERE ONLY TO GET MY CODE TO RUN FASTER
> I DIDN'T POST FOR YOU ALL TO CRITICIZE ME. IF YOU DON'T
> KNOW HOW TO GET IT TO RUN FASTER, PLEASE, DON'T REPLY AT ALL.
I KNOW !!! I KNOW !!!
YOU DON'T NEED TOUCH ONE LINE OF CODE. THE CODE IS PERFECT.
TO MAKE IT RUN FASTER YOU SHOULD BUY A BETTER SERVER !!!
(This response is just a joke to match your style)
Uncle Rico
i'm sorry, it's just i'm under a lot of pressure. i wrote all this
code myself and it took 3 months. i thought my code was written very
efficiently, some pointers on what i can do to speed up this code is
all i'm after.
Aaron Bertrand [SQL Server MVP]
> code myself and it took 3 months. i thought my code was written very
> efficiently, some pointers on what i can do to speed up this code is
> all i'm after.
Did you miss the multiple requests to show what you're actually dealing with
and actually trying to do, and the suggestions to start over?
--
Aaron Bertrand
TheSQLGuru
Lets examine that statement. You spent THREE months of your time (say 400+
manhours) writing code where you had full access to the tables, data and
specification. Now you come to this group with 1010!!! lines of a single
sproc without providing any table structures or specification and no
comments in the code. You expect us to drop everything because you are
SHOUTING and it is URGENT (which you initially failed to mention, btw) and
on our OWN, UNPAID time rework something that took you hundreds of hours to
complete?? Think about it -- how selfish is that?? Add in that you were
contentious with others on the forum. Not good Mr. Rico...
Another line "i thought my code was written very efficiently, some pointers
on what i can do to speed up this code is all i'm after."
Nope, your code is most certainly not efficent. Sorry, but there is no
other way to put it, and that is not a personal condemnation just a
statement of fact. The biggest pointer I can give (and as has been
mentioned by others) is to remove the row-processing logic and move to
set-based logic. Unfortunately for a situation as complex as this one
appears to be that is a VERY difficult undertaking and could well be beyond
what your experience and training will allow you to achieve (that is not a
personal condemnation either). Thus I will also STRONGLY recommend that
your company hire a professional to redo this sproc from scratch if they
truly desire a high-performance piece of code.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uncle Rico" <Uncle....@gmail.com> wrote in message
news:1182270506.8...@q69g2000hsb.googlegroups.com...
Alex Kuznetsov
I would get myself a good book, such as "Inside SQL Server 2005: T-SQL
Programming", read the chapters on cursors and temp tables, and
rewrite the script.
I would split the script into manageable chunks of code at most 40-50
lines each.
Most likely most of your cursors can be replaced with set-based
queries. If not, make sure you have proper indexes on your temp tables
whenever you select from them.
Roy Harvey
I think someone found that piece of garbage in some database somewhere
and decided to see what sort of reaction it would get. And they got
one. But obviously they are not serious (wear out the shift key?).
Aaron Bertrand [SQL Server MVP]
> and decided to see what sort of reaction it would get. And they got
> one. But obviously they are not serious (wear out the shift key?).
If it's any consolation, I've seen worse. Yes, I inherited the code, and
yes, I started over from scratch. :-)
Uncle Rico
have worked with sql for 10 years and have always used cursors but if
that is the only way to get the proc faster then i will do what i have
to do but i dont think my boss will be happy if it will take a long
time
please if you know how to rewrite cursors give me some help thanks
David Portas
> ok i guess i will rewrite the cursors but i dont know how to do that i
> have worked with sql for 10 years and have always used cursors
That is a tragedy.
>
> please if you know how to rewrite cursors give me some help thanks
How can we help you? You still haven't explained what you want to do
and I doubt anyone will have the time or the inclination to try to
interpret the code you posted.
Chris2
> ok i guess i will rewrite the cursors but i dont know how to do that
i
> have worked with sql for 10 years and have always used cursors but
if
> that is the only way to get the proc faster then i will do what i
have
> to do but i dont think my boss will be happy if it will take a long
> time
Uncle Rico,
Please be aware that cursors are a capability of T-SQL that are
generally a last resort for "can't do it any other way" or "importing
from non-Normalized sources where a Normalizing Union query won't
work" situations.
> please if you know how to rewrite cursors give me some help thanks
>
Based on the initial part of your code, the code is pulling
information from tbl_ORDERSTATUS, and sticking it into variables and a
temp table.
There is a variable @STR_NUM, and appears to be set based on the
contents of a portion of the SENTNAME column of tbl_ORDERSTATUS.
We're right at the start, and we can already see that the database is
not normalized.
If the spec requires you to yank information out of columns in this
manner, the table wasn't properly designed to begin with. Google
Normalization.
A relational database is not going to run quickly if it is not
normalized such that it requires complicated substring data
extractions from columns.
Any type of function applied to a column slows down processing.
The first code segment after the temporary tables are created (11
temporary tables!) has a LEFT wrapped around an LTRIM wrapped around
two CONVERTs wrapped around a MAX wrapped around an SUBSTRING! Wow!
I'm spinning just looking at it.
There are SENTNAME! and SENTNAME columns in tbl_ORDERSTATUS. It may
not be invalid, but it certainly isn't standard to include an
exclamation mark in a column name. What information is it supposed to
convey? Is it like the SENTNAME column, only more emphasized? This
is another indicator of poor database design (vague object and
attribute names).
The greater than/less than logic that is making decisions based on
various ranges from a number plucked from the SENTNAME column needs to
be represented as facts in your database, not as code-logic. (And
tbl_ORDERSTATUS is in huge need of redesign so that the SENTNAME
column is not overloaded. This table is not in first normal form.)
You would have a table indicating the specified ranges, and you would
join that tbl_ORDERSTATUS to restrict the rows you need.
@STR_FILENAME is then set to a value based on the logic/range
processing that has just occurred.
tbl_ORDERSTATUS.SENTNAME is then updated using @STR_FILENAME in
certain rows, WHERE SENTNAME is zero length string and WHERE
tbl_ORDERSTATUS.INT_ORDERID matches tbl_ORDERCUSTOMERS.OFFICE rows
that have a value of 'ORDR'.
Then an INSERT happens into #KEY from tbl_ORDERSTATUS for rows that
have a SENTNAME equal to @STR_FILENAME (which we just set above).
Then, tbl_ORDERSTATUS gets queried again to load two more variables,
again based on rows that have a SENTNAME equal to @STR_FILENAME (which
we just set above).
Then, hold on to our hats, #KEY gets an index.
It is almost as if an attempt is being made to process through
permanent tables, except they only exist during this sproc. To me
this is another indicator of vague/incomplete/non-normalized db
design.
Then, a cursor gets declared on #KEY.
The cursor then begins to go through that, row by row.
It loads some variable based on certain decisions, and then does an
UPDATE of tbl_ORDERSCHEDULE using those variables.
If I wanted this process to run more quickly, I'd have a better db
design where I simply joined tbl_ORDERSTATUS to tbl_ORDERSCHEDULE
along with joins to any necessary business logic fact tables. I might
need a VIEW or three (maybe). I would then run the UPDATE based on
the information retrieved from that.
Oh, the PRINTed message at the end of the cursor loop, I would toss
that right out the window.
Once the main process noted above was done, I'd have an extra query
(or mini sproc) run against the tables to pop out that message as
necessary.
You asked for information on how to replace your cursor-driver
processing with set-based processing.
I've given it as best I can based on the lack of DDL, sample data, and
sample output data.
Everything I see points to the need for db schema overhaul, on top of
the need for cursor-elimination.
Sincerely,
Chris O.
Bob
1) Break it up.
Use a number of smaller stored procedures or functions instead of one long
one.
2) Formatting. Don't use all UPPER case. It makes things difficult to
read. You may have got that hint already. Use tabbing to demonstrate blocks
of code.
3) Store your dates as DATETIME. Dates are stored in the database as two
4-byte integers which is more efficient than your storage mechanism. All
that DATEPART / CONVERT stuff is absolutely unnecessary.
4) Try to avoid cursors. Cursors are slow. Most problems will have a
set-based solution which will be faster.
5) Try to avoid excessive use of functions, eg RIGHT, LEFT, CONVERT. Ask
yourself, why isn't the data in a format I can use directly?
6) Avoid excessive use of SUBSTRING. This indicates your column is storing
more than one value and is therefore not atomic. Your database should be
normal form. If it isn't, ask your self why not. See 'Normalization' in SQL
2005 Books Online (BOL)
7) Avoid mixing DDL ( eg CREATE TABLE, CREATE INDEX ) with DDL ( eg SELECT,
INSERT, UPDATE ). This is known as interleaving
and can cause excessive recompilation in your stored procedure. Point 1,
'Break it up' will help with this.
8) Avoid SELECT .. INTO. Better to use CREATE TABLE / INSERT instead
9) Consider your use of ORDER BY. In a true table, the order the rows are
in doesn't matter, and if the tables have clustered indexes the data will be
sorted by the index, not the order you are using. Do you really need to use
it in your INSERTs?
Anyway there's a few tips to get you started. Now you have a working
procedure, it should be easy to make small incremental changes and verify you
get the same results.
Good luck. Hope that helps.
wBob
Uncle Rico
instead of buffoons who just want to be mean about my hard work in my
code that i did for 3 months i will try to make all the changes you
said and it will go faster i hope
please nobody else reply cause only bob and chris2 know what they are
talking about thanks
SQL Menace
So first it was ALL CAPS now it is no caps, every heard of proper case
and capitalizing names and the letter I
SQL Menace
Turkish proverb: No matter how far you have gone on a wrong road, turn
back.
Got this from Practices of an Agile Developer by Venkat Subramaniam,
Andy Hunt
http://www.amazon.com/gp/product/097451408X/102-5735017-0910517?ie=UTF8&tag=sql08-20&linkCode=xm2&camp=1789&creativeASIN=097451408X
Seems pretty good advice for you
Aaron Bertrand [SQL Server MVP]
> talking about thanks
All right ingrate, welcome to my short "don't reply" list.
--
Aaron Bertrand
TheSQLGuru
--
TheSQLGuru
President
Indicium Resources, Inc.
"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> wrote in message
news:O8haa1At...@TK2MSFTNGP06.phx.gbl...
Chris2
Uncle Rico,
You're welcome.*
I'll disclaim the sql genius part . . . I am not.
-------------------
* I am doubtful as to how much help my vague generalities could have provided, but it was
all I could come up with.
Sincerely,
Chris O.
Lee Boozer
>ok i guess i will rewrite the cursors but i dont know how to do that i
>have worked with sql for 10 years and have always used cursors
Not meaning to be condescending, but if you've been doing SQL for 10 years and
you've always used a lot of cursors, then you've never learned how to use SQL
properly. There are times when nothing but a cursor will do, but I can typically
count on one hand the number of cursors I have to code when writing a complete
application. I avoid them like the plague, because (as you are experiencing)
they are dog slow.
When coding SQL you *really* need to be thinking in set-based logic. I.E. "I
need to do something, now how do I get SQL to do it to a complete SET of rows at
once?" I suggest taking your first cursor, and spend some time trying to figure
out if there is any way to accomplish the same end result, but without using
cursors. Use temp tables if necessary, store interim results and/or working data
as extra columns in the temp tables if you have to (for instance your
@DECIMAL_MAXORDER and @STR_ORDEROFFICE variables might be stored as interim
results in your #KEY table). But try to accomplish the same thing as a series of
simple SQL statements. If you accomplish that, I can almost guarantee it will
run much faster than your cursor solution.
You may also want to throw some debug statements (such as "Print GetDate()") in
to see which cursor(s) are the biggest time sinks, and concentrate on trying to
eliminate the slowest first.
Good luck,
Lee