[AccessD] OT kinda: Order of criteria in SQL statement

Dan Waters dwaters at usinternet.com
Fri Nov 21 10:40:20 CST 2008


Hi Mark,

Sorry - I'm not familiar with PeopleSoft.

Dan

-----Original Message-----

OK...This just got weirder...lets forget about the criteria...I decided to
just not select the field...and the query crashed.  This SQL is running
inside a PeopleSoft query tool.  I think the tool is doing something funny
when executing.  All I did was omit a single field in the select
statement...and I crashed again...Below is what runs...and does not...only
dif I see is 1 less field in the select???  Thanks Mark
 
 
******************THIS RUNS**************************
SELECT A.CASE_ID, DATE( A.ROW_ADDED_DTTM), G.XLATLONGNAME, D.XLATLONGNAME,
A.SR_COST,D.FIELDNAME,D.FIELDVALUE,D.EFFDT,G.FIELDNAME,G.FIELDVALUE,G.EFFDT 
FROM PS_SR_RC_CONCESS A, PS_RC_CASE E, PSXLATITEM D, PSXLATITEM G 
WHERE A.CASE_ID = E.CASE_ID 
AND A.BUSINESS_UNIT = E.BUSINESS_UNIT 
AND DATE( A.ROW_ADDED_DTTM) BETWEEN :1 AND :2 
AND D.EFFDT = 
(SELECT MAX(D_ED.EFFDT) FROM PSXLATITEM D_ED 
WHERE D.FIELDNAME = D_ED.FIELDNAME 
AND D.FIELDVALUE = D_ED.FIELDVALUE 
AND D_ED.EFFDT <= CURRENT DATE) 
AND A.SR_CONCE_STATUS = D.FIELDVALUE 
AND D.FIELDNAME = 'SR_CONCE_STATUS' 
AND G.EFFDT = 
(SELECT MAX(G_ED.EFFDT) FROM PSXLATITEM G_ED 
WHERE G.FIELDNAME = G_ED.FIELDNAME 
AND G.FIELDVALUE = G_ED.FIELDVALUE 
AND G_ED.EFFDT <= CURRENT DATE) 
AND G.FIELDNAME = 'SR_CONCE_TYPE' 
AND G.FIELDVALUE = A.SR_CONCE_TYPE 
AND E.RC_SEVERITY = '13' 
******************THIS RUNS**************************
 
******************DOES NOT RUN**************************
SELECT A.CASE_ID, DATE( A.ROW_ADDED_DTTM), G.XLATLONGNAME,
A.SR_COST,D.FIELDNAME,D.FIELDVALUE,D.EFFDT,G.FIELDNAME,G.FIELDVALUE,G.EFFDT 
FROM PS_SR_RC_CONCESS A, PS_RC_CASE E, PSXLATITEM D, PSXLATITEM G 
WHERE A.CASE_ID = E.CASE_ID 
AND A.BUSINESS_UNIT = E.BUSINESS_UNIT 
AND DATE( A.ROW_ADDED_DTTM) BETWEEN :1 AND :2 
AND D.EFFDT = 
(SELECT MAX(D_ED.EFFDT) FROM PSXLATITEM D_ED 
WHERE D.FIELDNAME = D_ED.FIELDNAME 
AND D.FIELDVALUE = D_ED.FIELDVALUE 
AND D_ED.EFFDT <= CURRENT DATE) 
AND A.SR_CONCE_STATUS = D.FIELDVALUE 
AND D.FIELDNAME = 'SR_CONCE_STATUS' 
AND G.EFFDT = 
(SELECT MAX(G_ED.EFFDT) FROM PSXLATITEM G_ED 
WHERE G.FIELDNAME = G_ED.FIELDNAME 
AND G.FIELDVALUE = G_ED.FIELDVALUE 
AND G_ED.EFFDT <= CURRENT DATE) 
AND G.FIELDNAME = 'SR_CONCE_TYPE' 
AND G.FIELDVALUE = A.SR_CONCE_TYPE 
AND E.RC_SEVERITY = '13' 
******************DOES NOT RUN**************************
 


> From: dwaters at usinternet.com
> To: accessd at databaseadvisors.com
> Date: Fri, 21 Nov 2008 09:44:23 -0600
> Subject: Re: [AccessD] OT kinda: Order of criteria in SQL statement
> 
> Mark - can you post the SQL statement?
> 
> Dan
> 
> -----Original Message-----
> 
> 
> Hello All,
> 
> Does the order/sequence you add your criteria (WHERE clause) in a SQL
> statement affect performance? The query in question has a 'lookup' table
> that translates values from a certain field. When I remove this
table...the
> query no longer runs (times out...system has 10 minute governor). This
> table join is right in the middle of the WHERE clause. Removing this will
> not pull more rows, so unless the order matters...I can't understand what
my
> issue is?
> 
> Thanks,
> 
> Mark A. Matte
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Windows Live Hotmail now works up to 70% faster.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_faster_
112008
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list