Very bad Performance

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!

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.

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?

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!

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!

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')

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!

And did that improve the performance?

<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>

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.

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.

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.

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