CODEWITHSUNDEEP
sql-server-2008sql-server-2005
Areeb
Areeb

Reputation: 211

Need to Improve performance of sql queries

I have 3 table a,b,c.

select * from a

select * from b where aid in (select id from a)

select * from c where bid in (select Id from b where aid in (select id from a))

These queries in sp and working fine but as performance I need to optimise these. Could you please suggest how do I improve perfomance, or any tools available to optimise sql queries.

Thanks .

Upvotes: 0

Views: 143

Answers (5)

Pankaj
Pankaj

Reputation: 10095

  1. Avoid usage of *. Mention explicitly the column names.
  2. Use Non Clustered Index for the tables with large number of records.
  3. Use Joins instead of Sub Queries.
  4. Use "Set NoCount On" in the Stored Procedures.
  5. You can use Temp tables. Insert the required data and execute it finally. Don;t forget to drop it before you exit the stored procedure.

Upvotes: 0

user806549
user806549

Reputation:

There can be many reasons why your queries are slow. From what you describe a few starting points could be:

  • Indexes missing or incorrect. At the very least you must have indexes on columns that you join or filter on.
  • Statistics for indexes must be up-to-date
  • Indexes could in some cases benefit from having more fields as included columns.
  • Correct amount of data returned - both in terms of columns and rows. If you return too much, you'll flood the various buffers and reduce overall performance.

You might want to use the Tuning Advisor and/or SQL Server Profiler - both avaialble in the Performance Tools menu under your SQL Server folder in startup.

Also, learning the Management Studios reporting possibilities, and getting acquainted with execution plans are good starting points.

For the more advanced learners, start including statistics for query I/O (in Management Studios Query Window), using the Windows Performance Monitor to keep an eye on relevant SQL Server counters, etc. A good explanation to some of them can be found here: http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/

Upvotes: 2

Arion
Arion

Reputation: 31239

You can also try using exists instead of IN:s. If there is allot of data that can help:

SELECT 
    *
FROM 
    a

SELECT 
    * 
FROM 
    b 
WHERE EXISTS
    (
        SELECT 
            NULL 
        FROM 
            a
        WHERE
            a.id=b.aid
    )

SELECT 
    * 
FROM 
    c 
WHERE EXISTS
    (
        SELECT 
            NULL 
        FROM 
            b
        WHERE
            c.bid=b.Id
            AND EXISTS
            (
                SELECT 
                    NULL 
                FROM
                    a
                WHERE
                    b.aid=a.id
            )
    )

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26861

I think you could rewrite the queries using INNER JOIN:

select * from c 
INNER JOIN b ON b.id = c.bid 
INNER JOIN a ON a.id = b.aid

if you have indexes on id, bid and aid all should be fine

Upvotes: 3

djna
djna

Reputation: 55907

Can you express your queries like:

select * from b where b.aid = a.id and a.somefield = 'value'

Upvotes: 0

Related Questions