Poor performance is due to data tranfer between SQL Server and client application. There's no simple solution: in some way you will have to change your application or your system...

Try this:

SELECT *

FROM (

select '0' + dbo.date_format(getdate(), 'MMDDYY') AS End_Date

union all

select

'1' +

CONVERT(varchar, dbo.date_format(mt.end_date, 'MMDDYY')) +

left(right('0000000'+mt.client_num, 7) + '.' + right('0000000'+mt.matter_num, 7) + ' ...

SQLServer translates COALESCE in CASE / WHEN, so it would be exactly the same to do:

CASE

WHEN field IS NULL THEN '0'

WHEN...

varchar with no length means varchar(1), so you'll get 1 as result.

Gianluca

Thanks Gail,

I have a new top ranked item for my to-do list today...

Thank you very much Gail, I didn't know about page splits!

I use sometimes NOLOCK when my data is partially static and I need to read only the static part, avoiding...

Gail, what do you mean "reading rows twice"? I know I could miss some rows with NOLOCK,...

Try this:

UPDATE TempFactTable

SET CodeID = LookupID

FROM TempFactTable t

INNER JOIN (

SELECT LookupCode, MAX(LookupID) AS LookupID, MAX(OtherCode) AS OtherCode

FROM LookupTable

...

You can query the ASCII code for the first char:

'a' is different from 'A'

try this:

SELECT ASCII('A')

SELECT ASCII('a')

One thing you could do is:

SELECT *

FROM MyTable

WHERE ASCII(LEFT(FirstName,1)) <> ASCII(LEFT(UPPER(FirstName),1))

Onether thing you...

Your result set is too big. Work on the server side.

If you're sure every user works only on his data, try putting some NOLOCK in the select satements to avoid table and page locking during reads.

With this little information this...

It depends! What does the procedure do? Does it select or modify data?

Gianluca

Babar Javaid (3/11/2009)

---

The used SP return the aggregate data and i am calling the SP 5 million times. One time call return the whole data to code and code do...

If you can alter the stored procedure code, aggregate the data with the appropriate group by clause, in order to get the data formatted the same way you need to...

If you're building a report you should be working with a reasonable set of records and 5 million rows is far from being reasonable, so you need to cut it...