CODEWITHSUNDEEP
sqlsql-serverselectoptimization
user3260664
user3260664

Reputation: 155

Optimize ms sql select query for huge DB

My query is 

SELECT DISTINCT
    Company.Name,
    Customer.FirmID,
    Customer.ID,
    Customer.ExtendedId,
    (CustomerExtended.FirstName + ' ' + CustomerExtended.LastName) as "Customer Name"

FROM
    Company,
    Customer,
    CustomerExtended

WHERE
    (Customer.ExtendedId = 1) OR
    (CustomerExtended.FirstName = 'John' AND CustomerExtended.LastName = 'Smith')

since DB is very huge, I didn't managed to execute this query evan once. I waited 2 hours but it kept running. is there a way to optimize this?

Upvotes: 1

Views: 90

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44346

If you need distinct you probably did something wrong. In this case you didn't join your tables(or didn't join your tables correct, what you did is called a cross join)

SELECT 
    com.Name,
    cus.FirmID,
    cus.ID,
    cus.ExtendedId,
    cue.FirstName + ' ' + cue.LastName as [Customer Name]
FROM Company com 
JOIN Customer cus
ON com.id = cus.fk_Company
JOIN CustomerExtended cue
ON cue.fk_Customer = cus.id
WHERE
    (cus.ExtendedId = 1) OR
    (cue.FirstName = 'John' AND cue.LastName = 'Smith')

The id should be primary keys, the fk columns should be foreign keys refering to the primary keys

Upvotes: 1

podiluska
podiluska

Reputation: 51514

Unless your customer base is every single human, I doubt your database is actually "very huge", so I think you probably need to join your tables to get your desired results

eg:

FROM
    Company 
        inner join Customer on Company.CompanyID = Customer.FirmID
    ...

See http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ for an explanation of joins.

Upvotes: 4

Related Questions