How to speed up SQL table response time and set proper Indexes?

I have a table, like you can see below

Listings
-----------------------------------------------------------------
ListingID            Make       Model     Year          Zipcode 

1                    BMW        325       2009          90210
2                    Mercedes   330 C     2006          33160
3    

about 2 millions rows. Each day I'm adding new ro开发者_StackOverflowws and deleting old ones.

Within my application I query this table, by listingid, by make, by make and model, by make model and zipcode.

I have clustered index on ListingID, and non-clustered on all others columns.

if I query table by listingid, I get very fast response. by make, model, zipcode it's very slow. for example to return 10000 records with joining table with zipcodes, about 25-30 secs.

here is the size of this table

Here is the example of query I use to return listing within specific radius.

1. First based on zipcode and radius, with help of function I insert zipcode into temp table #CloseZips (takes 0 sec). This procedure returns zipcode within radius very fast 0 sec.

2. Second I join with listings table, you can see. This part takes time 25-60 secs

           SELECT ListingID, Make, Model, Year, L.Zipcode, Year
       FROM Listings L WITH (NOLOCK)
       INNER JOIN  #CloseZips Z ON Z.ZipCode = L.Zipcode 
       WHERE L.Make = @Make AND L.Model = @Model AND L.IsActive = 1
       ORDER BY Z.Distance DESC
   

Questions:

1. What woulb be the right approach to impove performance on that kind of size tables?

2. Do I need to have indexes on all columns?

3. What is the best way to maintain tables like this?

It's a little hard to help you optimize a query when you didn't give the actual query itself, but here it goes:

• If you're joining the table zipcodes, make sure that the zip code column is indexed in that table.

• There's part of the query that you haven't discussed, which is how you evaluate the nearness of one zip code to another. Your query will have to solve that part of the problem also, and I'm guessing that part is written in such a way that it's not indexed.

• If you have separate indexes on make, model, and zipcode, and you search on all three columns only one index will be used. You'll need to use the EXPLAIN tool to discover which one it is — it may not be the optimal one.

• Since, according to your description, a search always involves a make, and the searches that include zipcode always involve the model too, I would replace your indexes on make and model with a single index on (make, model, zipcode). This can be used against any of your queries to provide an indexed search. (You will probably still need the zipcode index for the JOIN).

Again, post the complete SQL query and we may be able to be of more assistance.