Optimize a code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Optimize a code

Author

Topic

CanadaDBA

583 Posts

Posted - 2005-11-08 : 13:50:30

Users of one of the applications are suffering to get time-out. I noticed lots of usage of INNER JOIN and OUTER JOINs in the database. Most of them are like the following. Is it possible to rewrite it in a better optimized way? Is there any folrmulation that I ask the developers to replace such codings with a better one?


SELECT ...
FROM tblAcc a
left outer join tblExp t1 on t1.AccID = a.AccID and t1.Type = 'R11'
left outer join tblExp t2 on t2.AccID = a.AccID and t2.Type = 'R12'
left outer join tblExp t3 on t3.AccID = a.AccID and t3.Type = 'R13'
left outer join tblExp t4 on t4.AccID = a.AccID and t4.Type = 'R14'
left outer join tblExp t5 on t5.AccID = a.AccID and t5.Type = 'R15'
left outer join tblExp t6 on t6.AccID = a.AccID and t6.Type = 'R16'
left outer join tblExp t7 on t7.AccID = a.AccID and t7.Type = 'R17'
left outer join tblExp t8 on t8.AccID = a.AccID and t8.Type = 'R18'
left outer join tblExp t9 on t9.AccID = a.AccID and t9.Type = 'R19'
left outer join tblExp t0 on t0.AccID = a.AccID and t0.Type = 'R20'

Canada DBA

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-08 : 14:18:19

Seems reasonable to me.

The performance problem is probably due to a poor execution plan (table scans, index scans) becuase the key fields AccID are not properly indexed, or the Statistics are not updated.

Can you plug the query above into SQL Query Analyzer, capture an execution plan and post it back here?

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 14:18:57

Do you have an index on ACCID and Type for the tblExp table?

How big is tblAcc?

It's going to return all rows in that table. Is that what you want?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-08 : 14:19:30

Is AccID unique in each table or can multiple AccIDs in one table match another AccID in another table?

CanadaDBA

583 Posts

Posted - 2005-11-08 : 15:22:25

Facts:
1. AccID is int and PK and Identity in tblAcc.
2. AccID is Int and Type is VarChar(10) in tblExp. Both make the PK and non-Identity in tblExp.
3. tblAcc has 19737 rows.
4. tblExp has 78193 rows.
In the code I don't see anywhere saying TOP 100 or something to limit the returned values.

Canada DBA

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-08 : 15:26:31

Well, with that excat code, at a minimum, you will be receive all 19737 rows in the result set, which seems insane. What is the result set used for?

That's a lot of data for application interface

Can you post the entire stored procedure? Or is the code inside the application?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CanadaDBA

583 Posts

Posted - 2005-11-08 : 16:11:33

The above code was portion of a SELECT with Inner Joins and other Outer Joins!! I just seperated portion of the SELECT to probably reduce the number of JOINs.

The code is saved as a stored procedure and the app calls it.

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-11-08 : 16:15:27

Referring to a combination of Int and Varchar as PK in tblExp, is it good that it is joined to tblAcc based on the AccID?

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-11-08 : 18:28:02

Assuming there is no reference to t1, t2, ... in the SELECT columns list, i.e. t1.Colx, t7.Coly, ... are not in the SELECT list then can I replace the original code with this?


SELECT ... FROM tblAcc a left outer join tblExp t1 on t1.AccID = a.AccID and t1.Type in ('R11','R12',...)

Canada DBA

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-08 : 21:45:45

quote:
Originally posted by CanadaDBA

Referring to a combination of Int and Varchar as PK in tblExp, is it good that it is joined to tblAcc based on the AccID?

It is common to do so, and sometimes required to get the resultset you are after.

Did you review the execution plan to look for table scans and index scans?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-09 : 06:42:26

You could always give this sort of thing a go:


SELECT ...
FROM tblAcc a
LEFT JOIN (
    SELECT AccID,
      MAX(CASE WHEN Type = 'R11' THEN ColX END) AS ColX_R11,
      MAX(CASE WHEN Type = 'R12' THEN ColX END) AS ColX_R12,
      MAX(CASE WHEN Type = 'R12' THEN ColY END) AS ColY_R12,
      ...
    FROM tblExp
    GROUP BY AccID
  ) AS t
  ON a.AccID = t.AccID

CanadaDBA

583 Posts

Posted - 2005-11-09 : 08:08:55

Thank you for the code, Arnold. The reason that I want to replace the original code with


SELECT ... 
  FROM tblAcc a LEFT OUTER JOIN tblExp t1 
                ON t1.AccID = a.AccID AND t1.Type IN ('R11','R12',...)

is to optimize the execution. At the moment, it is slow and I believe the developers have got benefit of "copy & paste" to develop their code faster!! Now, the question is that am I optimizing and speeding up the execution of the code by this replacement?

Canada DBA

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-09 : 08:44:19

What does the execution plan look like?

CanadaDBA

583 Posts

Posted - 2005-11-09 : 12:52:21

The SELECT is part of a stored procedure. One of the Profiler's result rows is a EXEC <spName> <Parameters list> that calls this SP and a number of 32188 is in Duration column. I beleive this number is in ms (Please notify if I am wrong).

Using same parameters, the Execution Plan returned a long list. But honestly, I don't undrestand it! I was not able to copy and paste it here. There are 35 Query in the result pane. The SELECT is part of Query 34. Query cost (relative to the batch): 0.00%

BOL: Additionally, SQL Query Analyzer shows suggestions for additional indexes and statistics on nonindexed columns that would improve the ability of the query optimizer to process a query efficiently. How can I use it and improve the code?

quote:
Originally posted by SamC

What does the execution plan look like?

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources