Well - examples ARE nice, but they'll actually be useful if we have some clue on how your data is laid out.

Your DDL (table structure) and some example data would...

Good thing to walk away some times....:) your TWO subqueries look like they can be done as ONE subquery

Select

F_K_HTXTITL_HTXLINC,

...

Scott Duncan (11/29/2007)

---

As above, you will need a CPU license for the reasons given. You will need one license per CPU (socket, not core) installed in the server, regardless...

welll...then...good job!!!!!

:hehe::cool::w00t:;):P:D:)

Jeff Moden (11/29/2007)

---

Heh... That would be true for outputs... imported files are a whole (or is it "hole"?)...

k - let's focus on just one. Say - the one I updated. What indexes do you have on the tables involved?

Also - did you get a chance...

oops - let's revise that one little bit:

select ssn, sum(maxcred) total from

(select ssn, flag, max(credit) maxcred from tmpltable group by ssn,flag) T

group by ssn

You only need to group by...

question - is the execution on the view we were working on (old versus new) any faster?

Just curious, because the multiple nested views might be the issue.

It looks to me...

Note: this is NOT going to scale well. If you need to do this against something big, you should be able to go get lunch, it is going to...

Oops - forgot one thing

Select

DHT_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,

HTXLINC.DHL_LINC_NBR as LINC_NBR,

DHL_RIGHTS_IND as RIGHTS_IND,

DPR_NON_PATENT_IND as NON_PATENT_IND,

DPR_MUNC_CODE MUNC_CODE,

DPR_PROPRTY_PARCEL_ID PROPRTY_PARCEL_ID,

Linccount

from ALTA_Staging..DB_HTXLINC HTXLINC

inner join

ALTA_Staging..DB_HTXTITL HTXTITL

on HTXTITL.P_K = HTXLINC.F_K_HTXTITL_HTXLINC

inner join

(Select Count(*) as Linccount...

Jason - exhibit A:

So - the syntax...

I'm thinking you were talking to PW - check out my example - should help a little.

The trick is not to have a reference to the OUTER query in the...

Both vwMinLTOXLINC and vwMinHTOXLINC contain multiple correlated sub-queries. The LINC_COUNT is causing the DB_HTXLINC and DB_LTXLINC to be scanned once for EACH row in the outer view. That's...

If b has multiple rows with the SAME value in project, then it's going to "duplicate" or rather show duplicatively the same row from employee.

you could try throwing a DISTINCT...

Jessica (11/29/2007)

---

Got this from another DBA:

The problem appears to be that the tables DB_HTXTITL and DB_LTXTITL are scanning an index instead of seeking on based on the DRE_TITLE_REFRNC_NBR column of...