0

I am trying to achieve the VLOOKUP function in my SQL server by running a SQL script.

For small example, my tables look like:

TableA

ID Type and a lot other columns
123 A xx
321 B yy
213 C zz

TableB

Number Type and a lot other columns..
321 C dd
213 A ee
123 B rr

I try to VLOOKUP Table A's ID column in Table B's Number Column, and update Table A's Type column value from Table B's column value. The result will be:

Table A

ID Type and a lot other columns..
123 B tt
321 C pp
213 A qq

I have around 100,000 rows in Table A and 2,000 rows in Table B. If I using VLOOKUP in Excel, the whole process will take around less than 1 mins.

I try to achieve same results goal and efficiency in SQL Server by SQL Scripts. Here is my SQL script look like:

SET A.[Type]=B.[Type]
FROM TableA A
LEFT JOIN TableB B
ON A.[ID]=B.[Number]

The whole process run the correct result, but it took 46 mins to run the scripts. May I ask if is there thing wrong with the SQL script? or why it took so long in for SQL Server with SQL script compare with VLOOKUP? Or is there any way to improve the efficiency of my SQL script?

Thanks you very much!

liiiii
  • 11
  • 1
  • 4
  • 1
    Performance is horrible in both cases. You didn't post the table specifications but if it takes 46 minutes, it's obvious that ID and Number aren't indexed. `VLOOKUP` is a lot slower than a SQL query with proper indexing – Panagiotis Kanavos Jan 05 '21 at 20:06
  • 1
    Sounds like a lack of indexes and/or a very poorly resources server. Posting the query plan (use Paste the Plan) will help us help you. – Thom A Jan 05 '21 at 20:07
  • @PanagiotisKanavos Hi thanks for you response. can u be more specific how i can use indexing to improve the speed? thanks! – liiiii Jan 05 '21 at 20:22
  • You're asking for a tutorial on databases – Panagiotis Kanavos Jan 05 '21 at 20:22

0 Answers0