Parameterized Query unexplainably slow

[michael.mann]

Hi,

I came across a very weird problem. I get a statement from a file, dump
the text into a sql command, add a sql parameter and run the query.
This lasts about 1 1/2 hours, compared to 2-3 minutes running the same
statement from within the sql query analyzer.

To get around this problem I added the following lines INTO the
statement file:
declare @param int
set @param = 20060411

using this, the statement lasts 2-3 minutes.

When using s sql parameter with the sql command the same statement
needs 1 1/2 hours. I added the parameter as follows:

cmd.Parameters.Add("@FromDate_SID", date.ToString(("yyyyMMdd")));
or
cmd.Parameters.Add("@FromDate_SID",
Convert.ToInt(date.ToString(("yyyyMMdd"))));

Both result in a duration of 1 1/2 hours.

Does anyone can give me a clue on this? I'm lost, and I am running out
of ideas.

Thanks in advance
Michael

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

What is the type of the parameter?
Why are you converting it to a string with such a uncommon format?
By the name of the field it seems that it's a date, not a string,
OTOH, you declare @param as an Int

I think you have some issues with the type of the data

post your query and the type of the column you compare with the parameter
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
<mi**********@dws.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

Hi,

I came across a very weird problem. I get a statement from a file, dump
the text into a sql command, add a sql parameter and run the query.
This lasts about 1 1/2 hours, compared to 2-3 minutes running the same
statement from within the sql query analyzer.

To get around this problem I added the following lines INTO the
statement file:
declare @param int
set @param = 20060411

using this, the statement lasts 2-3 minutes.

When using s sql parameter with the sql command the same statement
needs 1 1/2 hours. I added the parameter as follows:

cmd.Parameters.Add("@FromDate_SID", date.ToString(("yyyyMMdd")));
or
cmd.Parameters.Add("@FromDate_SID",
Convert.ToInt(date.ToString(("yyyyMMdd"))));

Both result in a duration of 1 1/2 hours.

Does anyone can give me a clue on this? I'm lost, and I am running out
of ideas.

Thanks in advance
Michael