Hi why does this sql code start then just
hans with the sql cursor without any movement ?

SELECT UPPER(STDCODES.TOWN), UPPER(UKSTREET.TOWN)
FROM STDCODES, UKSTREET
WHERE STDCODES.TOWN = UKSTREET.TOWN

Rita

Rita


Its probably because you're doing a cartesian join (I think that's the right name) and creating an absolutely gimormous dataset. Try a simple join instead. Something like:

SELECT UPPER(STDCODES.TOWN), UPPER(UKSTREET.TOWN)
FROM STDCODES
JOIN UKSTREET ON STDCODES.TOWN = UKSTREET.TOWN


Roy Lambert [Team Elevate]

Just the same hangs for YONKS
I converted the Town in stcodes
to upper to match the ukstreet
table and dropped the upper function
and it breezed through. The ukstreet
table is huge but nothing that has ever
slowed me down to date.
Maybe all them streets need sweeping.
Or that upper function does a ton of
work.
Rita

I think upper in this case would be doing lot ot work since you're asking it
to convert during the sql execution so at the very least it'll run once per
row match and can't really use an index (and upper is a string operation so
relatively slow)).

Using either an uppercase fields (like you did) or even case-insensitive
index should speed things up a bit.

What does the execution plan say for the query ?

Raul


"Rita" <nospam@nospam> wrote in message
news:3F99F96C-C656-42E9-897E-3A0B39F63614@news.elevatesoft.com...
> Just the same hangs for YONKS
> I converted the Town in stcodes
> to upper to match the ukstreet
> table and dropped the upper function
> and it breezed through. The ukstreet
> table is huge but nothing that has ever
> slowed me down to date.
> Maybe all them streets need sweeping.
> Or that upper function does a ton of
> work.
> Rita
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4138 (20090608) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4138 (20090608) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Rita,

<< Just the same hangs for YONKS >>

If you could post the query plan for this, I can tell you what the issue is.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5661F9FA-03AB-454F-AD24-AB8BE1B57300@news.elevatesoft.com...
>
> If you could post the query plan for this, I can tell you what the issue
> is.
>

The issue was the upper function the 1st table had a field town
like 'London' the 2nd table had a field town like 'LONDON'
I exported the data to upper in the 1st table and reimported
the whole thing its no longer a problem its sorted.
Rita