Query running for very long time

[Former Member]

Help me optimize the below sql,running for many many hours Database : sybase

select
    too.toID,too.toName,frm.fromID,frm.fromName<br>
from
    entityClientTempTable frm, entityClientTempTable too
where
    frm.fromServerId = too.toServerId 
and
    frm.fromID < too.toID 
and 
    lower(substring(frm.fromName, 0, CHARINDEX(''------'',frm.fromName))) like lower(too.toName))

Number of records in entityClientTempTable : half a million approx
There are no indexes on entityClientTempTable table
entityClientTempTable is a temporary table.

[Breck_Carter]

Please explain WHAT you are trying to accomplish.

Besides the obvious syntax errors, the biggest challenge MIGHT be the cartesion product (every row in entityClientTempTable is joined with every row in entityClientTempTable, so the candidate result set has 2x the number of rows).

Here's what your (fixed) code looks like when executed...

BEGIN
   DROP TABLE entityClientTempTable ;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE LOCAL TEMPORARY TABLE entityClientTempTable (
   fromServerId   VARCHAR ( 10 ) NOT NULL,
   fromID         VARCHAR ( 10 ) NOT NULL,
   fromName       VARCHAR ( 10 ) NOT NULL,
   toServerId     VARCHAR ( 10 ) NOT NULL,
   toID           VARCHAR ( 10 ) NOT NULL,
   toName         VARCHAR ( 10 ) NOT NULL )
   ON COMMIT PRESERVE ROWS;

CREATE INDEX ix_fromServerId ON entityClientTempTable ( fromServerId );
CREATE INDEX ix_to_ServerId ON entityClientTempTable ( fromServerId );

INSERT entityClientTempTable VALUES ( 'a', 'a', 'a------', 'a', 'b', 'a-' );
INSERT entityClientTempTable VALUES ( 'a', 'a', 'a------', 'a', 'b', 'a-' );
COMMIT;

select
    too.toID,too.toName,frm.fromID,frm.fromName 
from
    entityClientTempTable frm, entityClientTempTable too
where
    frm.fromServerId = too.toServerId 
and
    frm.fromID < too.toID 
and 
    lower(substring(frm.fromName, 1, CHARINDEX('------',frm.fromName))) like lower(too.toName);

toID       toName     fromID     fromName   
---------- ---------- ---------- ---------- 
b          a-         a          a------    
b          a-         a          a------    
b          a-         a          a------    
b          a-         a          a------