• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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...
0
wasabi3689
Asked:
wasabi3689
  • 8
  • 6
  • 2
  • +2
3 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now