Ms Sql .Net - timeout

Hello,
I have a stored procedure. When I execute it in Sql Server Management studio it takes less then 1 second to return data.
It returns 21 rows and 13 columns.
When I call this procedure from .Net I get TimeOut, even though I extended the CommandTimout to 60 seconds.
I have a parameter in the stored procedure and when set to true it returns the first 7 columns instead of 13.
The strange thing is that when I only get the first 7 columns I do not get timeout and it takes very short time to load RadGrid with the data. I have no idea why this can happens. I hope someone can help me with this.

Attached is an excel document with the result from the query.  

Greetings
sql_result.xlsx
johnson1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
It sounds like you're suffering from parameter sniffing. Try creating a variable inside your stored procedure, assigning the passed-in value to that internal variable, and running your query based on that variable instead. That will avoid the parameter sniffing issue.

Example... Instead of

CREATE PROC foo
(
  @PassedVariable varchar(50)
)

AS

SELECT
  *
FROM
  [MyTable]
WHERE
  [bar] = @PassedVariable

Open in new window

try
CREATE PROC foo
(
  @PassedVariable varchar(50)
)

AS

DECLARE @InternalVariable varchar(50
SET @InternalVariable = @PassedVariable

SELECT
  *
FROM
  [MyTable]
WHERE
  [bar] = @InternalVariable

Open in new window

I know it seems weird but it DOES work. Look up SQL parameter sniffing for more info.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
The problem is the changing output format. This means that your procedure needs to be recompiled. Force that recompile:

CREATE PROC foo
(
  @PassedVariable varchar(50)
)
WITH RECOMPILE
AS
  SET NOCOUNT ON;
  -- your code.

Open in new window

0
Éric MoreauSenior .Net ConsultantCommented:
just like Russ, I am convinced that your issue is related to parameter sniffing. If you want to read more about this, have a look at http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html
0
johnson1Author Commented:
Thank you very much.  You saved the day.I would never have thought of this:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.