November 12, 2003 at 10:12 am
It's a little hard to explain what I am trying to do here. I have 3 tables that I am using in my update query. Two tables are a one to one relationship on the id field, (called xteleilnk). They can have millions of records potentially. The third connects to only one of the other tables via a phone field. This table is in a separate database. I want to update a field in the 2nd table based on data between the 1st and 3rd table. Here is my query as of now, but it really takes a long time. I am trying optimizing my indexes but it doesn't seem to help all that much.
Update TELESCRP set xpriority = 99, xcallback = NULL
where (( XTELELINK IN
(SELECT XTELELINK FROM TESTSQLVW
WHERE Phone in (SELECT PHONE FROM DONTDIAL.dbo.DontDial))
OR XTELELINK IN
(SELECT XTELELINK FROM TESTSQLVW WHERE Phone2 in
(SELECT PHONE FROM DONTDIAL.dbo.DontDial))
OR XTELELINK IN
(SELECT XTELELINK FROM TESTSQLVW WHERE Phone1 in
(SELECT PHONE FROM DONTDIAL.dbo.DontDial))
OR XTELELINK IN
(SELECT XTELELINK FROM TESTSQLVW WHERE Phone21 in
(SELECT PHONE FROM DONTDIAL.dbo.DontDial))
OR XTELELINK IN
(SELECT XTELELINK FROM TESTSQLVW WHERE phone3 in
(SELECT PHONE FROM DONTDIAL.dbo.DontDial))
OR XTELELINK IN
(SELECT XTELELINK FROM TESTSQLVW WHERE phonenum in
(SELECT PHONE FROM DONTDIAL.dbo.DontDial))) )
I am willing to answer any questions and I am open to suggestions. It's really rather complicated. Thank you in advance.
November 13, 2003 at 2:36 am
You could try replacing the WHERE IN with WHERE EXISTS (look it up in BOL) as I read the EXISTS is generally much faster than IN as it uses a binary comparison as opposed to a potential repeated lookup - also try putting a trace on the databases and then use the Index Tuning Wizard to see if your columns are indexed optimally ...
Steve
November 13, 2003 at 3:08 am
Is it the SELECT part of the statement that is taking a long time to run (i.e. the where((XTELELINK IN.....), or is it the actual UPDATEs (i.e. are there so may updates that they will take ages, regardless of the SELECT part)?
Either way, you may find it runs quicker if you do the updates in smaller chunks i.e. one update for each one of the 'OR XTELELINK IN ...'
Also, SQL Server will treat an IN statement as a series of OR statements. OR statements often stop the query optimiser from using an index. You may be better off using 'UPDATE XTELELINK FROM XTELELINK INNER JOIN DONTDIAL....', which may prompt SQL Server to you the index you had expected.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply