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.

asked 15 Feb, 08:36

rohanit05's gravatar image

rohanit05
10114
accept rate: 0%

3

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------    

(15 Feb, 09:21) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×14

question asked: 15 Feb, 08:36

question was seen: 158 times

last updated: 15 Feb, 09:21