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