Data Adapter Problem?

[G Dean Blake]

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 Chipman [MSFT]]

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

 

[Val Mazur \(MVP\)]

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.