Query very slow - help

[inarobis]

Hello guys,

I have a query that works fine but it it really really slow. Could someone help to make this operation a little bit more viable.


The table1 is identical and more up to date than table2, so it consists to check if a date exists in the table2 and if the date doesn't exist table2, it needs to update this table from table1.


IF EXISTS (
SELECT *
FROM table1
WHERE table1.sc = 7 and table1.code = 101 and table1.date not in (
SELECT table2.date
FROM table2
WHERE table2.se = 7 and table2.code = 101 ))


INSERT INTO table2 (CODE, QQ, QS, DATE, VALUE, SC)
SELECT CODE, QQ, QS, DATE, VALUE, SC
FROM table1
WHERE table1.sc = 7 and table1.code = @Code
and table1.date not in (
SELECT table2.date
FROM table2
WHERE table2.sc = 7 and table2.code = @Code)



ELSE

print 'not insert'


I have 2000 rows and it is not too much but 1 operation (every Loop) pro second it is a little slow :O

any suggestions?
Ina

 

[arrowhouse]

How about using a derived table to do an outer join & look for the null rather than a not in:

INSERT INTO table2 (CODE, QQ, QS, DATE, VALUE, SC)
SELECT table1.CODE, table1.QQ, table1.QS, table1.DATE, VALUE, SC
FROM table1
LEFT OUTER JOIN
(
SELECT DISTINCT table2.sc ,
table2.code , table2.date
FROM table2
WHERE table2.sc = 7 and table2.code = @Code
) has_row
ON table1.sc = has_row.sc
AND table1.code = has_row.code
WHERE table1.sc = 7 and table1.code = @Code
AND has_row.sc IS NULL

Also is it really necessary to check that some rows exist before attempting to do this? If there aren't any no rows will be inserted. If you do need to do the check then also change its sql.

As always pls check the code before running as don't have your db to check on.

 

[inarobis]

Thank you for this help. I will be trying it.

Every week I need to update this table from informations coming from a xml file that i transform in a temp table.

So there is the checks for "insert into" and for "update"
as I need to do an update of rows whose value is different from table1 but date exists in both tables.


Thanks again
Ina

 

[SQLSister]

arrowhouse is right, the if exists is just extra unneeded processing. If no records meet the criteria, none will e inserted. Also, remember if you are doing an update to existing records and then an insert of new records, always do the update first otherwise it will update the records you just inserted causing extra processing. In using arrowhouse's code, only use the distinct key word if the data may have duplicates, otherwise it is not needed and will slow down the process.

Additionally, you might examine your indexes to see if they are causing the problem. Unecessary indexes on the table being inserted to can cause slowness (do not delete necessary indexes or you will slow down the selct queries on this table) or lack of indexes on the table being inserted from could cause slowness. If you aren't familiar with indexes, please read up on them in Books Online before attempting to adjust them.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[inarobis]

I have some problem with this query as it doesn't return any difference between table1 and table2 even if table2 is less upto date than table1

ina

 

[inarobis]

thanks guys Maybe I need to read some books and documentations because I am not familiar with indexes in tables and do not kwon if the tables in system I am working on are indexed.


Thanks

 

[SQLSister]

This query will only insert records which do not currently exist. If you want to update based on differences, you will need to write one or more update queries.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[arrowhouse]

Can you just check your value of @code? If you are still using your exsits statement can you check that it is set to 101 as per your IF EXISTS statement?

Have had a re-read of the statement I posted & can't see anything out, but its always easier to see issues with other peoples code!

SQLSister is right. This code is only going to add rows if a row doesnt already exist. If you are looking to update the rows based on a change in the other table you would need other statements.

What I think you were saying though is that there were rows in table1 that aren't in table2. Is that correct?

 

[inarobis]

Thank Arrowhouse and SQLSister for this help very cool because I could understant my mistake.

I read some topics about indexes and now my query is very fast.

Now i update and after i insert missing rows as SQLsister proposed to me and it works fine.

I have question when I do left join I do not see any row from this query? and table1 have repeated values and table2 it is correct, why?

Thanks

Ina