CODEWITHSUNDEEP
sqlsybase
Chris
Chris

Reputation: 639

Slow performance query

I'm having a slow query performance and sometimes it came to an error of "Can't allocate space for object 'temp work table'"

I have 2 tables and 1 view. The first two tables have an left join and the last view will do a sub query. Below is the sample query.

SELECT a.* 
  FROM Table1 a LEFT JOIN Table2 b ON a.ID = b.ID 
 WHERE a.ID (SELECT ID 
               FROM View1).

The above query is very slow. BUT when I used a #temp table it becomes faster.

SELECT ID 
  INTO #Temp 
  FROM View1

SELECT a.* 
  FROM Table1 a LEFT JOIN Table2 b ON a.ID = b.ID 
 WHERE a.ID IN (SELECT ID 
                  FROM #Temp)

Could someone explain why the first sql statement is very slow? and kindly give me an advise like adding new index?

Note: The first query statement cannot be altered or modified. I used only the second query statement to show to my team that if we put the 3rd table into temporary table and used it, makes faster.

Upvotes: 0

Views: 78

Answers (1)

MarioAna
MarioAna

Reputation: 865

Basically in the first query you are accessing the view for each and every row, and in turn the view is executing it's query.

In the second one you are executing the view's query just once and using the returned results through the temp table.

Try:

SELECT a.* 
  FROM Table1 a LEFT JOIN Table2 b ON a.ID = b.ID,
       (SELECT ID 
          FROM View1) c
 WHERE a.ID = c.ID;

Upvotes: 2

Related Questions