Performance Tuning

[g***@gmail.com]

Hi,


Question:

When I run the query in stored procedure, it takes 7 minutes
When I run the query in ASE open window, it takes 10 seconds

What is the reason, and please help me to understand.

Query:

INSERT INTO A
Select
....
....
FROM A
JOIN B
WHERE A.ID = B.ID

I have index on both tables.
Table A- Clustered Index
Table B- Non-Clustered Index


Table A contains 60,000 rows
Table B contains 2 crores of rows
Final output: 1,50,000 rows

[Milo Minderbinder]

Post by g***@gmail.com
Hi,
When I run the query in stored procedure, it takes 7 minutes
When I run the query in ASE open window, it takes 10 seconds
What is the reason, and please help me to understand.
INSERT INTO A
Select
....
....
FROM A
JOIN B
WHERE A.ID = B.ID
I have index on both tables.
Table A- Clustered Index
Table B- Non-Clustered Index
Table A contains 60,000 rows
Table B contains 2 crores of rows
Final output: 1,50,000 rows

I'm not surprised about this behavior. Take a look at the plans. I guess
they differ. Create the procedure "with recompile". If your in luck, the
optimizer will bless your procedure (sometimes) with a useful plan.
Milo