I'm really stumped on this problem, and if any of you can help me with this, you're a genius.
My work environment is an Access 2003 ADP file, which is reading tables/views on a SQL Server 2000 database.
In a nutshell, I've created a view that has a listing of details for each bus in our fleet(it's capacity, video system, parking location etc). This view is generated by combining several other views together. When I run it via Enterprise Manager(either locally or remotely), it takes a few seconds. When I run this query via my Access ADP, it takes about 10 seconds. No problem considering all of the hoops the data has to jump through.
Now here's the problem. The location of each bus is coded as a number, so I included the "CASE" function in the select statement, in order to translate that into something everyone will understand.
When I run this "case" query via enterprise manager, it only takes a few seconds, but when I run it via ADP, it takes 2 minutes. Not only that, but the NETWORK activity on the server goes beserk(40% utilization). Though, that is not the ADP client, so I'm not sure what that traffic is going, or coming from. I did SQL tracing on the server, and indeed, it's spinning out of it's mind trying to process the query from ADP.
I've tried moving the "case" from the end query, to the subview that finds the buses location, but that didn't help. How could running the same exact query via ADP take so much longer than running it via Enterprise Manager?
My work environment is an Access 2003 ADP file, which is reading tables/views on a SQL Server 2000 database.
In a nutshell, I've created a view that has a listing of details for each bus in our fleet(it's capacity, video system, parking location etc). This view is generated by combining several other views together. When I run it via Enterprise Manager(either locally or remotely), it takes a few seconds. When I run this query via my Access ADP, it takes about 10 seconds. No problem considering all of the hoops the data has to jump through.
Now here's the problem. The location of each bus is coded as a number, so I included the "CASE" function in the select statement, in order to translate that into something everyone will understand.
When I run this "case" query via enterprise manager, it only takes a few seconds, but when I run it via ADP, it takes 2 minutes. Not only that, but the NETWORK activity on the server goes beserk(40% utilization). Though, that is not the ADP client, so I'm not sure what that traffic is going, or coming from. I did SQL tracing on the server, and indeed, it's spinning out of it's mind trying to process the query from ADP.
I've tried moving the "case" from the end query, to the subview that finds the buses location, but that didn't help. How could running the same exact query via ADP take so much longer than running it via Enterprise Manager?
