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? Jul 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érace
    Jul 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? Jul 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>
    – Paul
    Jul 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érace
    Jul 7, 2014 at 13:03


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.