SQL query optimization

[User 4645056]

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

[WoutL]

You could try

SELECT c.ipkClientsID AS [ID], c.tblClients.sClientName AS [Client: Client Name],
CONVERT(varchar(8000), sTextValue) AS [Client: Postal Address Group (R)]
FROM tblClients c
Left join tblReportSummary r on r.ifkID = c.ipkClientsID
AND r.iParam = @iParam
AND r.sFieldName = 'Client: Postal Address Group (R)'
WHERE c.ipkClientsID IN
(
SELECT ifkSelectedItemsID
from tblSelectedItems where sID = @iParam
)

But I think you can best look at the Indexes on the tables.

Wout Louwers

[User 4645056]

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

[User 4645056]

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?

[_Damian S_]

If the table tblSelectedItems is quite large, you would be better off linking it with an inner join, like this: replace this bit:

Member 4648370 wrote:

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

With this: inner join tblSelectedItems on ((c.ipkClientsID = ifkSelectedItemsID) and (sID = @iParam))

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[WoutL]

Could you look at the differences between the execution plans of the query and the SP?

Wout Louwers