Mark Breen
marklbreen at gmail.com
Mon Mar 22 14:27:03 CDT 2010
Hello John, Just FYI, [Following comments are for information and not intended to be for scalable systems or high volumes of users] an ODBC linked table, over the internet, to MySQL or MS SQL works fantastically fast. At times you would not realize that it was a linked table via OBDC, even when that is a shared server in a hosting company with 100 other people on the same box. Using a Jet Query can give fantastically fast results and criteria such as Where OrderID = x works super. Doing a join from Customers to Orders works well enough but can be slower. However for the average user a join of two tables might be plenty fast with data coming back in 1-2 seconds. Doing a join from Customers to orders to Order Details starting to become slow. In that case, you can create a View then you "Select * from CustomersOrdersOrderItems" where Order Id = x. Not surprisingly this is fantastically fast again. So what is my point? Do not assume that linked tables and jet based queries are all bad, they are good enough for normal users to use and you only need server based queries when they become more complex. I hope that is useful for you, you should try it sometime and you will be surprised to see how fast "dirty linked tables" can be. HTH, Thanks Mark On 22 March 2010 17:58, jwcolby <jwcolby at colbyconsulting.com> wrote: > Mark, > > I thought it was slow through SSMS as well. However I just went back in to > look and it is pretty > much instantaneous displaying the top (1000) records for any table. > > The database files are on my local laptop. The laptop is a DELL (Intel) > dual core with 4 gigs of > RAM. The hard drive is an upgrade to 7200 rpm. > > Everything was running fine. I had SQL Server 2005 Express services > running on the laptop and > installed 2005 SSMS to be able to see it from SSMS if needed. > > I never had any issues until I switched to 2008. I upgraded my SERVER to > 2008 and suddenly was > unable to get at the databases on the server. Thinking back on it that was > suspicious since I would > think that 2005 SSMS should be able to see and talk to 2008 server > instances. > > I wasn't really thinking deeply about that though. I just decided that > 2008 was running great on > the server, let's install on my laptop as well. When I tried, it > complained during install about > the Express stuff. I just decided to completely uninstall everything SQL > Server and reinstall from > scratch with 2008. That in fact worked quite well. > > So it seems that maaaaybe the issue could be ODBC access to the 2008 stuff > from Access 2003. The > interface is still pretty darned slow. > > I think life is prodding me to learn how to bind forms and combos to stored > procedures out on SQL > Server. I actually moved to SQL Server exactly to allow me to learn this > stuff here at my office on > a real system, then I never got around to it. > > John W. Colby > www.ColbyConsulting.com > > > Mark Breen wrote: > > Hi John, > > > > So it is SQL Server itself or the billing db? > > > > Are other db's also slow? If it is just billing then you need to look at > > indexes etc, and as Bobby is suggesting, you may want to use the wizard > to > > create a daily maintenance plan, that will re-build the indexes on > specified > > databases. I always include a daily maintenance plan on any transaction > > based db's I look after. > > > > However, if it is machine and OS / installation related, that is all > > irrelevant and you need to look at firewall, AV or some other machine > > related issue. I would plum first for a careful complete removal of SQL > and > > another install in that case. I would turn off AV/ Firewal and anything > > else that might effect it (Spyware software). > > > > Let us know which it is, I recently upgraded a few machines to sql 2008 > and > > would be keen to hear how you get on. > > > > Also, is it slow in SSMS or just through the GUI that you are using? > IOW, > > could it be ABO or ODBC related? > > > > Mark > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >