Will a clustered index speed up this query? (Inner join with a where clause)

indexinsertsqlite

I'm inserting data into a new table in a new database, based on a select to an existing table in an other database. The existing table has some 1500 million rows, and I am inner joining a table with about 10% of that. Both existing tables, are indexed on C5, while the the new table is indexed on C2, which is an unixtime column (I am going to do aggregates over time with the new table). This is taking horrendously long time. Its been running for over 3 hours, and the new table is just over 750 mb big, I expect it to be about 50GB when its filled up. This is also about 50% of the original table.

Is it because I'm indexing the new unixtime column?

Or could it be that I've not indexed the C8 column, that I use in my inner join? If I clustered C8 with C5, I'd probably get a faster inner join?

Here is the query:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7) 
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7 
FROM OldTable1 
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5 
WHERE OldTable2.C8 >= 70 AND OldTable2.C8<= 89;

Best Answer

You are totally trashing the index on NewTable.C2
Just drop / disable the index and then build / rebuild it after the insert is complete

And you need to break this up as you are putting a massive load on the transaction log

If you want to optimize the select then just run the select alone

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7) 
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4
     , OldTable1.C5, OldTable1.C6, OldTable1.C7 
FROM OldTable1 
JOIN OldTable2.C5 
  ON OldTable2.C5 = OldTable1.C5 
 AND OldTable2.C8 >= 70 
 AND OldTable2.C8 <= 89;

Related Solutions

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Sql-server – How to speed up query on table with millions of rows

On reason this can happen is that you're using local variables.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns.

Here's an example using a similar setup. In the Stack Overflow schema there's a narrow-ish table called Votes that looks like this.

With no index on CreationDate, our only option would be to scan the Clustered Index. But if we create one only on CreationDate, the optimizer can choose to use that index if it thinks doing a Key Lookup for the rest of the columns is cheaper than scanning the Clustered Index and applying a predicate.

CREATE INDEX ix_yourmom ON dbo.Votes(CreationDate)

For this query:

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate;
GO

The cardinality estimate for unknown variables using between is 16.4317%. That leads to a clustered index scan and a missing index request for an index that covers the entire query.

If you run the query with RECOMPILE, you allow for the parameter embedding optimization.

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate
OPTION ( RECOMPILE );

Which gives us a different query plan, and a more accurate estimate.

Hope this helps!

Best Answer

Related Solutions

Sql-server – Combine – Select Into, with Insert Into Select

Sql-server – How to speed up query on table with millions of rows

Related Question