indexed table vs unindexed table

I have a table. I want to insert a huge records into it. For fast performance, I want to know if I should set up an index, is better or completely no index is better?

How to have a script to commit 1000 records each insert from the folowing script format

insert into...

select....
from...
where...
wasabi3689Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
An index will not speed up the inserts, in fact, it may slow them down.

If you need to index the table, insert the rows first and add the appropriate indexes later.  If you need a clustered index, create the index and insert the rows in the order of the clustered index.
0
Éric MoreauSenior .Net ConsultantCommented:
an index will slow down insert operations because the new records will need to be indexed. updates will also be a bit longer if you update fields part of the index

but indexes will help your select statements if you have a WHERE clause that matches the fields you have indexed. Update and Delete statement can also benefits from indexes if the correct fields are used.

to insert 1000 rows, just add "GO 1000" before your insert.
0
ste5anSenior DeveloperCommented:
What does "'huge" means? 10 million rows?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

wasabi3689Author Commented:
yes, it's a million record table insertion



how "Go 1000" before insertion?
0
Éric MoreauSenior .Net ConsultantCommented:
GO 1000
INSERT INTO ...

Open in new window


The statement following GO will be repeated x number of times
0
wasabi3689Author Commented:
I want the insert statment to insert/commit  every 1000 each time until insert all records end.

I google GO 1000, I don't find how to use it.

LIke below

GO 1000
Insert into....
select....
from...
where....

Does it "GO 1000" can do this purpose?
0
Éric MoreauSenior .Net ConsultantCommented:
0
Scott PletcherSenior DBACommented:
Generally overall performance if you create the clustered index prior to loading, particularly if you can load the table in clus index order.

You should load in fairly large batches, say 50,000 rows.  1,000 rows per batch will generate extra logging because of all the separate transactions.

You can specify:
GO 1000
/*or some other integer number*/

to execute the batch before that GO the number of times you listed.  For example:

PRINT GETDATE()
GO 10

will print the current datetime 10 times.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wasabi3689Author Commented:
I want to know if the following code is equaivalent to GO 1000

DECLARE @BatchSize INT = 10000

      WHILE 1 = 1
      BEGIN
      
      Insert into....
        select....
         from...
          where....


   IF @@ROWCOUNT < @BatchSize BREAK
   
END
0
Éric MoreauSenior .Net ConsultantCommented:
@@ROWCOUNT will always be 1 in your case because it is reset for every loop
0
Éric MoreauSenior .Net ConsultantCommented:
I just made a test, the GO goes after the statement to repeat. Try it with this:
SELECT GETDATE()
GO 10

Open in new window

0
wasabi3689Author Commented:
what do you mean "@@ROWCOUNT will always be 1 in your case because it is reset for every loop"?

How to fix it?

Your code
SELECT GETDATE()
GO 10

will be equal to my code in funtionanity?
0
Éric MoreauSenior .Net ConsultantCommented:
if you mean to use the system variable @@rowcount after the insert statement in a loop, after each insert statement the value of @@rowcount will be 1 because it is returning the number of rows affected by the last statement (your insert in your case) and not the total number of rows into your table. Check https://msdn.microsoft.com/en-us/library/ms187316.aspx?f=255&MSPPError=-2147217396
0
wasabi3689Author Commented:
If I want to insert total number of rows into my table,

what @@ROWCOUNT  value will be?
0
Éric MoreauSenior .Net ConsultantCommented:
the number of rows you have inserted
0
wasabi3689Author Commented:
I want to insert all rows into a table. I don't know exact how many rows
0
Éric MoreauSenior .Net ConsultantCommented:
You are really hard to follow. Can we get the full picture of what you are trying to do? What do you currently have in the table you are planning to select from?

if you do this:
insert into tableA...
select....
from tableB...
where...

Open in new window


here, @@rowcount would be the number of rows inserted which is the total number of rows you have returned by the select from tableB query.

All the content of TableB will be inserted into tableA.

If you want only the 1000 first records (because you are saying something about 1000 records in your question), you can use this:
insert into tableA...
select TOP 1000 ....
from tableB...
where...

Open in new window


here, @@rowcount would be the number of rows inserted which is 1000
0
Scott PletcherSenior DBACommented:
With all those comments, you pick your own final comment as deserving of all the points?  Seriously??
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.