SQL Server Performance Forum – Threads Archive
Performance Tuning in SQL
Hai,
In the VB application I am using two tables which have many-to-many relationships. I want to generate a report using the informations from theses two tables. Due to many-to-many relationship, the Crystal report generate the report with multiple entries. That is if the first table has 10 records and second has 20 records, totally 200 records are displayed instead of 20. To avoid this I am thinking of two methods. I can use a new temporary table or the temporary feild in the first table which will be updated whenever the second table is updated. Which is the best method? I want ur help
Hi there, without knowing the details of the application, it almost sounds like a problem with the original design? Are you able to tell us the names of the tables, how they relate and why it is a many-many relationship between the two tables? If you have a many-many then how do you know which record’s field to update when a row changes? The normal approach of dealing with a many-many it to break it into three tables where the two tables both have a many-one with the third table Cheers
Twan
Thanks for ur reply.
In the VB Application I am using two tables called VehicleFuelInfo and VehicleMaintenanceInfo. Both have the relation by having a feild called VehicleNo which is taken from the VehicleMaster table. As there are different transactions for these two, I can’t link them in such a way to produce the report that tells about both Fuel Informations as well as Maintenance Information. If I link it will form a many-to-many relationship. To avoid this I can use a temporary table containing informations from both tables or as I want to show the report that has all the informations from table VehicleFuelInfo and only one feild from the table VehicleMaintenanceIfo called MaintenanceCost, I can use the temporary feild called MaintenanceCost in the table VehicleFuelInfo so that I can update that feild each time a record is added in VehicleMaintenanceInfo table. I want to know which will give the best performance. Thanks
Madhivanan
if you want the total maintenancecost, then you could select sum( MaintenanceCost ) from VehicleMaintenanceInfo. You could also join this into a bigger statement like select …,
( select sum( MaintenanceCost )
from dbo.VehicleMaintenanceInfo
where VehicleNo = vfi.VehicleNo ) as totMaintenanceCost
from dbo.VehicleFuelInfo vfi Cheers
Twan
Hai Twan
Thanks for ur suggession. I think It will work correctly. Thanks
Madhivanan
]]>