SQL performance problem

[Mondeo]

I have a table which is similar to this

 

VehicleRef, Manufacturer, Model, Derivitive, Term, MilesPA, CH

 

1000, Audi, A3, 1.6 SE, 12, 10000, 104.99

1000, Audi, A3, 1.6 SE, 12, 20000, 102.99

1000, Audi, A3, 1.6 SE, 12, 30000, 102.99

1000, Audi, A3, 1.6 SE, 24, 10000, 102.99

1000, Audi, A3, 1.6 SE, 24, 20000, 102.99

1000, Audi, A3, 1.6 SE, 24, 30000, 102.99

1000, Audi, A3, 1.6 SE, 36, 10000, 102.99

1000, Audi, A3, 1.6 SE, 36, 20000, 102.99

1000, Audi, A3, 1.6 SE, 36, 30000, 102.99

 

The above all relates to 1 vehicle and there are about 500 vehicles, the primary key is vehicle ref.

 

For my page of results I want to display each unique vehicle and the lowest CH price for that vehicle (and also the term and milespa relating to the lowest price)

 

First I fill a datatable with the following query (lowerprice and upperprice are variables)

 

SELECT DISTINCT vehicleref,manufacturer,model,derivative FROM tblFigures WHERE ch > lowerprice AND ch < upperprice

 

This works fine and the query executes in under a second.

 

The problem comes when I try and loop through the datatable and add the additional info using a different query.

 

SELECT TOP 1 ch,term,milespa FROM tblFigures WHERE vehicleID = 1000 and ch > lowerprice and ch < upperprice ORDER BY ch ASC

 

When I add the second part it takes over a minute to get the results.

 

Is there anyway I can optimise this, either using indexes or more concise select statements perhaps, any help appreciated.

 

Thanks

[PlausiblyDamp]

Performance wise duplicate primary keys can be a problem anyway - having many duplicate PKs is potentially going to cause problems.

 

If at all possible you might be better off splitting the table into 2 or more (one table containing the VehicleRef, Manufacturer and Model fields with the remaining entries in a related table. This would also simplify your queries as you wouldn't need to be using the DISTINCT keyword for the initial query.

 

Also note that in the code

[highlight=sql]

SELECT TOP 1 ch,term,milespa FROM tblFigures WHERE vehicleID = 1000 and ch > lowerprice and ch < upperprice ORDER BY ch ASC

[/highlight]

having to do an order by so you can limit the results to a single record is also likely to be a performance hit, again if the tables where split a simple join could obtain the correct data without the associated performance hit.

 

Regardless of the above as you appear to be sorting and querying on the ch column this is a possible good choice for an index.

[MrPaul]

Normalization

 

To expand on what PlausiblyDamp posted, you appear to have an awful lot of repeated information there, and it doesn't look like your database design is sufficiently normalized. If you normalize then you will increase speed and efficiency, and will probably avoid problems in other areas (such as your 200-column table in the other thread).

 

It is difficult to give definite advice without knowing your entire database structure, but from the looks of it, the VehicleRef, Manufacturer, Model and Derivitive fields could all be moved out into their own table:

 

 

[u][b]VehicleRef[/b][/u] [u]Manufacturer[/u] [u]Model[/u] [u]Derivative[/u]
1000       Audi         A3    1.6 SE


[u][b]VecID[/b][/u] [u]VehicleRef[/u] [u]Term[/u] [u]MilesPA[/u] [u]CH[/u]
1     1000       12   10000   104.99
2     1000       12   20000   102.99
3     1000       12   30000   102.99
4     1000       24   10000   102.99
5     1000       24   20000   102.99
6     1000       24   30000   102.99
7     1000       36   10000   102.99
8     1000       36   20000   102.99
9     1000       36   30000   102.99

 

I suggest you do some research on normalization, and attempt to get your database into at least 3NF. First normal form (1NF), the most basic normalization step, requires that you have a unique primary key field, and currently you do not have even that, so your database design definitely needs attention.

 

Good luck :cool: