SQL Server Performance

help on query optimization

Discussion in 'T-SQL Performance Tuning for Developers' started by sultani.khalid.af, Aug 2, 2008.

  1. sultani.khalid.af New Member

    Hi,
    can some one suggest me any alternative salution for improving the execution performance of this query becoze i am using this in a web page and it's a little time,is it the problem with count(*) or more number of subqueries.
    i have also indexed the columns in tables.
    select(select count(*) from projectinfo where ngoid=dbo.NGOInfo.ID ) as TotalProjectHeldByOrganization,
    (
    select count(*) from ProjectLeadPerson where ProjectInfoID IN (Select ID From ProjectInfo Where NGOID = dbo.NGOInfo.ID )) as TotalProjectStaff,(select Count(*) from ahscstaff where ahscinfoid in (select id from ahscinfo where ngoid=dbo.NGOInfo.ID )) as TotalAhscStaff,
    (
    select count(*) from ahscinfo where ngoid=dbo.NGOInfo.ID ) as TotalAhsc,(
    select count(*) from ahsctransport where ahscinfoid in (
    select id from ahscinfo where ngoid=dbo.NGOInfo.ID )) as TotalNumberofTransportUsedByAhscFROM
    dbo.NGOInfo where dbo.NGOInfo.Id=99
    Thanks in advance.
  2. Adriaan New Member

    Not sure you can improve much. You could drop the dbo.NGOInfo table from the main query, and just apply the "99" criteria in te subqueries. Or store that value in a variable, and then use the variable in the subqueries.
    You could also make the script easier to read by using single aggregate queries, and storing the result in a variable - like this:
    SET NOCOUNT ON
    DECLARE @result1 INT, @result2 INT
    SELECT @result1 = COUNT(*) FROM dbo.MyTable1
    SELECT @result2 = COUNT(*) FROM dbo.MyTable2
    SELECT @result1 AS Alias1, @result2 AS Alias2
    You should always include both the owner of the table/view that you're querying, and either the full table name, or a table alias, with each reference to a column. Two reasons for that:
    (1) This will improve SQL Server's chances of finding a fitting execution plan in cache the next time you run the query.
    (2) More importantly, it reduces your chances of getting incorrect results, especially when dealing with subqueries.
  3. sultani.khalid.af New Member

    HI Adriaan ,
    ALTHOUGH THERE IS NOT A HUGE DIFFERENCE IN PERFORMANCE BUT IT WAS EASY TO IDENTIFY QUERIES WITH HUGE EXECUTION PLAN MORE EASILY.
    AND CODE YOU PROVIDED IS EASY TO MAINTAIN .
    THANKS ALOT.

Share This Page