Solved

indexed table vs unindexed table

Posted on 2016-10-03
19
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +2
19 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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 70

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 33

Expert Comment

by:ste5an
ID: 41826825
What does "'huge" means? 10 million rows?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

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



how "Go 1000" before insertion?
0
 
LVL 70

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 70

Expert Comment

by:Éric Moreau
ID: 41827156
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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
 
LVL 70

Expert Comment

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

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 70

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 70

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 70

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:Scott Pletcher
ID: 41850470
With all those comments, you pick your own final comment as deserving of all the points?  Seriously??
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

740 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