| Author |
Topic |
|
suhana
Starting Member
2 Posts |
 Posted - 2012-01-02 : 12:12:51
|
| I have several tables with the following fields and datatypesquery involving multiple tables1. State definition tableStateID char(2), AgencyName Varchar(35)AL, AlaskaGA, GeorgiaCO, ColoradoFL, Florida2. Cities definition tableStateID int, Branch Name varchar(35), StateIDchar(2)1000, Ancorage, AK1001, Lakes, AK1002, Kodiac, AK2000, Atlanta, GA2001, Berlin, GA3000, Aurora, CO3001, Denver, CO3002, Boulder, CO4000, Orlando, FL4001, Tampa, FL3. Offices definition tableOfficeName Varchar(35), OfficeDesc Varchar(50)1000 Office10, .....1001 Office11,......1002 Office12,......2000 Office20,......2001 Office21,......3000 Office30,......3001 Office31,......3002 Office32,......4000 Office40,......4001 Office41,......here, each city has just one office( office name is preceded by cityid), but a city can have many offices in it4.Vehicles definition tableVehicleID int, OfficeName, VehicleDesc100, 1000 Office1,.....101, 1000 Office1,.....102, 1000 Office1,.....103, 1000 Office1,.....104, 1001 office2,.....105, 1001 office2,.....106, 1002 office3,.....107, 2000 office4,.....108, 2000 office4,.....109, 2000 office4,.....110, 2001 office5,.....111, 2001 office5,.....112, 2001 office5,.....113, 3000 office6,.....114, 3000 office6,.....There are several other tables which store only the vehicle id and other related atributes of the vehiclefor eg. voltage tablevehicle id int, batteryvolts int,eventdate datetime100, 10286, 8/1/2007 9:20:25 AM100, 10289, 8/1/2007 9:20:28 AM100, 10289, 8/1/2007 9:21:18 AM100, 10286, 8/1/2007 9:21:25 AM101, 10286, 8/1/2007 9:20:25 AM101, 10286, 8/1/2007 9:20:25 AM101, 10286, 8/1/2007 9:20:25 AM102, 10286, 8/1/2007 9:20:25 AM102, 10286, 8/1/2007 9:20:25 AM103, 10286, 8/1/2007 9:20:25 AM103, 10286, 8/1/2007 9:20:25 AM103, 10286, 8/1/2007 9:20:25 AM109, 10286, 8/1/2007 9:20:25 AM109, 10286, 8/1/2007 9:20:25 AM109, 10286, 8/1/2007 9:20:25 AMVehicleUsage tablevehicleid int, odometer int, ignition int, recorddate datetimeI have to generate a report where the user can query either by state, or city, or office or vehicles and you can imagine all possible combinations of parameters there by all state, one or multiple states, all offices and all vehicles, one office or all its vehicles, one or multiple offices and one or more vehicles of the selected offices, all vehicles only, one or multiple vehicles only, etcwhat i want to do is produce a report which lists state, city, office, vehicleid, batteryvolts, odometer, ignition where battereyvolts, odometer, igntionare fields in other tables and are calulated values summed based on the max or min date or other conditions in the where clauseCurrently I am doing a left outer join where I start with state, left outer join with city on stateid, and left outer join with office on city id and so on.But since database is farily large with large number of records and is growing everyday, I am worried about the performance of the query as it could be very quick if the user queries for just one vehicle, but could take much longer if it involves calculating for several vehicles of several officesand so onIf anyone can help me, I would highly appreciate. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-02 : 12:50:30
|
| I would suggest starting off with analysing the execution plans of slow running queries or intensive queries and see if you can add some level of optimisation like adding proper indexes------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
suhana
Starting Member
2 Posts |
Posted - 2012-01-02 : 13:27:11
|
| I have developed a solution which is okay if user queries by just one vehicle.I am worried about the situations where if the user queries by for eg 1000 Office10 and all vehicles in it, or 1000 Office10, 1001 Office11 and all vehicles in it or similar parameter combinationFor eg. user supplies vehicle id 100Currently I have the query something like this:ALTER PROCEDURE [dbo].[reportOneVehicle] @VehicleID intASBEGIN SET NOCOUNT ON;select a.officename , b.vehicleid,c.odometer ,c.Ignition ,e.BatteryVoltage/1000 as Voltage, f.TotalAccumfrom ((select officename, officedesc from Offices) as aleft outer join(select vehicleid, officename from vehicle where vehicleid = 100 ) as bon a.officename = b.officenameleft outer join(select vehicleid, odometer/1609.344 as odometer, ignition/3600 as ignition, recorddate from vehicleusage ) as con b.vehicleid = c.vehicleid)left outer join (select vehicleid,(convert(varchar(6), (max(AccumSeconds))/3600)+ ':' + RIGHT('0' + convert(varchar(2), ((MAX(AccumSeconds))%3600)/60),2) + ':' + RIGHT('0' + convert(varchar(2), (max(AccumSeconds))%60),2)) as TotalAccum from AccumulatedTotal where Desc = 'Motion' group by VehicleID) as f on b.vehicle = f.vehicleid left outer join (select p.vehicleid, p.BatteryVoltage, p.eventdate from voltage p where p.eventdate = (select max(eventdate) as eventdate from voltagewhere vehicleid = p.vehicleid)) as eon b.vehicleid = e.vehicleidorder by b.Officename,b.vehicleid ENDThis is okay for one vehicle, but how should I handle if user has parameters like multiple vehicles (100,101,102) or OfficeName,VehicleID('1000 Office10', '1001 office11') (100,101,102,103)I am worried about performance, and so writing an efficient query which can tackle situations similar to this, where user can query from State level, or city level or office level or vehicle level and yet get all activities related to the vehicles that belong to an office, city, state. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-02 : 15:52:53
|
Firstly, on the query itself, you don't need all those subqueries and having them just makes the query harder to read.Taking just a snippet of your query...SELECT a.officename , b.vehicleid , c.odometer , c.Ignition , e.BatteryVoltage / 1000 AS Voltage , f.TotalAccumFROM ( ( SELECT officename , officedesc FROM Offices ) AS a LEFT OUTER JOIN ( SELECT vehicleid , officename FROM vehicle WHERE vehicleid = 100 ) AS b ON a.officename = b.officename LEFT OUTER JOIN ( SELECT vehicleid , odometer / 1609.344 AS odometer , ignition / 3600 AS ignition , recorddate FROM vehicleusage ) AS c ON b.vehicleid = c.vehicleid ) This could be written as just this:SELECT o.officename , v.vehicleid , v.odometer / 1609.344 AS odometer , v.ignition / 3600 AS ignitionFROM Offices o LEFT OUTER JOIN Vehicle v ON o.officename = v.officename AND v.vehicleid = 100 LEFT OUTER JOIN vehicleusage vu ON v.vehicleid = vu.vehicleid I'm sure you'll agree that's a lot easier to read.Also, if you're going to alias tables, use meaningful aliases. Aliasing Vehicles as B makes no sense. B is unrelated to the table name and just means when I encounter that elsewhere in the query I have to go back and look for which table has been aliased B.As for the various situations, typically you would create one procedure for each, so a procedure for search by vehicleID (if you're using SQL 2008 see table-type parameters for how you pass multiple), another procedure to search by office, etc. One procedure written so it can handle all possibilities is going to be slow or complex, and will usually end up being both.--Gail ShawSQL Server MVP |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
query involving multiple tables