Re: Data access perfomance

Tech-Archive recommends: Fix windows errors by optimizing your registry

Eduardo Fonseca B. (EduardoFonsecaB@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> Hi, thanks I kind reduce the time inserting into the database, the problem
> was that I had a primary key on the table,
> then I drop the keys and created a non clustered index, the insertion
> reduce from 20 minute to 10, and into a document that lasted 11 hours the
> time reduce to 5,
> but this had one miliion registers.
> Still I believe It's slow.
> The insert statemente looks like
> @ARE THE COMMAND REPLACED BY MY LOGIC@
> ? are the order parameters sent to the OledbParameters
>
> INSERT INTO @TABLE_NAME@(COLUMN_NAME1, COLUMN_NAME2...)
> VALUES( ?, ?...)

So far so good. You sould also use the same command object for the
same table, and only refresh the parameter values from call to call.
If you build a new command object for each row you insert, you are losing
a lot of time.

> I better don't create stored procedures, my idea with this kind of
> applications is that the database structure should not be modified, but
> thanks anyway.

A noble goal, but unfortunately it can come with a cost in performance.

> What does exactly do the Prepared() method?.

By preparing the query, and just altering the parameters, the command
is parsed and ready to use. When you do something like inserting a
million rows, this can be a big time saver. Again, this presumes that
you reuse your command object.

The exact effect of Prepare depends on the DBMS and the OLE DB provider.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

.

Relevant Pages

  • question about how to handle if row exists
    ... Since this is not supported in SQLCE ... what is the recommended way of inserting new rows in the database with a ... customer_id is a primary key ...
    (microsoft.public.dotnet.framework.compactframework)
  • question about how to handle if row exists
    ... Since this is not supported in SQLCE ... what is the recommended way of inserting new rows in the database with a ... customer_id is a primary key ...
    (microsoft.public.sqlserver.ce)
  • FormView & SqlDataSource
    ... Is there a way to handle when you're on insert mode on a formview and the ... record you're about to save to the database already exist and insted you ... rather update instead of inserting and getting an error of primary key? ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Requery "Too Soon"?
    ... "The database has been placed in a state by user ... then the Requery will return the just ... MsgBox appears, I get an empty subform, i.e., I do ... I'll try inserting a DoEvents or two. ...
    (microsoft.public.access.formscoding)
  • Re: SQLCE performance from .NET CF v2.0
    ... Please remember when bulk inserting is being executed against SQL CE, ... > local database functionality. ... > database technology and I am considering switching to SQLCE at the same ... > execute the prepared statement again. ...
    (microsoft.public.dotnet.framework.compactframework)