Latency between Access and SQL Server

1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User

Welcome to IWETHEY!

Post #186,310 by ben_tilly 12/7/04 5:13:12 PM Reply	Latency between Access and SQL Server I'll bet that Access is doing something stupid like fetching each row from SQL server locally in a synchronous way, so you have to wait for 22,000 "please gimme another row", "here's your row", "thank you" transactions. If that silly little round-trip takes .003s, then there's your minute. Note that when you attach tables through ODBC, Access cannot use any of the indexes on the query. It just fetches the data locally and does full table scans. As already suggested, look up passthrough queries and use them. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #186,317 by folkert 12/7/04 5:40:00 PM Reply	That is just WRONG. Cannot use indexes. That BLOWS. -- [link\|mailto:greg@gregfolkert.net\|greg], [link\|http://www.iwethey.org/ed_curry\|REMEMBER ED CURRY!] @ *i_wethey* No matter how much Microsoft supporters whine about how Linux and other operating systems have just as many bugs as their operating systems do, the bottom line is that the serious, gut-wrenching problems happen on Windows, not on Linux, not on Mac OS. -- [link\|http://www.eweek.com/article2/0,1759,1622086,00.asp\|source] Here is an example: [link\|http://www.greymagic.com/security/advisories/gm001-ie/\|Executing arbitrary commands without Active Scripting or ActiveX when using Windows]
Post #186,320 by ChrisR 12/7/04 5:53:08 PM Reply	Sometimes faster just to import the whole table and ditz with it locally from there. Access tends to go overboard on the locking as well, which can cause its own set of problems.

Why does something this simple, take soo long? - (folkert) - (21) - Dec. 7, 2004, 02:27:17 PM EST

Probably at the Access level - (JayMehaffey) - (1) - Dec. 7, 2004, 03:10:19 PM EST

Doesn't matter either way. - (folkert) - Dec. 7, 2004, 05:37:39 PM EST

Investigate Pass-Through Queries in Access -NT - (altmann) - (1) - Dec. 7, 2004, 04:35:46 PM EST

Thanks. I do that later. - (folkert) - Dec. 7, 2004, 05:39:12 PM EST

Latency between Access and SQL Server - (ben_tilly) - (2) - Dec. 7, 2004, 05:13:12 PM EST

That is just WRONG. - (folkert) - Dec. 7, 2004, 05:40:00 PM EST

Sometimes faster just to import the whole table - (ChrisR) - Dec. 7, 2004, 05:53:08 PM EST

I would ask for a showplan - (Simon_Jester) - (1) - Dec. 7, 2004, 05:25:51 PM EST

There is an osql - (altmann) - Dec. 7, 2004, 06:43:59 PM EST

The answer is in the first line of your post -NT - (tuberculosis) - (1) - Aug. 21, 2007, 06:34:31 AM EDT

Clever, cleaver, thine art. -NT - (folkert) - Dec. 8, 2004, 01:02:49 PM EST

Most questions that start, "why does Access......?" - (tablizer) - (9) - Dec. 22, 2004, 05:17:20 PM EST

That might change. - (inthane-chan) - (8) - Dec. 22, 2004, 05:38:40 PM EST

Not unless VB is integrated with SQL server - (ben_tilly) - (7) - Dec. 22, 2004, 06:28:24 PM EST

Yeesh! - (inthane-chan) - (4) - Dec. 22, 2004, 06:47:37 PM EST

That trick is actually useful..ugly, but useful -NT - (ben_tilly) - (3) - Dec. 22, 2004, 07:50:13 PM EST

The poor man's Control Tables -NT - (ChrisR) - (2) - Dec. 22, 2004, 07:57:27 PM EST

That's one thing to use it for -NT - (ben_tilly) - Dec. 22, 2004, 08:40:28 PM EST

Files systems are not much better. Trees too damned limited -NT - (tablizer) - Dec. 26, 2004, 03:24:20 AM EST

Perish the thought - (ChrisR) - Dec. 22, 2004, 07:06:12 PM EST

"In the Next Version" - (altmann) - Dec. 22, 2004, 08:01:26 PM EST

iwethey.org

That will get motion if the package maintainer is a sheep...
78 ms