question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Replace Temp table in Stored Proc

I originally had a sproc that used a view to bring back some of it's data as the sproc has a where clause on a concatenated field and at the time I felt the most efficient way of doing this was creating the concatenated field in the view and joining that to original tables and using the concatenated field in the view for the where clause. I then had to add more tables into my view and the time it took became unusable. At the starting point it was 9 sec and I needed to get it to c1 sec. I used the execution plan, created indexes and got it down to 4 seconds. I took the view out and in the sproc I put the data I needed in a temp table putting the where clause in the insert statement of the table, thus creating a table with much fewer rows. This got the execution time down to 2 secs which I was OK with. However, I have now found that although the sproc works perfectly in SQL our app doesn't like it so need to take the temp table out. I created a table outside the sproc and just populated it in the sproc and the app still does not like it. Can anyone please advise me on what I can do to get the data I need out, and have an acceptable execution time. I am on SQL 2000 so can't use CTE unfortunately.
sql-server-2000performance-tuningtemporary-table
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

if the sp works fine in SQL, then yes it's up to them to prove what the issue is.....
3 Likes 3 ·
what does the app not like?
1 Like 1 ·
The software guys haven't told me just that they 'could' investigate it if there was absolutely no way round it. I thought I could spend a shortwhile investigating and if there isn't an alternative then throw it back at them. Thanks
0 Likes 0 ·
Current script and errors from app dev team, then we can get cracking!
0 Likes 0 ·
This is the error message in the app "cannot change the activeconnection property of a recordset object which has a command object as its source" I think it's written in C#
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
That error used to happen when sp returns more recordsets than the code is expecting, or if the rowcounts causes an issue. Maybe try SET NOCOUNT ON at the start of the sp
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Absolutely . . . from now on.
3 Likes 3 ·
Thank you - of course you always SET NOCOUNT ON don't you?
1 Like 1 ·
You are truly a genius. Many thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.