SQL Server Performance

Very bad Performance

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Willi, Nov 27, 2007.

  1. Willi New Member

    Hello!
    (sorry for my bad English, but i hope, you understand me!)
    CONVERT(decimal(8, 2),
    case when
    (
    SELECT COUNT(*)
    FROM tblAccount
    WHERE SID = (
    SELECT MAX(SID)
    FROM tblAccount
    WHERE MemberID = @MemberID And sDate > '01/01/2004'
    )
    ) > 0 then
    (
    SELECT Endsaldo
    FROM tblAccount
    WHERE SID = (
    SELECT MAX(SID)
    FROM tblAccount
    WHERE MemberID= @MemberID And sDate > '01/01/2004'
    )
    )
    else
    '0.00'
    end
    ) AS theCurrent
    This Script ist as Part of many Queries and have a very bad Performance. I tested some other Methods, but: The Performance is either slow and bad. A Function in Microsoft Access is much faster! Can't believe it. Do you can help me for a better, faster version?
    Big thanks!
  2. anandchatterjee New Member

    IN first look
    I think thses two area are un-necessaryly called for each rows
    1.
    SELECT MAX(SID)
    FROM tblAccount
    WHERE MemberID = @MemberID And sDate > '01/01/2004'
    2.
    SELECT MAX(SID)
    FROM tblAccount
    WHERE MemberID= @MemberID And sDate > '01/01/2004'
    You can store this value inside an variable and execute the remaining portion and you will definitely get a performance boost. Let me know your experience after implementing it.
  3. Willi New Member

    Thanks for your Answer!
    And what can i make when:
    SELECT
    MemberID, SomeOtherFields,
    CONVERT(decimal(8, 2),
    case when
    (
    SELECT COUNT(*)
    FROM tblAccount
    WHERE SID = (
    SELECT MAX(SID)
    FROM tblAccount
    WHERE MemberID = MemberID And sDate > '01/01/2004'
    )
    ) > 0 then
    (
    SELECT Endsaldo
    FROM tblAccount
    WHERE SID = (
    SELECT MAX(SID)
    FROM tblAccount
    WHERE MemberID= MemberID And sDate > '01/01/2004'
    )
    )
    else
    '0.00'
    end
    ) AS theCurrent,
    Other Fields
    From Table
    Where blablabla
    Is the best way to use a Function?
  4. Adriaan New Member

    I'm not sure you're getting correct results in the subqueries, since you're using
    WHERE MemberID = MemberID
    without mentioning the tables (or their aliases). Always prefix all columns with the table from which you're taking them, (1) to avoid confusion and error, and (2) to improve the chances of execution plans getting reused.
    Other than that, use a derived table (an aliased query in the FROM clause) to get the MAX(SID) per MemberID for the sDate criteria, and join that on the MemberId and SID on your main table (add an alias to the MAX() so you can join on it).
    Finally, if the COUNT(*) is 0, then the SUM() will be 0 or NULL. You should be able to do a simple SUM() with a GROUP BY, and add ISNULL() to insert a 0 for a NULL, without the fancy subquery. Think simple - it usually works better!
  5. Willi New Member

    I'm so sorry, that's my error:
    WHERE MemberID = MemberID
    Yes, i use TableAlias in the real Script like this:
    WHERE TableAlias1.MemberID = TableAlias2.MemberID
    The Result is Correct, but the Performance, that i'snt correct. Microsoft Access is faster, can't believie it! [:S]
    2 more Scripts:
    DECLARE @SID int;
    DECLARE @theCurrent nvarchar(10)

    SET @SID = (SELECT Max(SID) AS MaxSID
    FROM tblAccount
    WHERE MemberID = @MemberID And sDate > '01/01/1990');

    IF (@SID is not null AND @SID > 0)
    SET @theCurrent = CONVERT(nvarchar, (SELECT Endsaldo
    FROM tblAccount
    WHERE SID = @SID));
    ELSE
    SET @theCurrent = '0.00';
    Yes, i test with nvarchar and Decimal. An other Script:
    SET @SID = (SELECT Endsaldo
    FROM tblAccount AS tAccount INNER JOIN
    (
    SELECT Max(SID) AS MaxSID
    FROM tblAccount
    WHERE MemberID = @MemberID And sDate > '01/01/1990'
    ) AS tmp1
    ON tAccount.SID = MaxSID);
    All the Scripts i tested: Bad Performance. Yes, the Performance is now better, but Access is faster, this is an SQL Server and other SQL-Scripts in the Site (ASP.NET) is faster. Very big Confused! I hope, you can help me for a better World (hey, for a faster World). Thank you!

  6. Adriaan New Member

    Does this give you the correct results per MemberID, ignoring the additional columns?
    SELECT t1.MemberID, CONVERT(decimal(8, 2), ISNULL(t1.Endsaldo, 0)) TheCurrent
    FROM tblMember m
    FULL JOIN tblAccount t1 ON m.MemberID = t1.MemberID
    WHERE t1.SID =
    (SELECT MAX(t2.SID) FROM tblAccount t2
    WHERE t2.MemberID = t1.MemberID And t2.sDate > '01/01/2004')
  7. Willi New Member

    No!
    But this give me the correct results:
    SELECT
    tblAccounts.MemberID,
    CONVERT(decimal(8, 2), ISNULL(tblMember.Endsaldo, 0)) TheCurrent
    FROM
    tblMember RIGHT JOIN tblAccounts AC ON tblAccounts.MemberID = tblMember.MemberID
    WHERE tblMember.SID =(
    SELECT MAX(t2.SID)
    FROM tblMember t2
    WHERE t2.MemberID = tblAccounts.MemberID And tblMember.sDate > '01/01/2004'
    )
    I'm not sure to understand your question or what you mean with it. But i hope, you understand me and my Problem. Thanks for all!
  8. Adriaan New Member

    And did that improve the performance?
  9. Willi New Member

    <P mce_keep="true">Hello <IMG style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" alt="" src="http://sql-server-performance.com/Community/Themes/default/images/user_IsOffline.gif">Adriaan,</P><P mce_keep="true">Yes, it improve the performance. Old: 6.20 Minutes, new: 3.30 Minutes in&nbsp;a Query. In an other Query: 0.5 to 0.9 Second, new: 0.28 to 0.45 Second. In the same ASP.NET-Site is an SQL-Query with more than 30 Fields, 3 Joins: 0.08 to 0.15 Second. This bigger Query is faster!</P><P mce_keep="true">And: Microsoft Access is faster. I mean, less than 0.1 Second.</P><P mce_keep="true">&nbsp;[*-)]</P>
  10. Adriaan New Member

    By any chance, is this an Access database that was 'upgraded' to SQL Server?
    One of the things that Access does is adding an implicit index on both sides of a Foreign Key relationship. In SQL Server, you have to add these indexes yourself.
    So if you're comparing a query by Jet on data in an MDB file, against SQL in an 'upgraded' database, then it's apples and oranges.
    Final option - try adding OPTION (MAXDOP 1) as the final clause in your T-SQL query. Sometimes SQL gets too many processors involved in a query, and performance can drop dramatically.
  11. Willi New Member

    Thanks for your Answer!
    >By any chance, is this an Access database that was 'upgraded' to SQL Server?
    Yes, i make a Import from Access to SQL Server.
    >One of the things that Access does is adding an implicit index on bothsides of a Foreign Key relationship. In SQL Server, you have to addthese indexes
    yourself.
    Oh, OK! But: On Which Field i have to make a Index? MemberID and on the Date-Field?

    >Final option - try adding OPTION (MAXDOP 1) as the final clause in yourT-SQL query. Sometimes SQL gets too many processors involved in aquery, and performance can drop dramatically.
    I have tested and become an error. Like "Error in near of Option".
    If i write:
    Select * FROM TableName OPTION (MAXDOP 1)
    its ok, no Error.
    if i write:
    SELECT
    tblAccounts.MemberID,
    CONVERT(decimal(8, 2), ISNULL(tblMember.Endsaldo, 0)) TheCurrent
    FROM
    tblMember RIGHT JOIN tblAccounts AC ON tblAccounts.MemberID = tblMember.MemberID
    WHERE tblMember.SID =(
    SELECT MAX(t2.SID)
    FROM tblMember t2
    WHERE t2.MemberID = tblAccounts.MemberID And tblMember.sDate > '01/01/2004'
    ) OPTION (MAXDOP 1)
    OR:
    SELECT
    tblAccounts.MemberID,
    CONVERT(decimal(8, 2), ISNULL(tblMember.Endsaldo, 0)) TheCurrent
    FROM
    tblMember RIGHT JOIN tblAccounts AC ON tblAccounts.MemberID = tblMember.MemberID
    WHERE tblMember.SID =(
    SELECT MAX(t2.SID)
    FROM tblMember t2
    WHERE t2.MemberID = tblAccounts.MemberID And tblMember.sDate > '01/01/2004'
    OPTION (MAXDOP 1)
    )
    OR:
    SELECT
    tblAccounts.MemberID,
    CONVERT(decimal(8, 2), ISNULL(tblMember.Endsaldo, 0)) TheCurrent
    FROM
    tblMember RIGHT JOIN tblAccounts AC ON tblAccounts.MemberID = tblMember.MemberID
    WHERE tblMember.SID =(
    SELECT MAX(t2.SID)
    FROM tblMember t2
    WHERE t2.MemberID = tblAccounts.MemberID And tblMember.sDate > '01/01/2004'
    OPTION (MAXDOP 1)
    ) OPTION (MAXDOP 1)
    All Scripts say my Editor: Error. I use a SQL Server Developer Edition.
  12. Adriaan New Member

    You are not consistently using table names and aliases - for instance since you've added the AC alias for tblAccount, you can only use AC in the rest of the query, not tblAccount (in the column list after SELECT, in the ON clause, etc.).
    The OPTION (MAXDOP 1) bit should be the very last thing in the main query, so do not put it in the subquery.
  13. jasonL New Member

    Modified from Andriaan: (WIlli's sub-qry 11-28-2007 6:26 AM DOES not look right????):
    INDEX needed account.memberid+sdate
    TRY 1: (assume account table is optional join to member)
    SELECT t1.MemberID, isnull(t1.Endsaldo,0) TheCurrent
    FROM tblMember m
    RIGHT JOIN tblAccount t1 ON m.MemberID = t1.MemberID And t1.sDate > '01/01/2004'
    WHERE t1.SID =
    (SELECT MAX(t2.SID) FROM tblAccount t2
    WHERE t2.MemberID = t1.MemberID And t2.sDate > '01/01/2004')
    This should be quick ??? EXPLAINATION: Outer qry is index join, Sub-qry is index lookip
    TRY 2:
    SELECT t1.MemberID, t1.Endsaldo TheCurrent
    FROM tblMember m
    FULL JOIN tblAccount t1 ON m.MemberID = t1.MemberID And t1.sDate > '01/01/2004'
    WHERE t1.SID =
    (SELECT MAX(t2.SID) FROM tblAccount t2
    WHERE t2.MemberID = t1.MemberID And t2.sDate > '01/01/2004')
    union
    SELECT t1.MemberID, 0 TheCurrent
    FROM tblMember m
    where not exists (select '1' from tblAccount t1 where t1.MemberID = m.MemberID And t1.sDate > '01/01/2004')

    Will need to have index tblaccount: memberid, sDate . Let me know how!
    EXPLAINATION: 1st qry take cares or "exists" (index scan) and second qry take cares of "when NOT exists"
    (Note: syntax not check -- above is for demonstration purpose only!!!)
    JasonL http://blogs.msdn.com/usisvde

Share This Page