SQL Server Performance Forum – Threads Archive

query taking infinite time to execute

Hi, I am trying to execute the following query and getting no result even waiting for 20 minutes. UPDATE table1 SET tbl1.fld1=tbl2.fld1,tbl1.fld2=tbl2.fld2 FROM table1 tbl1 WITH (NOLOCK) INNER LOOP JOIN table2 tbl2 WITH (NOLOCK) ON REPLACE(tbl2.fld1,’-‘,”)=tbl1.fld1 Here tbl1.fld1 is Primary key. tbl2.fld1 is indexed. tbl2.fld1 have same data as tbl1.fld1 but on have additional ‘-‘ in string, e.g; tbl1.fld1=’ABVCD' tbl2.fld1=’A-BV-C-D' Here is my system information OS Win2K3-STD SQL2K5-Enterprise RAID10- OS/DB/TEMPDB RAID10- User Log RAID5(10 drives,1 hotspare,10kprm,146GB) – User datafiles DB tablename row_count column_count size tabl1 4514096 7 4179488 KB tabl2 6798450 4 369992 KB Database Engine Tuning Advisor Tried to check if I can optimize query, and I got following error [Microsoft][SQL Native Client][SQL Server]Invalid object name ‘tabl1′. Please advise, where is have done mistake [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />]?

I would split this up, drop the join hint, and also the table hint. Hints should be used only if they solve very specific problems. Assuming you must lookup fld2 before fld1 is updated, you start with the key intact: UPDATE table1
SET tbl1.fld2 =
(SELECT tbl2.fld2 FROM table2 tbl2 WHERE REPLACE(tbl2.fld1,’-‘,”)=tbl1.fld1) Then you update the key field: UPDATE table1
SET tbl1.fld1 = tbl2.fld1
FROM table1 tbl1 INNER JOIN table2 tbl2
ON REPLACE(tbl2.fld1,’-‘,”)=tbl1.fld1

sorry, i make a mistake in query. Query is like this UPDATE table1
SET tbl1.fld2=tbl2.fld2,tbl1.fld3=tbl2.fld3
FROM table1 tbl1
INNER JOIN
table2 tbl2
ON
REPLACE(tbl2.fld1,’-‘,”)=tbl1.fld1 When i execute this query with top 100000 records it took about 2 minutues. Does it mean it will take long time to execute the above query?

Just make sure you update the key column in a separate query. The other two columns can be done together. – It may help if you start with a separate update on the key column only, then a second update query for the other columns, where you can do a simple join. … but why do you have one key column with a hyphen in the data, and a corresponding one without a hyphen in the data? Problem #1 There could be all kinds of possible mismatches! Because of the routine that removes the ‘-‘ from the other key, ‘abcd12345’ would match all of these values: ‘a-bcd12345’, ‘ab-cd12345’, etc. etc.
‘a-b-cd12345’, ‘ab-c-d12345’, etc. etc.
‘a-b-c-d12345’, ‘ab-c-d-12345’, etc. etc.
‘a-b-c-d-12345’, ‘ab-c-d-1-2345’, etc. etc.
etc. etc. … and SQL ends up updating the same row for each and every match that is found. Updates in and by themselves are probably the most time-consuming data-manipulation actions, and it only gets worse when unnecessary updates are done. I hope you get the picture. Problem #2 If there is only one match, then how come the data is different? You should review the data structure, and how it is decided in the real world what the key value should be.

Did you use proper indices?
Post table structures Madhivanan Failing to plan is Planning to fail

Hi, First to Adriaan, I do understand your reasoning for why to create same field in 2 different tables with different information.
Unfortunatly i have taken over dirty work of our vendor (who start pressing my existing company with huge quotes on a small change). 1-
code "A-B-CDE-FF" is real code of items and not sure why vendor use second column as "ABCDEFF" for searching purspose (i was not in the company when this whole thing was developed, so don’t ask me why).
2-
single code can exist in system only one time there cannot be same code appearing twice (as location of "-" with the item code is always same as i mentioned above). so it cannot be possible that 2 items can have same code. so i am quite safe in the sense system will not update same item more than 1 time. Now Madhivanan, here is structure of my table ********************************************************
table1
CREATE TABLE [dbo].[table1](
[fld1] [char](13) NOT NULL,
[fld2] [char](16) NOT NULL,
[fld3] [char](1) NULL,
[fld4] [char](2) NULL,
[fld5] [text] NULL,
[fld6] [char](6) NULL,
[fld7] [char](5) NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[fld1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO CREATE NONCLUSTERED INDEX [IX_table1_1] ON [dbo].[table1]
(
[fld6] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO CREATE NONCLUSTERED INDEX [IX_table1_2] ON [dbo].[table1]
(
[fld7] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO table2
CREATE TABLE [dbo].[table2](
[fld1] [char](16) NULL,
[fld2] [char](10) NULL,
[fld3] [char](10) NULL,
[fld4] [char](10) NULL
) ON [PRIMARY]
END
GO CREATE NONCLUSTERED INDEX [IX_table2] ON [dbo].[table2]
(
[fld1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
******************************************************** As you can see fld1 in both tables are indexed.
Here is am showing structure of my entire table, and my query will look like UPDATE table1
SET fld6=tbl2.fld2,fld7=tbl2.fld3
FROM
table1 tbl1
INNER JOIN
table2 tbl2
ON
REPLACE(tbl2.fld1,’-‘,”)=tbl1.fld1 I let this above script on at my computer yesterday night and this morning when i came to office it was over (execution finish in about 7:30 hours). But i havn’t execute this process at server (as server is busy to process some data from last 2 days…). Any comment for above information? ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

Another idea – since you do have proper indexes on both ends – is to create a proper table that lists the fld1 values along with the processed ones. CREATE TABLE #t (oldkey varchar(16) PRIMARY KEY, newkey varchar(16))
CREATE INDEX t_newkey ON #t (newkey) INSERT INTO #T
SELECT t.fld1, REPLACE(t.fld1, ‘-‘, ”)
FROM dbo.tbl2 t
GROUP BY t.fld1 Then run the updates joining the two tables through #t. You can try testing with a smaller number of rows to see if it’s any different.

Try: UPDATE tbl1
SET tbl1.fld2=tbl2.fld2,tbl1.fld3=tbl2.fld3
FROM table2 tbl2
INNER JOIN
table1 tbl1
ON
REPLACE(tbl2.fld1,’-‘,”)=tbl1.fld1
option (force order)

]]>