Re: adVarWChar slower than adVarChar

Well, now we get the special characters properly imported into the table.
But the importing time for one file containing some thousand lines
increased from 8 minutes to more than 8 hours!!!

Have you profiled this? Is it really the case that the 1 call of the SP via
the Command object is what takes 8 hours? Nothing else? That is where the
time goes. And the Command object persists between each call does it?
Also how many parameters does this Command object have, direction and type
of them? Just the 1 you showed us?
Also - version of MDAC are you using?

Any ideas?

It is a pure guess. Nothing more than that. I am guessing that despite what
you showed us, ADO is having to do some translation internally before
sending the parameter. And to do that it might be allocating and
deallocating a buffer to do that. And that takes time (but it is hard to
account for 8 minutes -> 8 hours).

Point 1:
I would follow this procedure:
(i) comment out the setting up of the Parameters and replace with
cmdADO.Parameters.Refresh
and see if that is faster. Let ADO setup the parameters
(ii) If it is faster, dump the Parameters : type, direction, other
properties to file/screen and this time setup your parameters manually using
the same values just dumped. Comment out cmdADO.Parameters.Refresh, it has
done its job. The only thing to change is that ADO uses direction
adParamInputOutput when it may just be adParamInput. Adjust appropriately.

Point 2:
Are you calling this SP right? It does not return any recordsets, right?
Many VB programmers should be shot through the head because they allow the
defaults (a _MASSIVE_ mistake on RecordSet.Open()) on important ADO methods.

Assuming that parameters have been set:
cmdADO.Execute ,, adCmdStoredProc OR adExecuteNoRecords

Point 3
And does the SP have "SET NOCOUNT ON" at the top on entry?

I know that if I was doing the programming, my first action would be to find
out where the time is spent and what proportions are on the execution of the
SP. Are you sure it is this and not some ghastly inefficiency in surrounding
code?

Stephen Howe


.

Relevant Pages

  • Re: Stored procedure executes twice
    ... The SET NOCOUNT issue is an unrelated issue to yours (judging by your ... duplicate executions. ... >> Do your stored procedures contain SET NOCOUNT ON? ... >> used ADO); try changing the lock types to see if that helps. ...
    (microsoft.public.sqlserver.programming)
  • Re: ADO Properties
    ... ADO.NET has same objects like Connection, Command object as ADO does. ... there is Dataset in place of Recordset. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Your kidding me right?
    ... Or my book that covers ADO to ADO.NET transitioning. ... Practices for VB Programmers ). ... Microsoft MVP ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Can someone explain why my code is not working
    ... posting to a class ADO group Also, ... am unable to get a return value from a stored procedure in .NET using ... ADO: Add "SET NOCOUNT ON to the body of your procedure, ...
    (microsoft.public.data.ado)
  • Re: Data Environment - any pitfalls?
    ... The DE was introduced with ADO 2.0 as ... beginning programmers jump straight into ADO programming and end up writing ... of the many features that ADO and Data objects can supply to improve the ... survives into the finished product. ...
    (microsoft.public.vb.general.discussion)