1
\$\begingroup\$

Base Info

  • Two tables: tData, tData2
  • Exactly the same columns
  • About 200,000 records
  • SQL Server 2008 R2

Logic

At first sight we need to insert tData rows into tData2. What else?

We need a renamed version of a column inside another (tData2) with the condition checking it won't be an existing item when it's renamed. Here's the code:

INSERT INTO [tData2]
(
    [Key],
    Info1,
    Info2
)
SELECT 
    REPLACE([Key],'_',' '),
    Info1,
    Info2
FROM
    [tData]
WHERE
    (REPLACE([Key],'_',' ') = [Key]) OR
    (REPLACE([Key],'_',' ') NOT IN (SELECT [Key] FROM [tData]))

The problem is it's really slow for me on a top-notch 64 bit system. It has taken more than an hour so far and it's still executing.

How to speed it up? Any alternatives? Any ideas?

\$\endgroup\$
1
\$\begingroup\$

I'm not a DBA/DBMS guru, so just some ideas:

\$\endgroup\$
  • \$\begingroup\$ Anyway this is a valuable comment, I liked it, Could you please give an example on the last part you mentioned ? \$\endgroup\$ – Sypress Jul 19 '12 at 5:07
  • 1
    \$\begingroup\$ @Sypress: I'm sorry, I've tried to rewrite the query but I gave it up after a few tries. I haven't used so complex SQL queries recently. Please let me know how went the migration and what worked or not. \$\endgroup\$ – palacsint Jul 19 '12 at 9:37
  • 1
    \$\begingroup\$ no problem friend, thanks again, your suggestions could be useful to me. \$\endgroup\$ – Sypress Jul 19 '12 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.