November 12, 2003 at 10:14 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 4:09 am
I think you should look at removing all the OR statements. In general, an OR is bad for performance.
Additionally, I would have a look at changing the IN statements in the inner queries to normal joins.
...
WHERE XTELELINK IN
(SELECT T.XTELELINK
FROM TESTSQLVW T
INNER JOIN
DONTDIAL.dbo.DONTDIAL D
ON T.Phone = D.Phone)
OR ...
Other things to speed it up is moving the DONTDIAL table inside the same database.
And maybe, just maybe, make a view UNIONing all of the phonefields from XTELELINK together. This would facilitate removing the OR's a great deal. If you make it an indexed view, performance shouldn't be to bad.
November 13, 2003 at 5:07 am
Try this
Update TELESCRP
set xpriority = 99, xcallback = NULL
from TELESCRP , TESTSQLVW , DONTDIAL.dbo.DontDial,
where
TELESCRP.XTELELINK = TESTSQLVW.XTELELINK
and
DONTDIAL.dbo.DontDial.Phone
in ( phone, phone1, phone2, phone21, phone3 , phonenum )
I suggest if you can check / post the plans for both statements. If there is a index on
DONTDIAL.dbo.DontDial.Phone then it should use it.
November 13, 2003 at 5:13 am
small correction
Update TELESCRP
set xpriority = 99, xcallback = NULL
from TELESCRP , TESTSQLVW , DONTDIAL.dbo.DontDial,
where
TELESCRP.XTELELINK = TESTSQLVW.XTELELINK
and
DONTDIAL.dbo.DontDial.Phone
in ( TESTSQLVW.phone, TESTSQLVW.phone1, TESTSQLVW.phone2, TESTSQLVW.phone21, TESTSQLVW.phone3 , TESTSQLVW.phonenum )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply