Solved

indexed table vs unindexed table

Posted on 2016-10-03
19
60 Views
Last Modified: 2016-10-19
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
Comment
Question by:wasabi3689
  • 8
  • 6
  • 2
  • +2
19 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 41826802
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41826811
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41826825
What does "'huge" means? 10 million rows?
0
 

Author Comment

by:wasabi3689
ID: 41826856
yes, it's a million record table insertion



how "Go 1000" before insertion?
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 41826899
GO 1000
INSERT INTO ...

Open in new window


The statement following GO will be repeated x number of times
0
 

Author Comment

by:wasabi3689
ID: 41827143
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41827156
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 41827202
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
 

Author Comment

by:wasabi3689
ID: 41827280
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41827347
@@ROWCOUNT will always be 1 in your case because it is reset for every loop
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41827351
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
 

Author Comment

by:wasabi3689
ID: 41827429
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41827770
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
 

Author Comment

by:wasabi3689
ID: 41828721
If I want to insert total number of rows into my table,

what @@ROWCOUNT  value will be?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41828976
the number of rows you have inserted
0
 

Author Comment

by:wasabi3689
ID: 41829019
I want to insert all rows into a table. I don't know exact how many rows
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 41829602
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41850470
With all those comments, you pick your own final comment as deserving of all the points?  Seriously??
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now