SQL Tuning Indexes
-
Hi,
How do I make the below SQL script more efficient and smaller, by changing the design and merging tables. As well as horizontal and vertical petitioning.
SELECT Manuf_name, V_model, count(R.Vehicle_reg) num
FROM Rental R, Vehicle V, Manufacture M
WHERE R.Vehicle_reg = V.Vehicle
AND V.Manuf_id = M,Manuf_id
GROUP BY Manuf_name
ORDER BY num;
Thanks,
Neil.
-
neilwalker (5/11/2012)
Hi,How do I make the below SQL script more efficient and smaller, by changing the design and merging tables. As well as horizontal and vertical petitioning.
SELECT Manuf_name, V_model, count(R.Vehicle_reg) num
FROM Rental R, Vehicle V, Manufacture M
WHERE R.Vehicle_reg = V.Vehicle
AND V.Manuf_id = M,Manuf_id
GROUP BY Manuf_name
ORDER BY num;
Thanks,
Neil.
Query is invalid, v_model isn't in group by.
how many rows in each of the above tables?
Can you post the table schema for all three tables including indexes?
-
Other than being written using ANSI-89 style joins, I don't see anything wrong with the code. I have taken a few minutes to rewrite it using ANSI-92 style joins, which is how you should be writing your t-sql code. It separates the JOIN criteria from any filter criteria you may have in a query.
SELECT
M.Manuf_name,
V.V_model,
count(R.Vehicle_reg) num
FROM
dbo.Rental R
inner join dbo.Vehicle V
on (R.Vehicle_reg = V.Vehicle)
inner join dbo.Manufacture M
on (V.Manuf_id = M,Manuf_id)
GROUP BY
M.Manuf_name
ORDER BY
num;
My question is what is the problem you are experiencing?
-
Thanks Lynn,
Trying to do an assignment. Taken this code and updated it as much as possible. Just wanted to make sure it's written in the most efficient way.
Neil.
-
Only thing I would change is the following:
SELECT
M.Manuf_name,
V.V_model,
count(R.Vehicle_reg) num
FROM
dbo.Rental R
inner join dbo.Vehicle V
on (R.Vehicle_reg = V.Vehicle)
inner join dbo.Manufacture M
on (V.Manuf_id = M,Manuf_id)
GROUP BY
M.Manuf_name,
V.V_model
ORDER BY
num;
You have two non aggregate columns in your select list, you should probably be grouping on both of them, not just one.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply