This is Wierd! (Works in Query manager Fails in Code)

there is one known bug in .net's sql parameters that you might be hitting - possibly.

can that int ever be zero?

in cases where a zero is passed it cam be mis-applied as a different parameter.

like data size or data type.

i have a place in one of my apps that has a work around where it has hit me.  but it takes a very specific thing to trigger it.

my other question would be to run the query and get the time it takes to execute if it takes more than about 10 seconds then i would use query analyzer to find out why.

i suspect you may have some perf issues in there that should be addressed.

i have seen a first pass at a query take 30 seconds and then an index drop the same query to less than one second.

generaly any query that needs more than 5 seconds to run in query analyzer would be suspect to me.

there is a bug in the parameters object that can mis-read a zero int value in some cases. i do not think you are getting that bug but just in case i felt it was worth mentioning.

in my case i had a param that had to be zero, and when i used an actual 0 in the parameter list it would freak out.

i had to create a const int Zero = 0;

and use that as my parameter. 

I would use query ana. and find out what's eating up the time....  adding an index or a view as needed... or re-writing some of the sql possibly... i'd have to have a sample to test with to know for sure.

but whats the execution time in QA ? 

what does the plan show for what branches take the most % of the total time?

are the major points getting an Index Seek? Index Scan? or other?

also try making a sproc out of it and see what happens if you call that in QA and from .net

Ya know that does look odd, i never did it that way.... looking at one of my code blocks i have done it 2 ways and nither look like his.

here is one way i have done that works unless it's a zero:

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@FranchiseID", FranchiseID));

that is one way...

another is that i would create each param and then make the collection so that i could do a loop where the params chnaged with each pass but no creating objects inside the loop unless i had to.

like :

parameter p1,p2,p3;

params.add(p1)

for (...){

p1=somevalue;

cmd.exec;

... and so on.

}

also true....

all my recent sql code (in the last 2 /3 years)  uses using(){} 

in fact it generally is a set of them:

using ( sql connection ....){

  .Open();

     using ( sql command ....){

           using ( Sql data reader ...... Cmd.ExecuteReader()){

                while (reader.......){

                }

           }

      }

   .Close();

}

with a try block where it seems best in a given case....

in a V1 code beta  i did way way back on early 2005 that was not doing this i had connection issues.

in a revised version where i used this pattern of the using statement plus other cleanup no connection limit issues.

the first beta version was very very rapidly built to get the project started, we knew it had issues to fix etc...

it used like 260 connections! 

when the connection use was fixed it now never uses maore than the default connection pool limits.

and runs today with about 300 client machines connecting to it.

this may sound odd but...

Use using, it uses Dispose when your using goes out of scope to cleanup and stop using the object in question.

like the Try / Catch / Finally  says that finally will always happen even if a catch is invoked;  a using always does object cleanup when the used object goes out of scope. so you will not have to write code to make sure that the objects close / dispose logic fires.

so use using to close and dispose of any class that impliments the IDisposable interface.

it works and makes it a very clear pattern in the code for others to follow and have correct code.

IMHO anyway

Ok so then under some condtion the query gets a fault.  what is different about the members?

possibly the subquerys fail on select members due to the difference?

might that be the issue?  like it cant find a row in that nested part that starts with "COALESCE(B.DISPDESC,(SELECT TOP"

also what pool? connection pool?

is that a side efect of the query failure? not closing connections clean on the error?

OK, Next idea to figure this out is to run a trace.

I know that parameters and the sql do get sent to the server with some chnages ......

Hey hold on ......   i wonder if @P1 is used by the net to sql processing internally.

try chnaging that to say  @MemberParam  and see what happens  ...  I think when i do a sql trace i see params beeing done as p1 p2 ... p99  in the sql text

might be mistaken but check that.

also like i said use sql trace to capture the traffic from your app and see what .net is actualy sending over the wire.  if it is parsing the sql and doing something like the @p1  thing you will see it.

also you may see an error in the sql reply that was hidden by the .net layer.

see if that might be it though. or what the sql trace tells us.