Given two large tables of ~8,000,000 rows each. They are being joined on substrings of
- name
- address
- zipcode
Naturally, the query is taking a long time. What are the best methods to improve it
-
Does "no primary keys" = "no indexes"? And how exactly are you joining, exact string matches or are you using patterns/LIKE?– Aaron BertrandJul 3, 2014 at 16:19
-
Do a DESC <table_name> \G for both tables. And provide your joining query. What's a "long time"?– VéraceJul 3, 2014 at 16:24
-
In addition to Aaron's comment, what version of SQL Server are you using and can you make ddl changes to the tables?– Sean GallardyJul 3, 2014 at 16:47
-
@Vérace I don't recognise the DESC syntax, how does one enter that? I'm more familiar with using sp_help <table_name>– PaulJul 7, 2014 at 10:30
-
Sorry - I used MySQL syntax. DESCribe - it's not the DESCending in SQL, it's describe the table - or SHOW CREATE TABLE - or whatever it is in SQL Server. Give us the table structure and whatever indexes already exist. Show us the query also. Does SQL Server have functional indexes? Virtual fields? I'm thinking something along those lines.– VéraceJul 7, 2014 at 13:03
Add a comment
|