[RESOLVED] insert select statement performance

[brin351]

sql server 2005

I'm not sure the best way to go about this, your suggestions are appreciated.

I need to get matching records between 2 tables and insert the results into a 3rd table. These 2 tables are not alike except for a couple of fileds.

I have a select statement that does a CROSS JOIN on 2 tables then I need to insert the results into a 3rd table. The primaryKeys of the first 2 tables form the composite primary key on the 3rd table. Before inserting into the 3rd table I need to remove any records in the select statement that already exist in the 3rd table.

i.e.

INSERT table3
SELECT key1, key2
FROM table1 CROSS JOIN table2
WHERE table1.xx = 'true'
AND table2.xx = 'false'
AND table1.yy > table2.yy
AND NOT EXISTS(SELECT key1 FROM table3 WHERE key1 = table1.key1AND key2 = table2.key2)

Now this is a heafty query with the cross join and exists. Does the exists statement execute every time or only if the preceeding where statements succeed.
OR is there a better way to write this type of query.

[szlamany]

Show some data - although you described your needs well, without data it's impossible for me to visualize what you have.

Show some data from the two tables and what you expect to be the results inserted into the third table.

[brin351]

Thanks for your response here is example data

table1 key1 -> table3 key1, key2 <- table2 key2
table 3 holds keys of table1 & 2

table1
key1 - xx - yy - other columns
1 - true - 20
2 - false - 10
3 - true - 30

table2
key2 - xx - yy - other columns
a - false - 5
b - false - 10
c - true - 8

table3
key1 - key2 - emailed
1 - a - false
3 - a - false
1 - b - false
3 - b - false

So according to the where clause table1 record with key1 values(1 & 3) would match table2 record with key2 value(a) so 1 & a, 3 & a get inserted into table 3. The same happens for records with key1(1 & 3) and key2(b)

Now the next time this query is run the same matches will occur along with new records inserted in table1 & 2 so I have to remove or stop the existing matches before inserting key1 & key2 in table3.

The logic is matching a seller with a buyer (once a day) so a buyer will match multipul sellers and vice versa and we need a record of previous matchs so we don't keep matching/emailing the same ones every time.

I've thought of having a comma delimited list of key1's in table2 to do away with table 3 but this has it's own issues OR right join somehow? accors table1, table3, table2 instead of cross join table1,2 and testing exists on table3.

I'm estimating a future max 10, 000 records produced buy the cross join after the where clause knocks out most. but then each one is tested exists in table3

[randem]

I would think that you need to do a select on the firt two tables to get the records you need to insert into the third table then do an OUTER JOIN on the third table to find the records that already exist.

Hopefully you have unique record identifiers so that when you do the join you can take that information to do a delete on the third table then do an insert.

Ideally for speed, you should have an active indicator on the third table and just mark them as deleted before inserting the new records, as long as you do not have a primary key on the table.

[brin351]

I cant do a delete on the 3rd table because its used in several places for buyers, seller to look up existing matches or why delete before insert if it's already there. My first post shows my my current query that works. Im doing a select with cross join table 1 to 2 then filtering that. Your saying I could do an outer join on table 3 instead of testing EXISTS - would that be better? I'd have to do testing - it's about time to dump heaps of records in and start that but I'm only intermediate on DB performance etc so I'm guessing.

[randem]

You have indexes on these tables, correct?

[brin351]

Only one primary key in table 1 and table 2 . tables 3 has a composite PK of key1 and key2 from tbl1 & 2 respectively with a forign key relation to each. I can add more indexs if needed or changed everything if there is a much better way to record matches between 2 tables with simular columns.

Thanks

[szlamany]

First - from BOL

However, if a WHERE clause is added, the cross join behaves as an inner join

So it's just a simple join really.

Table1 records only where xx is true joined to Table2 records only where xx is false - and of course the yy>yy part.

Exists() is not a fast call - it's evaluated row-by-row - so it's far from a natural set-based construct.

Since you are in SQL 2005 - and stored procedures are available - I would create a TEMP TABLE in a SPROC. That TEMP TABLE having the columns of key1 and key2. Do an INSERT into that table just like you are now - but lose the CROSS JOIN since it's not needed.

Then either set a flag in that TEMP TABLE about the row already existing or DELETE the row from the TEMP TABLE that already exists.

Delete From #TempTable
   From #TempTable TT
   Join Table3 T3 on T3.Key1+T3.Key2=TT.Key1+TT.Key2

Now you are left with the rows that need to be inserted only.

You could even put a PK on the temp table - but I think it would have no benefit.

All this can be tested in a query window and execution plans reviewed.

[brin351]

Thanks szlamany

I have implement your suggestion and it works well. It makes sence that deleting rows from the temp table is better than the exists statement and I found an appropiate column to join T1 & T2 on so its all round better than what I had.