Data Adapter Problem?

The following is an issue using ado.net in an asp.net page using SQL
Server....

I have a Statement that when executed from my asp.net dataadapter takes
about 24,000 reads according to SQL Profiler. I think the read count is
accurate as it grids a long time.

The same statement when executed from Query analyzer takes only 116 reads
and it executes quickly.

Why the Difference? Same table, same server.

The statement as it executes is as follows:
exec sp_executesql N'SELECT Amount, InvoiceNum, Job FROM Unpaid_Bal
WHERE (InvoiceNum = @InvoiceNum) AND (Job = @JobNum)',
N'@InvoiceNum varchar(50),@JobNum varchar(11)', @InvoiceNum = '100488RS',
@JobNum = 'ABCE-036245'

thanks,
G

Mary
Wed Mar 09 12:58:18 CST 2005

Hard to say without knowing more about the configuration, schema,
indexes, network, etc. However, I would recommend invoking a
parameterized stored procedure over sp_executesql, especially in a web
application where you are more vulnerable to SQL injection attacks.
sp-executesql requires permissions on the base tables, whereas stored
procedures do not.

--Mary

On Wed, 9 Mar 2005 09:43:27 -0800, "G Dean Blake" <gb@nospam.com>
wrote:

>The following is an issue using ado.net in an asp.net page using SQL
>Server....
>
>I have a Statement that when executed from my asp.net dataadapter takes
>about 24,000 reads according to SQL Profiler. I think the read count is
>accurate as it grids a long time.
>
>The same statement when executed from Query analyzer takes only 116 reads
>and it executes quickly.
>
>Why the Difference? Same table, same server.
>
>The statement as it executes is as follows:
>exec sp_executesql N'SELECT Amount, InvoiceNum, Job FROM Unpaid_Bal
>WHERE (InvoiceNum = @InvoiceNum) AND (Job = @JobNum)',
>N'@InvoiceNum varchar(50),@JobNum varchar(11)', @InvoiceNum = '100488RS',
>@JobNum = 'ABCE-036245'
>
>thanks,
>G
>

Val
Wed Mar 09 19:17:06 CST 2005

Hi,

When you check SQL Profiler do you see that your SQL executed only once? If
yes, try to execute your statement without parameters (just hardcode them
for the testing purposes) to see what happens. If it helps, then most likely
problem is with the passing parameters to the SQL statement.
--
Val Mazur
Microsoft MVP

http://xport.mvps.org



"G Dean Blake" <gb@nospam.com> wrote in message
news:%23$Lm%239MJFHA.4028@tk2msftngp13.phx.gbl...
> The following is an issue using ado.net in an asp.net page using SQL
> Server....
>
> I have a Statement that when executed from my asp.net dataadapter takes
> about 24,000 reads according to SQL Profiler. I think the read count is
> accurate as it grids a long time.
>
> The same statement when executed from Query analyzer takes only 116 reads
> and it executes quickly.
>
> Why the Difference? Same table, same server.
>
> The statement as it executes is as follows:
> exec sp_executesql N'SELECT Amount, InvoiceNum, Job FROM Unpaid_Bal
> WHERE (InvoiceNum = @InvoiceNum) AND (Job = @JobNum)',
> N'@InvoiceNum varchar(50),@JobNum varchar(11)', @InvoiceNum = '100488RS',
> @JobNum = 'ABCE-036245'
>
> thanks,
> G
>