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.
| Author |
Topic |
|
qwertyjjj
Posting Yak Master
131 Posts |
 Posted - 2007-10-15 : 10:15:52
|
| We have an Access FE that connects to SQL server.I was runnig a trace on the server and it seems there are multiple instaces of code like this:exec sp_execute 5, '0090976815'literally hundreds / thousands of entries. Is Access doing something weird here? I noticed in the code there are some DLookups, which might cause problems.Is the sp_execute likely to be linked to table scans or something? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-15 : 10:58:55
|
| It will be linked to a query plan (number 5 in fact). '0090976815' is the value sent to it.You should see a sp_prepare or something like that which will show you the query being executed with parameters (one).That will be before the first sp_execute.It's due to access loop through a linked table (probably) running a query on each row. Very slow if the table is big and a common feature of databases that are upsized to sql server without thought.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
|
|
qwertyjjj
Posting Yak Master
131 Posts |
Posted - 2007-10-15 : 11:14:25
|
| Could Dlookup be the cause?...or just general access looping through the table because it doesn't know what index to use etc.? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-15 : 11:34:09
|
its not so much that it doesn't know what index to use (SQL Server will decide that automatically), its that Access is more of an Isam system than a Relational one. So Access is good at things like "Whats the next row in 'walking order'" and "Display 20 rows in a grid and allow scrolling and editing", which are not very easily converted to "set based" thinking, so Access front end and SQL Backend (e.g. after an Upsize) is going to have lots of "Loop round to get 20 rows" type logic, and probably also lots of "And hold the cursor open in case the user scrolls"  Kristen |
|
|
|
|
|
|
Profiler - lots of sp_execute