indexed table vs unindexed table

wasabi3689
wasabi3689 used Ask the Experts™
on
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...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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.
ste5anSenior Developer

Commented:
What does "'huge" means? 10 million rows?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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



how "Go 1000" before insertion?
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
GO 1000
INSERT INTO ...

Open in new window


The statement following GO will be repeated x number of times

Author

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?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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.

Author

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
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
@@ROWCOUNT will always be 1 in your case because it is reset for every loop
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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

Author

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?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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

Author

Commented:
If I want to insert total number of rows into my table,

what @@ROWCOUNT  value will be?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
the number of rows you have inserted

Author

Commented:
I want to insert all rows into a table. I don't know exact how many rows
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
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
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
With all those comments, you pick your own final comment as deserving of all the points?  Seriously??

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial