Unanswered: SQL Server trace logs for Access queries
We have many Access applications that read data from an SQL Server using linked tables.
Looking at the trace logs on the SQL server to try to identify poorly performing Access apps, and looking at the SQL that is acutually being executed, we have come across sql that appears to be using SQL parameters, and am trying to track these back to Access queries.
This traced query is being run hundreds of thousands of times, and is exactly the same.
eg - from SQL trace:
WHERE (col1 = @P1
AND col2 = @P2
AND col3 = @P3
AND col4 = @P4)
I don't think that any of the possible Access queries actually pass params to SQL server.
So the question is, what's happening - is Access creating it's own queries against SQL Server that are somehow parameterised?
I have found a query in the Access DB that seems similar to my trace query - ie it selects the same columns from the same table in the SQL Server DB, but it has no parameters. What it is doing is joining to an Access query, the join condition between the Access query and the SQL server query being on col1, col2, col3, col4 - ie same as passed as params into SQL server on the query that uses the join.
Also - the 1st query returns 1.5mil rows, and the table it's trying to join to has several hundred thousand rows.
So is it possible that Access, in trying to join the first query to the linked table is running the traced query for each row in the first query? And that it is creating a parameterised query to run many thousands of times?
I know how to optimise this process, but was just wondering if this Access likely to genereate it's own SQL server parameterised queries.