30 mins or so later... Hi Wout, OK your revised query worked (apart from minor error in line 1). BUT - and this is interesting - I thought I had this one licked. However I just ran Wout's suggested statement natively in SQL Manager and it produced results very quick. I tried a different parameter (different report) and it also worked well. I then tried to run it from code - which actually calls a SP that contains the query as Wout suggested... and ALAS it takes forever... I tried the original statement in SQL (not from SP) and it works very quick as well. I then called the SP from SQL manager using "EXEC sp_...." and it also took forever. It would appear that calling this transaction from a SP dramatically slows it down. Why is this? Any other good suggestions?

Thanks Wout, I will try your advice. The indexes was the first thing I checked. There are indexes on both tables for all related fields - I was hoping for a quick fix there, alas my predecessor had already tried that. I have now run through the code and it is definitely this stored proc that causes the problem. I am going to replace it with your statement and see what happens. regards Holger

modified on Wednesday, December 9, 2009 9:37 AM

Hi All, Thanks for those who take an interest in reading posts like these - and I sincerely hope that at least one of you can help me. I am fairly new at SQL (without formal trianing...) so this might be very noob. I have inherited the maintenance of a oldish software project. I am only getting to know the code and there is lot of it, so I feel a bit desperate. OK OK I'll get to the point! My client needs me to solve a problem ASAP. They send out mail merge letters frm the application and they have a very sophisticated piece of code that allow you to filter the client database according to many criteria. When the criteria is entered it generates an integer (iParam below) that is used to filter a few tables. We use a autogenerated SQL statement for every report to populate it. The SP looks like this:

@iParam varchar(100) AS
SELECT
ipkClientsID AS [ID],
tblClients.sClientName AS [Client: Client Name],
(SELECT CONVERT(varchar(8000), sTextValue) FROM tblReportSummary WHERE ifkID = tblClients.ipkClientsID AND iParam = @iParam AND sFieldName = 'Client: Postal Address Group (R)') AS [Client: Postal Address Group (R)] FROM tblClients

WHERE ipkClientsID IN (SELECT ifkSelectedItemsID from tblSelectedItems where sID = @iParam)

as you can see the select statement is quite simple BUT for some reason the performance has gradually deteriorated over time to the point where every report that runs, times out or crashes the application (?). I am hoping that you can take one look at it and say: That won't work well - try THIS!, as it might be a simple way of improving the select statement. Much obliged!!! for any help or comments. regards Hawk