Efficiently Inserting 1 Million records

I have an app that needs to insert 1 million records into a table. The tabl
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:

> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan

Efficiency of is null v\s nullif

hi ,
i am querying for data in which i compare nulls . And null = null for my
scenario. Thus i need to either use extra where conditions in the query to
check if the two compared fields are nulls. This i can do either by using is
null or nullif function. Which of the two options will be more efficient for
SQL to execute considering large data scenario?
eg:
Use NULLIF function
where (nullif(col1,col2) is null and nullif(col2,col1) is null
where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
Use IS NULL construct
where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1 <>
col2) or (col1 is null and col2 is not null) or (col1 is not null and col2
is null))prabhakar wrote:
> hi ,
> i am querying for data in which i compare nulls . And null = null for
> my scenario. Thus i need to either use extra where conditions in the
> query to check if the two compared fields are nulls. This i can do
> either by using is null or nullif function. Which of the two options
> will be more efficient for SQL to execute considering large data
> scenario? eg:
> Use NULLIF function
> where (nullif(col1,col2) is null and nullif(col2,col1) is null
> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
>
> Use IS NULL construct
> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1
> <> col2) or (col1 is null and col2 is not null) or (col1 is not null
> and col2 is null))
Not sure I understand. You have two WHERE clauses in each example. Can
you post the real SELECTs you are comparing. You can also check the
execution plans to see which one is cleaner.
David Gugick
Imceda Software
www.imceda.com|||I am comparing two fields in a table to check if they are not equal.This can
be done in following two ways
1) select * from tablefornull where ((col1 <> col2) or (col1 is null and
col2 is not null) or (col1 is not null and col2 is null))
2) select * from tablefornull where not(nullif(col1,col2) is null and
nullif(col2,col1) is null)
which one will be more efficient? The qeury execution plan is same for both
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uJFAyhzEFHA.2572@.tk2msftngp13.phx.gbl...
> prabhakar wrote:
> Not sure I understand. You have two WHERE clauses in each example. Can you
> post the real SELECTs you are comparing. You can also check the execution
> plans to see which one is cleaner.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||prabhakar wrote:
> I am comparing two fields in a table to check if they are not
> equal.This can be done in following two ways
> 1) select * from tablefornull where ((col1 <> col2) or (col1 is
> null and col2 is not null) or (col1 is not null and col2 is null))
> 2) select * from tablefornull where not(nullif(col1,col2) is null
> and nullif(col2,col1) is null)
> which one will be more efficient? The qeury execution plan is same
> for both
If the execution plan is the same, they will run exactly the same. I'm
guessing you are assuming for this exercise that NULL = NULL. Is that
correct?
How about this one:
Select * from tablefornull where isnull(col1, -999) = isnull(col2, -999)
David Gugick
Imceda Software
www.imceda.com|||prabhakar,
The performance difference in the actual processing of an individual row
not differ much between the two syntaxis. What could make a real
difference is using a syntax in a way that the optimizer can use indexes
so only the relevant rows need to be processed. Unfortunately, both
syntaxis will not achieve that.
But why do you want to compare NULLs? NULL is intended to mean something
like "unknown". In any case, the purpose of using NULLs is that one
row's NULL is not (never) equal to another row's NULL, by definition.
That is the definition that the ANSI SQL committee gave it. If you
reject that idea, you could take a look at "SET ANSI_NULLS OFF" in BOL,
and see if that works for you.
If you acknowledge the 'proper' use of NULLs, then you should never use
something like "nullif(col2,col1) is null". If there are some NULLs that
you want to treat as a values, then you could replace them with an
actual value, for example a predefined 'special' value. For varchar
columns this value could be '**Empty'. For int columns this could be
-2,147,483,648, etc.
After that you can use the following query. It is more complex than your
original query, but is likely to perform better.
SELECT *
FROM MyTable T1
WHERE NOT EXISTS (
SELECT 1
FROM MyTable T2
WHERE T2.KeyColumn = T1.KeyColumn
AND T2.Col1=T2.Col2
)
Hope this helps,
Gert-Jan
prabhakar wrote:
> hi ,
> i am querying for data in which i compare nulls . And null = null for my
> scenario. Thus i need to either use extra where conditions in the query to
> check if the two compared fields are nulls. This i can do either by using
is
> null or nullif function. Which of the two options will be more efficient f
or
> SQL to execute considering large data scenario?
> eg:
> Use NULLIF function
> where (nullif(col1,col2) is null and nullif(col2,col1) is null
> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
> Use IS NULL construct
> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1 <>
> col2) or (col1 is null and col2 is not null) or (col1 is not null and col2
> is null))