November 2, 2010 at 1:38 pm
I have a query I'm revisiting from my earlier days at my current position. I've noticed the query has started to timeout several times a week so I want to look at what I have previously written to see where I can make improvements. This particular query feels very convoluted to me and I know there has to be a more efficient way to get the result I'm needing. I've done a lot of research concerning joins - I've ran execution plans and studied what the various messages mean, as well as running the DB engine tuning advisor. I'm feeling stuck and a little over my head considering the level of training I've had in writing more complex t-sql queries.
The overall situation: I have three different databases, two proprietary solutions my company purchased over which I hold no control and can not make changes like adding indexes, etc. (with the exception of creating views and under SOME circumstances stored procs)and one home grown database where we store additional information. DB #1 stores GPS-tracked data, such as mileage and longitude and latitude of vehicles. DB#2 is part of a solution (pre-packaged) which allows us to track details about what happens when said vehicles are on the road. The most important piece of info I pull from DB #2 is an ID which is not static and can be switched about between vehicles on any given day (analogy: dynamic IP addresses). DB #3 stores maintenance info on said vehicles, like service dates, mileage on oil changes, etc.
I created a series of views to pull each piece together. One issue I have is that DB#1 needs an index on a table and doesn't have one. Also, it has bad data in it which I am unable to purge or predict when this data will occur. So I have a basic query pulling the latest GPS stats for each vehicle to be tracked. Because of the need for an index (and not having one) this query takes longer than it should. (We are dealing with each vehicle updating and logging to the database every 5 seconds or so - lots & lots of records).
The view's select statement is as follows:
SELECT VehicleID,RPTUTCDATE as DateMileage,UnitNumber, MedicNumber,Odometer as Mileage,ipAddress
FROM (
SELECT VehicleHistory.VehicleID,Odometer,
RPTUTCDATE,
VehicleName as UnitNumber,
VehicleColor as MedicNumber, ipAddress,
ROW_NUMBER() OVER (PARTITION BY Vehicle.VehicleID ORDER BY RPTUTCDATE DESC) AS RowNo
FROM VehicleHistory INNER JOIN Vehicle on Vehicle.VehicleID = VehicleHistory.VehicleID
WHERE Odometer IS NOT NULL AND Vehicle.VehicleGroupID = 35 ) sub
WHERE sub.RowNo = 1
UNION ALL
SELECT vehicle_id, dt_mileage, unit_number, medic_number, mileage,'0' as ipAddress
FROM intranet.dbo.Oilchange_Manual_Entries
Because of the bad data, I had to do a ROW_NUMBER() instead of a TOP or MAX to find the latest data w/ highest mileage. It's the only statement I've tried that worked consistently. This query, however, takes 5 seconds to run and that alone makes my stomach turn. LOL
I then created another view to join the previous view with DB#2. It pulls an address, description field and the dynamic ID I wrote about earlier.
SELECT RCSQL.dbo.Vehicles.daddress, RCSQL.dbo.Vehicles.description, dbo.vw_TJ_oilchange_mileage.DateMileage, dbo.vw_TJ_oilchange_mileage.UnitNumber,
dbo.vw_TJ_oilchange_mileage.MedicNumber, dbo.vw_TJ_oilchange_mileage.Mileage, RCSQL.dbo.Vehicles.veh, dbo.vw_TJ_oilchange_mileage.ipAddress,
dbo.vw_TJ_oilchange_mileage.VehicleID
FROM dbo.vw_TJ_oilchange_mileage LEFT OUTER JOIN
RCSQL.dbo.Vehicles ON dbo.vw_TJ_oilchange_mileage.MedicNumber = RCSQL.dbo.Vehicles.veh
The final view takes info from view #2(immediately above) and joins it to a notes field concerning maintenance service on vehicles from DB#3, the home grown DB. It is this query where I found an 8 second tack-on to the time. It takes nearly 14 seconds to run and shouldn't because there are only about 320 records in the table. My suspicion is that I am not joining the tables correctly and it is trying to do some sort of grouping on more records than necessary.
SELECT TOP (100) PERCENT vw.DateMileage, vw.UnitNumber, vw.MedicNumber, vw.Mileage, vw.daddress, CASE WHEN LEFT(vw.UnitNumber, 1)
= 9 THEN CAST(vw.Mileage AS int) ELSE CAST(ROUND(vw.Mileage * 0.062137119, 0) AS int) END AS Mileage_Converted, LEFT(n.notes, 20) AS Notes,
MAX(om.mileage_on_oilchange) AS LastOilChange, MAX(om.mileage_on_oilchange) + 4000 AS NextOilChange, CASE WHEN LEFT(vw.UnitNumber, 1)
= 9 THEN MAX((om.mileage_on_oilchange) + 4000) - vw.Mileage ELSE (MAX(om.mileage_on_oilchange) + 4000) - vw.Mileage * 0.062137119 END AS HowClose,
CASE WHEN LEFT(vw.UnitNumber, 1) = 9 AND ((MAX(mileage_on_oilchange) + 4000) - (Mileage)) > 400 THEN 'Early' WHEN LEFT(vw.UnitNumber, 1) != 9 AND
((MAX(mileage_on_oilchange) + 4000) - (Mileage * 0.062137119)) > 400 THEN 'Early' WHEN LEFT(vw.UnitNumber, 1) = 9 AND ((MAX(mileage_on_oilchange) + 4000)
- Mileage) < 0 THEN 'Late' WHEN LEFT(vw.UnitNumber, 1) != 9 AND ((MAX(mileage_on_oilchange) + 4000) - (Mileage * 0.062137119))
< 0 THEN 'Late' WHEN LEFT(vw.UnitNumber, 1) != 9 AND ((MAX(mileage_on_oilchange) + 4000) - (Mileage * 0.062137119)) < 400 AND ((MAX(mileage_on_oilchange)
+ 4000) - (Mileage * 0.062137119)) > 0 THEN 'Time' WHEN LEFT(vw.UnitNumber, 1) = 9 AND ((MAX(mileage_on_oilchange) + 4000) - (Mileage)) < 400 AND
((MAX(mileage_on_oilchange) + 4000) - (Mileage)) > 0 THEN 'Time' END AS Flag
FROM intranet.dbo.Oilchange_Notes AS n RIGHT OUTER JOIN
dbo.vw_TJ_Oilchange_MileageWithLocation AS vw ON n.vehicle_id = vw.UnitNumber LEFT OUTER JOIN
intranet.dbo.Oilchange_Mileage AS om ON vw.UnitNumber = om.vehicle_id
GROUP BY vw.DateMileage, vw.UnitNumber, vw.MedicNumber, vw.Mileage, n.notes, vw.daddress
ORDER BY vw.UnitNumber
When I remove all the code having to do with the Oilchange_Notes table, I noticed a definite impact of shedding the 8 seconds. (I took each section out and ran it to see what seemed to be the biggest issue.)
Here are scripts for the tables involved:
DB #1
VehicleHistory
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VehicleHistory](
[VehicleID] [int] NOT NULL,
[RPTUTCDATE] [datetime] NOT NULL,
[RptType] [smallint] NOT NULL CONSTRAINT [DF_VehicleHistory_RptType] DEFAULT ((-1)),
[IsGPSRpt] [bit] NOT NULL CONSTRAINT [DF_VehicleHistory_IsGPSRpt] DEFAULT ((1)),
[CreateTS] [datetime] NULL CONSTRAINT [DF_VehicleHistory_CreateTS] DEFAULT (getdate()),
[Velocity] [float] NULL,
[Heading] [smallint] NULL,
[Altitude] [float] NULL,
[GPSFix] [bit] NULL,
[SatellitesTracked] [smallint] NULL,
[SatellitesVisible] [smallint] NULL,
[RSSI] [smallint] NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[OdometerEnabled] [bit] NULL,
[Odometer] [int] NULL,
[IsIgnitionOn] [bit] NULL,
[RTSIEnabled] [bit] NULL,
[DTRIEnabled] [bit] NULL,
[Com1000InputsIncluded] [bit] NULL,
[Input1State] [bit] NULL,
[Input1NotificationFlag] [bit] NULL,
[Input2State] [bit] NULL,
[Input2NotificationFlag] [bit] NULL,
[Input3State] [bit] NULL,
[Input3NotificationFlag] [bit] NULL,
[Input4State] [bit] NULL,
[Input4NotificationFlag] [bit] NULL,
[Input5State] [bit] NULL,
[Input5NotificationFlag] [bit] NULL,
[Input6State] [bit] NULL,
[Input6NotificationFlag] [bit] NULL,
[Input7State] [bit] NULL,
[Input7NotificationFlag] [bit] NULL,
[Input8State] [bit] NULL,
[Input8NotificationFlag] [bit] NULL,
[AnalogInput1] [float] NULL,
[AnalogInput2] [float] NULL,
[AnalogInput3] [float] NULL,
[AnalogInput4] [float] NULL,
[AnalogInput5] [float] NULL,
[AnalogInput6] [float] NULL,
[AnalogInput7] [float] NULL,
[AnalogInput8] [float] NULL,
[PowerLevel] [float] NULL,
[IgnitionHasTurnedOn] [bit] NULL,
[IgnitionHasTurnedOff] [bit] NULL,
[SpeedViolationFlag] [bit] NULL,
[PwrDownIgnOffTimeOut] [bit] NULL,
[NVRAMError] [bit] NULL,
[VLUPwrdDwnDueToPowerLow] [bit] NULL,
[Event90PercentFull] [bit] NULL,
[FlashHWErrorOccurred] [bit] NULL,
[FlashUpgradeUnsuccessful] [bit] NULL,
[FlashUpgradeSuccessful] [bit] NULL,
[Output1State] [bit] NULL,
[Output2State] [bit] NULL,
[Output3State] [bit] NULL,
[Output4State] [bit] NULL,
[Output5State] [bit] NULL,
[Output6State] [bit] NULL,
[Output7State] [bit] NULL,
[Output8State] [bit] NULL,
[RFIdentifier] [int] NULL,
[RFChannel] [int] NULL,
[RFCell] [int] NULL,
[ContainsNotifications] [bit] NULL CONSTRAINT [DF_VehicleHistory_ContainsNotifications] DEFAULT ((0)),
[IsTimeCorrected] [bit] NULL CONSTRAINT [DF_VehicleHistory_IsTimeCorrected] DEFAULT ((0)),
[Packet] [varchar](225) NULL,
[PacketID] [numeric](18, 0) NULL,
[TSDSRcvdDate] [datetime] NULL,
[PacketSeq] [smallint] NULL,
[ipAddress] [varchar](30) NULL,
CONSTRAINT [PK_VehicleHistory] PRIMARY KEY CLUSTERED
(
[VehicleID] ASC,
[RPTUTCDATE] ASC,
[RptType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
AND then the Vehicle table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Vehicle](
[VehicleID] [int] IDENTITY(1,1) NOT NULL,
[VehicleName] [varchar](25) NOT NULL,
[DeviceUID] [varchar](24) NOT NULL,
[DeviceTypeID] [smallint] NOT NULL,
[VehicleGroupID] [int] NOT NULL,
[HomeGeofenceID] [int] NULL,
[VehicleMake] [varchar](15) NULL,
[VehicleModel] [varchar](15) NULL,
[VehicleYear] [int] NULL,
[VehicleColor] [varchar](15) NULL,
[VehicleLicense] [varchar](15) NULL,
[TelogisIconNum] [int] NOT NULL CONSTRAINT [DF_Vehicle_TelogisIconNum] DEFAULT ((61)),
[MOIconNum] [int] NULL,
[MOIconSize] [int] NULL,
[MOIconColor] [int] NULL,
[MOIconFont] [varchar](50) NULL,
[VehicleLicenseKey] [varchar](50) NULL,
[UserDefinedColumn1] [varchar](25) NULL,
[UserDefinedColumn2] [varchar](25) NULL,
[UserDefinedColumn3] [varchar](25) NULL,
[UserDefinedColumn4] [varchar](25) NULL,
[AnalogScaleID1] [int] NULL,
[AnalogScaleID2] [int] NULL,
[AnalogScaleID3] [int] NULL,
[AnalogScaleID4] [int] NULL,
[AnalogScaleID5] [int] NULL,
[AnalogScaleID6] [int] NULL,
[AnalogScaleID7] [int] NULL,
[AnalogScaleID8] [int] NULL,
[LastModifiedByUserID] [int] NULL,
[LastAppModifiedTS] [datetime] NULL,
[CreateTS] [datetime] NOT NULL CONSTRAINT [DF_Vehicles_ctime] DEFAULT (getdate()),
[LastModifiedTS] [datetime] NOT NULL CONSTRAINT [DF_Vehicles_mtime] DEFAULT (getdate()),
[MobileID] [varchar](25) NULL,
[MasterGroupID] [int] NOT NULL CONSTRAINT [DF_Vehicle_MasterGroupID] DEFAULT ((1)),
[VehicleLicenseType] [smallint] NOT NULL CONSTRAINT [DF_Vehicle_VehicleLicenseType] DEFAULT ((1)),
[VehicleTrailID] [int] NULL,
[VIN] [varchar](50) NULL,
[MineFleetEnabled] [bit] NOT NULL CONSTRAINT [DF_Vehicle_MineFleetEnabled] DEFAULT ((0)),
[ActiveStatus] [smallint] NOT NULL CONSTRAINT [DF_Vehicle_ActiveStatus] DEFAULT ((1)),
CONSTRAINT [PK_Vehicle] PRIMARY KEY CLUSTERED
(
[VehicleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_Vehicle_DeviceUID] UNIQUE NONCLUSTERED
(
[DeviceUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [UQ_Vehicle_VehicleName] UNIQUE NONCLUSTERED
(
[MasterGroupID] ASC,
[VehicleName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID1] FOREIGN KEY([AnalogScaleID1])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID1]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID2] FOREIGN KEY([AnalogScaleID2])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID2]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID3] FOREIGN KEY([AnalogScaleID3])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID3]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID4] FOREIGN KEY([AnalogScaleID4])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID4]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID5] FOREIGN KEY([AnalogScaleID5])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID5]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID6] FOREIGN KEY([AnalogScaleID6])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID6]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID7] FOREIGN KEY([AnalogScaleID7])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID7]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_AnalogScaleID8] FOREIGN KEY([AnalogScaleID8])
REFERENCES [dbo].[AnalogScale] ([AnalogScaleID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_AnalogScaleID8]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_HomeGeofenceID] FOREIGN KEY([HomeGeofenceID])
REFERENCES [dbo].[Geofence] ([GeofenceID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_HomeGeofenceID]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_LU_DeviceType] FOREIGN KEY([DeviceTypeID])
REFERENCES [dbo].[LU_DeviceType] ([DeviceTypeID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_LU_DeviceType]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_MasterGroupID] FOREIGN KEY([MasterGroupID])
REFERENCES [dbo].[MasterGroup] ([MasterGroupID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_MasterGroupID]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_VehicleGroup] FOREIGN KEY([VehicleGroupID])
REFERENCES [dbo].[VehicleGroup] ([VehicleGroupID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_VehicleGroup]
GO
ALTER TABLE [dbo].[Vehicle] WITH NOCHECK ADD CONSTRAINT [FK_Vehicle_VehicleLicenseType] FOREIGN KEY([VehicleLicenseType])
REFERENCES [dbo].[LU_VehicleLicenseType] ([VehicleLicenseTypeID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_VehicleLicenseType]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [FK_Vehicle_VehicleTrail] FOREIGN KEY([VehicleTrailID])
REFERENCES [dbo].[VehicleTrail] ([VehicleTrailID])
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [FK_Vehicle_VehicleTrail]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [CK_Vehicle_DeviceUID] CHECK ((len([DeviceUID])>(0)))
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [CK_Vehicle_DeviceUID]
GO
ALTER TABLE [dbo].[Vehicle] WITH CHECK ADD CONSTRAINT [CK_Vehicle_VehicleYear] CHECK (([VehicleYear]>(1900)))
GO
ALTER TABLE [dbo].[Vehicle] CHECK CONSTRAINT [CK_Vehicle_VehicleYear]
This is the code for the table from DB #2
Vehicles
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Vehicles](
[cmpy] [int] NOT NULL CONSTRAINT [DF_Vehicles_cmpy_2__10] DEFAULT (0),
[veh] [varchar](6) NOT NULL,
[description] [varchar](40) NULL,
[zone] [int] NOT NULL CONSTRAINT [DF_Vehicles_zone_18__10] DEFAULT (1),
[vtype] [int] NOT NULL CONSTRAINT [DF_Vehicles_vtype_15__10] DEFAULT (1),
[vstatus] [int] NOT NULL CONSTRAINT [DF_Vehicles_vstatus_14__10] DEFAULT (0),
[post] [int] NOT NULL CONSTRAINT [DF_Vehicles_post_10__10] DEFAULT (0),
[facility] [int] NOT NULL CONSTRAINT [DF_Vehicles_facility_8__10] DEFAULT (1),
[address] [varchar](75) NULL,
[address2] [varchar](30) NULL,
[city] [varchar](30) NULL,
[state] [varchar](2) NULL,
[zip] [varchar](10) NULL,
[vx] [int] NOT NULL CONSTRAINT [DF_Vehicles_vx_16__10] DEFAULT (0),
[vy] [int] NOT NULL CONSTRAINT [DF_Vehicles_vy_17__10] DEFAULT (0),
[startx] [int] NOT NULL CONSTRAINT [DF_Vehicles_startx_12__10] DEFAULT (0),
[starty] [int] NOT NULL CONSTRAINT [DF_Vehicles_starty_13__10] DEFAULT (0),
[maxload] [int] NOT NULL CONSTRAINT [DF_Vehicles_maxload_9__10] DEFAULT (999),
[curload] [int] NOT NULL CONSTRAINT [DF_Vehicles_curload_3__10] DEFAULT (0),
[licenseplate] [varchar](30) NULL,
[licenseexpires] [varchar](10) NULL,
[mobileid] [varchar](20) NULL,
[dfacility] [int] NOT NULL CONSTRAINT [DF_Vehicles_dfacility_4__10] DEFAULT (1),
[daddress] [varchar](75) NULL,
[daddress2] [varchar](30) NULL,
[dcity] [varchar](30) NULL,
[dstate] [varchar](2) NULL,
[dzip] [varchar](10) NULL,
[dvx] [int] NOT NULL CONSTRAINT [DF_Vehicles_dvx_5__10] DEFAULT (0),
[dvy] [int] NOT NULL CONSTRAINT [DF_Vehicles_dvy_6__10] DEFAULT (0),
[dzone] [int] NOT NULL CONSTRAINT [DF_Vehicles_dzone_7__10] DEFAULT (1),
[SchRecNo] [int] NOT NULL CONSTRAINT [DF_Vehicles_SchRecNo_11__10] DEFAULT (0),
[statdate] [varchar](10) NULL,
[stattime] [varchar](8) NULL,
[onboardphone] [varchar](26) NULL,
[numericpagerphone] [varchar](25) NULL,
[alphapagerphone] [varchar](25) NULL,
[alphapagerid] [varchar](25) NULL,
[autopage] [bit] NOT NULL CONSTRAINT [DF_Vehicles_autopage_1__10] DEFAULT (0),
[curcrew] [int] NULL CONSTRAINT [DF__Veh__curcre__175576E9] DEFAULT (0),
[normvtype] [int] NULL CONSTRAINT [DF__Veh__normvt__18499B22] DEFAULT (0),
[curschedrec] [int] NULL CONSTRAINT [DF__Veh__cursch__2C5093CF] DEFAULT (0),
[curodometer] [int] NULL CONSTRAINT [DF__Veh__curodo__2D44B808] DEFAULT (0),
[curhist] [int] NULL CONSTRAINT [DF__Veh__curhis__4333F927] DEFAULT (0),
[workzone1] [int] NULL CONSTRAINT [DF_Veh_workzone1_1__10] DEFAULT (0),
[workzone2] [int] NULL CONSTRAINT [DF_Veh_workzone2_2__10] DEFAULT (0),
[workzone3] [int] NULL CONSTRAINT [DF_Veh_workzone3_3__10] DEFAULT (0),
[workzone4] [int] NULL CONSTRAINT [DF_Veh_workzone4_4__10] DEFAULT (0),
[workzone5] [int] NULL CONSTRAINT [DF_Veh_workzone5_5__10] DEFAULT (0),
[vin] [varchar](50) NULL,
[purchaseDate] [varchar](10) NULL CONSTRAINT [DF__Veh__purcha__2A00990E] DEFAULT ('1900-01-01'),
[otherID] [varchar](50) NULL,
[homepost] [int] NULL CONSTRAINT [DF_Vehicles_homepost_19__10] DEFAULT (0),
[curnumericpagerphone] [varchar](25) NULL,
[curalphapagerphone] [varchar](25) NULL,
[curalphapagerid] [varchar](25) NULL,
[speed] [int] NULL CONSTRAINT [DF_Vehicles_speed_1__18] DEFAULT (0),
[billcode] [varchar](10) NULL,
[mobileack] [int] NULL CONSTRAINT [DF_Vehicles_mobileack_1__13] DEFAULT (0),
[mobileflags] [varchar](20) NULL,
[curnumericpagerphone2] [varchar](25) NULL,
[curalphapagerphone2] [varchar](25) NULL,
[curalphapagerid2] [varchar](25) NULL,
[curnumericpagerphone3] [varchar](25) NULL,
[curalphapagerphone3] [varchar](25) NULL,
[curalphapagerid3] [varchar](25) NULL,
[CurrentSpeed] [int] NULL DEFAULT ((-1)),
[CurrentDirection] [int] NULL DEFAULT ((-1)),
[startaddress] [varchar](30) NULL,
[startaddress2] [varchar](30) NULL,
[startcity] [varchar](30) NULL,
[startstate] [varchar](2) NULL,
[startzip] [varchar](10) NULL,
[startzone] [int] NULL DEFAULT (1),
[pagingmailaddress] [varchar](75) NULL,
[curpagingmailaddress] [varchar](75) NULL,
[curpagingmailaddress2] [varchar](75) NULL,
[curpagingmailaddress3] [varchar](75) NULL,
[paging_service_profile] [int] NULL,
[gps_timestamp] [datetime] NULL,
[current_unit_code] [int] NOT NULL DEFAULT (0),
[last_unit_code] [int] NOT NULL DEFAULT (0),
[Souls] [int] NOT NULL CONSTRAINT [DF_Vehicles_Souls] DEFAULT (0),
[FuelDepletedTime] [datetime] NOT NULL CONSTRAINT [DF_Vehicles_FuelDepleted] DEFAULT ('1900-01-01 00:00:00'),
[ETEOverride] [datetime] NOT NULL CONSTRAINT [DF_Vehicles_ETEOverride] DEFAULT ('1900-01-01 00:00:00'),
[InFlight] [int] NOT NULL CONSTRAINT [DF_Vehicles_InFlight] DEFAULT (0),
[LastFPWaypoint] [int] NOT NULL CONSTRAINT [DF_Vehicles_LastFPWaypoint] DEFAULT (0),
[Altitude] [int] NOT NULL CONSTRAINT [DF_Vehicles_Altitude] DEFAULT (0),
[FuelBeingDepleted] [int] NOT NULL CONSTRAINT [DF_Vehicles_FuelBeingDepleted] DEFAULT (1),
[vsb_id] [int] NOT NULL DEFAULT (0),
[vpriority] [int] NOT NULL DEFAULT (0),
[HomePostTimeZoneID] [int] NOT NULL DEFAULT (0),
[VehicleID] [int] IDENTITY(1,1) NOT NULL,
[MaintenanceID] [int] NULL,
[LastChanged] [datetime] NULL,
[SyncEntry] [bit] NOT NULL DEFAULT ((1)),
CONSTRAINT [PK_Vehices_1__13] PRIMARY KEY CLUSTERED
(
[cmpy] ASC,
[veh] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Vehicles_ID] UNIQUE NONCLUSTERED
(
[VehicleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Company vehicle belongs to' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'cmpy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicle id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'veh'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicle description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'zone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicle type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vtype'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicle status' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vstatus'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current post of the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'post'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'facility of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'facility'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'address'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'last official address of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'address2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'last official city of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'city'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start state (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'state'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current zip code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'zip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current latitude' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vx'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current longitude' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start longitude (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startx'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start latitude (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'starty'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'maximum load of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'maxload'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curload'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'license plate number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'licenseplate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'time when vehicle license expires' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'licenseexpires'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'mobile id for vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'mobileid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off facility code of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dfacility'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off address of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'daddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off address of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'daddress2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off city of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dcity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off state of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dstate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off zip code of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dzip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off latitude of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dvx'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off longitude of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dvy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'drop off zone of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'dzone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current schrecno of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'SchRecNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicle start date(used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'statdate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start time (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'stattime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'onboardphone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'numericpagerphone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'alphapagerphone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'pager id of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'alphapagerid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'checkbox control for autopage **Legend : 1 is checked 0 is unchecked' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'autopage'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curcrew'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'associated vehicle type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'normvtype'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current vehicle_schedule code of vehicle activation' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curschedrec'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curodometer'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curhist'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'working zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'workzone1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'working zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'workzone2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'working zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'workzone3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'working zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'workzone4'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'working zone' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'workzone5'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vin number of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vin'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'date vehicle purchased' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'purchaseDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'"other id" field in vehicle configuration' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'otherID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'home post of the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'homepost'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curnumericpagerphone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curalphapagerphone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'internally used for paging -- references code of driver to page if paging by crew members with auto paged turned on for the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curalphapagerid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current speed of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'speed'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'export code of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'billcode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'internal code used for avl' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'mobileack'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'internal code used for avl' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'mobileflags'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curnumericpagerphone2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curalphapagerphone2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'internally used for paging -- references code of driver to page if paging by crew members with auto paged turned on for the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curalphapagerid2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curnumericpagerphone3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curalphapagerphone3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'internally used for paging -- references code of driver to page if paging by crew members with auto paged turned on for the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curalphapagerid3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'CurrentSpeed'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'CurrentDirection'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start address (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startaddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start address (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startaddress2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start city (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startcity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start date (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startstate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicle start zip (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startzip'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'vehicles start zone (used internally)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'startzone'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'associated paging_mail address if associated profile is type of email' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'pagingmailaddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curpagingmailaddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curpagingmailaddress2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'not used' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'curpagingmailaddress3'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'associated paging service profile' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'paging_service_profile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'date and time of the last gps update' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'gps_timestamp'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'code of the associated unit id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'current_unit_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'last associated unit_id of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'last_unit_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'amount of people on board' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'Souls'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'time remaining until fuel depetion' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'FuelDepletedTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'over ride for estimated time to enroute' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'ETEOverride'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'if vehicle is in the air **Legend : 0 is no, 1 is yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'InFlight'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last waypoint the vehicle hit at time of update' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'LastFPWaypoint'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'current altitude of vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'Altitude'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'toggle for if fuel is being depleted **Legend : 0 is no, 1 is yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'FuelBeingDepleted'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the Vehicle Status Broadcaster that this vehicle is associated with ( should no lonber be used after version 3.8 )' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vsb_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'highest priority of trips assigned to the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'vpriority'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'time zone id of the home post of the vehicle' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'HomePostTimeZoneID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vehicle ID field (alternate ID)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'VehicleID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Maintenance id of the maintenance activity when vehicle is out of service' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'MaintenanceID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Used in Crew Scheduler Integration to determine when the record was last modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'LastChanged'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Used to sync a table entry with Crew Scheduler' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles', @level2type=N'COLUMN',@level2name=N'SyncEntry'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vehicle-specific data.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Vehicles'
GO
ALTER TABLE [dbo].[Vehicles] WITH NOCHECK ADD CONSTRAINT [FK_Vehicle_Priority] FOREIGN KEY([vpriority])
REFERENCES [dbo].[Priorities] ()
GO
ALTER TABLE [dbo].[Vehicles] NOCHECK CONSTRAINT [FK_Vehicle_Priority]
Here is the code from DB#3:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Oilchange_Notes](
[notes_id] [int] IDENTITY(1,1) NOT NULL,
[vehicle_id] [int] NOT NULL,
[notes] [nvarchar](1000) NOT NULL,
[dt_last_modified] [timestamp] NOT NULL,
CONSTRAINT [PK_Oilchange_Notes] PRIMARY KEY CLUSTERED
(
[notes_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Oilchange_Mileage](
[oilchange_id] [int] IDENTITY(1,1) NOT NULL,
[vehicle_id] [int] NOT NULL,
[dt_oilchange] [datetime] NULL,
[mileage_on_oilchange] [int] NOT NULL,
[dt_last_modified] [datetime] NULL,
CONSTRAINT [PK_Oilchange_Mileage] PRIMARY KEY CLUSTERED
(
[oilchange_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
If I need to add any other info to this, let me know. To recap: In the final view, I discovered it takes 12 seconds on a good day to run. Sometimes longer. Many times, it times out on my users. I'd like to see if there is a more efficient way to join all the tables & databases I need and shave some time off.
For me, I'm not just looking for a solution. I want to learn how to code better in t-sql. If you can explain not just the what/how but the why, I would greatly appreciate it.
November 3, 2010 at 9:57 am
I didn't receive any help or nibbles of any kind, however, as I've been looking over this I can see my "feeling" that it was grouping before it should seemed to be happening on my join. I joined a table from DB#3 to a view on DB#1 to a table on DB#3. I created a view for the data I needed on DB#3 and joined that with the view on DB#1.
While it did shave off 8 seconds, I still don't have, or feel like I have, any more knowledge on how to efficiently create complex joins. When I get past 3 tables or views, I feel lost.
If someone can point me to an online article or book that delves into this beyond a rudimentary look, I'd be very grateful. All I can find is the typical and very basic tutorial on joins. Nothing for the level I need.
Thanks!
November 3, 2010 at 11:51 am
TJonsek,
It's not a lack of interest, but time, that's probably got a lot of people delaying on getting involved in this thread. There's a lot of information in your first post and it needs to be digested for intelligent questions and discussion.
I wouldn't expect an answer until either one of the weekend junkies or someone who finds a lot of spare time during the work week can dig into it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 12:18 pm
I appreciate the reply and trust me when I say I know it's an incredible amount of information. It's not at all simple to work out. LOL
But as I came up with an idea to try & see if I could make an impact, I thought I'd post it. It's still not what I'd like it to be, but 8 seconds off a query is not a small thing when dealing with a web app.
And I do appreciate anyone who takes time & effort to read through this, digest it and/or try to help!
November 3, 2010 at 12:27 pm
Here's as far as I got in the time I've had so far.
Try this out, see if it helps with the first query's performance. I'll see if I can do more later.
;
WITH LastHistory(VID, LastDate)
AS (SELECT
VehicleID,
MAX(RPTUTCDATE)
FROM
dbo.VehicleHistory
WHERE
Odometer IS NOT NULL
GROUP BY
VehicleID) ,
History(VID, DateMileage, Mileage, ipAddress)
AS (SELECT
VehicleID,
RPTUTCDATE,
Odometer,
ipAddress
FROM
dbo.VehicleHistory AS VH
INNER JOIN LastHistory AS LH
ON VH.VehicleID = LH.VID
AND VH.RPTUTCDATE = LH.LastDate)
SELECT
VehicleID,
DateMileage,
VehicleName AS UnitNumber,
VehicleColor AS MedicNumber,
Mileage,
ipAddress
FROM
dbo.Vehicle AS V
INNER JOIN History AS H
ON V.VehicleID = H.VID
UNION ALL
SELECT
vehicle_id,
dt_mileage,
unit_number,
medic_number,
mileage,
'0' as ipAddress
FROM
intranet.dbo.Oilchange_Manual_Entries ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 3:18 pm
I also notice that you are missing some indices that might help. For example, Oilchange_Notes is missing an index on vehicle_id. Since that is used in your join, it will probably help to have an index on that field. If you turn on XML statistics, that will list possible missing indices.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 17, 2010 at 9:22 am
Thank you! I'm just getting back to this after being placed on another project for the last little bit. I'll look into the indices suggestion on the table.
G Squared - your code seems to have shaved time off - can you explain how/why or point me to a resource that can? I don't want to just copy & past, I want to learn so I can be a better coder.
Much Thanks!
November 17, 2010 at 10:01 am
One note of interest: When I run the database tuning advisor, it suggests an index on a table we do not control. We will be asking the company we purchased the product from if it would be possible to add the index.
Also - when I run the query first thing in the morning, it can take more than 60 seconds. But any time after that, it runs much faster. I am wondering if the timeout issue I see for my users could be the fact that the time it takes the query to run initially is greater than the timeout allowed for in the app. Once the query is run, I'm guessing it is caching it and this is why we see more errors in the morning as opposed to middle of day?
While I am working on the performance end, I've created a job to run that particular query every x hours to test that theory and see if it eliminates the timeouts.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply