SQL Server Performance

query taking infinite time to execute

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by waqar, Apr 5, 2006.

  1. waqar Member

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

    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
  3. waqar Member

    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?
  4. Adriaan New Member

    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.
  5. Madhivanan Moderator

    Did you use proper indices?
    Post table structures

    Madhivanan

    Failing to plan is Planning to fail
  6. waqar Member

    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. *~
  7. Adriaan New Member

    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.
  8. mmarovic Active Member

    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)

Share This Page