Nasty Access to SQL Conversion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nasty Access to SQL Conversion

Author

Topic

danodemano
Starting Member

4 Posts

Posted - 2008-08-27 : 09:39:09

So I have actually two queries that are linked that I need to be able to convert to SQL 2005. Let me give a brief background. I need to access the SQL server to pull locations of vehicles to display on a web page. I am currently linking to an Access database, which in turn links into the SQL server. This is, to say the least, not ideal and usually quite slow. I need to be able to convert these queries to be able to run them straight against the SQL server so that I don't have to "hop" through the Access database, if that's even possible. Here are the two queries:

qryAVLLatestUpdate:


SELECT dbo_TrackingCoord.TrackingObjectID, Max(dbo_TrackingCoord.EventTime) AS MaxOfEventTime
FROM dbo_TrackingCoord
WHERE (((dbo_TrackingCoord.EventType) Not Like "*Moved by DisSPatch Client*"))
GROUP BY dbo_TrackingCoord.TrackingObjectID;

qryAVLLatestDetails:


SELECT dbo_Vehicle.VehicleName, IIf([dbo_crew.isactive],[dbo_Employee.name],Null) AS Employee, 
dbo_TrackingCoord.EventTime, dbo_Vehicle.ContactInfo, dbo_TrackingCoord.Lat, dbo_TrackingCoord.Lon, 
dbo_TrackingCoord.Speed, dbo_TrackingCoord.Heading, dbo_TrackingCoord.Location, dbo_TrackingCoord.EventType, 
dbo_VehicleList.CrewID, dbo_Crew.CrewName, dbo_Crew.IsActive, dbo_Vehicle.Type
FROM (((((qryAVLLatestUpdate LEFT JOIN dbo_TrackingCoord ON (qryAVLLatestUpdate.MaxOfEventTime=dbo_TrackingCoord.EventTime) 
AND (qryAVLLatestUpdate.TrackingObjectID=dbo_TrackingCoord.TrackingObjectID)) 
LEFT JOIN dbo_Vehicle ON qryAVLLatestUpdate.TrackingObjectID=dbo_Vehicle.VehicleID) 
LEFT JOIN dbo_VehicleList ON dbo_Vehicle.VehicleID=dbo_VehicleList.VehicleID) 
LEFT JOIN dbo_Crew ON dbo_VehicleList.CrewID=dbo_Crew.CrewID) 
LEFT JOIN dbo_EmployeeList ON dbo_Crew.CrewID=dbo_EmployeeList.CrewID) 
LEFT JOIN dbo_Employee ON dbo_EmployeeList.EmployeeID=dbo_Employee.EmployeeID
WHERE (((dbo_Crew.CrewName) Not Like "*On Call*"))
ORDER BY dbo_Vehicle.VehicleName;

As you can see, these are rather nasty queries. What I need is a way to either combine them into a single query, or to somehow run one against the other. Any help/advice would be greatly appreciated! Thanks for your time!!

--
Dan

nonito
Starting Member

8 Posts

Posted - 2008-08-27 : 09:48:13

I just had the same problem, it was much easier figuring out what the output had to be. Start from scratch and just ignore Access queries.

danodemano
Starting Member

4 Posts

Posted - 2008-08-27 : 10:01:36

I was afraid that you would say that... :/

The reason I have been avoiding doing so is that I didn't write that, and I really don't know that much when it comes to MSSQL. Really I know just enough to be dangerous. I have done a fair amount of work in MySQL but very little in MSSQL.

I did actually start down that path though and didn't make it very far, though I didn't have a lot of time that last time I tried. It's the joins that always get me though, I can handle one or two, but I believe to make this work, I need to have something like 7 and at that point, it just goes way over my head.

--
Dan

danodemano
Starting Member

4 Posts

Posted - 2008-08-27 : 13:05:46

I've been messing around a bit with it here the past 2 hours or so, though I still can't seem to actually get it to work correctly. I can manage to return all the fields that I want, but the part where I'm running into problems and now seeing why it was done the way that it was is because of the MAX that's used in the first query. These table all have many thousands of records, but what I need is just the LATEST record one for each of the 27 different truck numbers. Pardon my Access use, but I wanted to get this to work there first before I tried to make it work against SQL server. This is what I have though:


SELECT dbo_TrackingCoord.TrackingObjectID, dbo_Vehicle.VehicleName, 
IIf([dbo_crew.isactive],[dbo_Employee.name],Null) AS Employee, 
Max(dbo_TrackingCoord.EventTime) AS MaxOfEventTime1, dbo_Vehicle.ContactInfo, dbo_TrackingCoord.Lat, 
dbo_TrackingCoord.Lon, dbo_TrackingCoord.Speed, dbo_TrackingCoord.Heading, dbo_TrackingCoord.Location,
dbo_TrackingCoord.EventType, dbo_VehicleList.CrewID, dbo_Crew.CrewName, dbo_Crew.IsActive, dbo_Vehicle.Type
FROM dbo_TrackingCoord LEFT JOIN ((((dbo_Vehicle LEFT JOIN dbo_VehicleList 
ON dbo_Vehicle.VehicleID = dbo_VehicleList.VehicleID) 
LEFT JOIN dbo_Crew ON dbo_VehicleList.CrewID = dbo_Crew.CrewID) 
LEFT JOIN dbo_EmployeeList ON dbo_Crew.CrewID = dbo_EmployeeList.CrewID) 
LEFT JOIN dbo_Employee ON dbo_EmployeeList.EmployeeID = dbo_Employee.EmployeeID)
ON dbo_TrackingCoord.TrackingObjectID = dbo_Vehicle.VehicleID
GROUP BY dbo_TrackingCoord.TrackingObjectID, dbo_Vehicle.VehicleName, IIf([dbo_crew.isactive],[dbo_Employee.name],Null), 
dbo_Vehicle.ContactInfo, dbo_TrackingCoord.Lat, dbo_TrackingCoord.Lon, dbo_TrackingCoord.Speed, dbo_TrackingCoord.Heading, 
dbo_TrackingCoord.Location, dbo_TrackingCoord.EventType, dbo_VehicleList.CrewID, dbo_Crew.CrewName, dbo_Crew.IsActive, 
dbo_Vehicle.Type, dbo_TrackingCoord.EventType
HAVING (((dbo_Crew.CrewName) Not Like "*On Call*") AND ((dbo_TrackingCoord.EventType) Not Like "*Moved by DisSPatch Client*"))
ORDER BY dbo_Vehicle.VehicleName;

The problem is that the above query will return some 40,000 records. Like I said, I just need the most resent for each vehicle, not the entire history. I'm sure that I am missing something simple here.

--
Dan

danodemano
Starting Member

4 Posts

Posted - 2008-08-27 : 13:50:17

Well, I think I actually solved my own problem, though I don't know if it's the best solution or not. I just ran the first query, dumped it into a temp table, then ran the second joining to that table. My query times have gone down SIGNIFICANTLY from 5ish seconds to .025 seconds. In fact, it's so fast, I can hardly even see the page refresh! Unless there is some downside that I am not aware of, I think I will just leave it like this.

--
Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources