I use VMWare exclusively and there is one occassion when SQL Server grinds to a halt and can go 100 times slower.
when running your query check the Total Available memory on the task manager. I have found that when the Available memory is less than 200mb AND/OR available HDD space on the computer is less than a 500MB (rough figures I know) on the computer running SQL server queries that normally take 1 second can take 20 or 50 or 100 seconds to perform. When I make sure there is plenty of ram, and disk, SQL server speeds up again. On Wed, Jan 20, 2010 at 9:44 AM, Gary T. Benner <[email protected]> wrote: > *[Reply]* > > HI all, > > In working with John on this one I did some research on available resources > for this task, and there seems to be a deafening lack of material available > online. As opposed to other Delphi related topics. > > Is this due to (a) this is not something that is straight forward ( ie fish > hooks abound), or (b) this is not something Delphi developers really want to > do ( they prefer other DB's ), or (c) are people keeping their solutions > close to their chest? > > Are the component sets included in Delphi up to the job? > > Are there other third party components that do a better job for the simple > Data Aware architecture? > > In John's case it may well be the VM has limited resources - it's all on > his laptop with 2M total, and Vista as the host may well be hogging memory > etc. > > Ideas, opinions appreciated. > > cheers > > Gary > > A*t 00:40 on 20/01/2010 you wrote * > > >To : [email protected] > > >CC : > > >From: John Bird, [email protected] > > >Content Type: text/html > > >Attached: > > > > > >This is a multi-part message in MIME format. > > > > > > > > > > > > > > > > > > > > >The DB server is as local as can be - ie in this case its on the same PC - > although in a VMWare workstation virtual PC. > > > > > >The slowness was the main reason I wanted to check DBExpress or > simpledataset to see how fast they were. > > > > > >Since then we decided to put an initial filter in the select SQL, and the > time taken to return data is definitely proportional to the number of > records - 30 records takes under a second, 700 2-3 seconds. This is > workable, although want faster. > > > > > >Question 1 - might the VMWare need tuning? With XP, MSSQL, D2007, and a > large dataset etc running may there some memory bottleneck issues with the > VM? Has 512MB assigned for VM, looks to be sufficient.. > > > > > >One trick I have read is to disconnect any datasource and/or controls > while data loads - this can make a huge difference when loading a large > dataset. (This was in the help for TKBMMemtable). > > > > > >Question 2 - How does ADO compare in speed to BDE/DBX etc? > > > > > >John > > > > > > I'd be looking to make sure that the DB Server itself isn't the > bottleneck here - quick and easy test, run the SQL select statement through > Query Analyser (yuck!) and see what sort of response time you get. > Alternatively, (and shameless plug) download a copy of DBOptimizer from our > website, point it at your server, and then get your application to hit the > server and see where the bottleneck is. DBOptimizer can also show you > network issues if it's that, and can also highlight if your client is > spamming a slow down message while it tries to digest the resultset. The > trial edition will give you all the results (it's evaluation time limited, > not feature limited) > > > > > > > > > > > > Peter Joint > > > Embarcadero Technologies, Inc. | www.embarcadero.com > > > [email protected] > > > Office: +61 (3) 9018-7752 | Fax: +61 (2) 8296-5831 | Mobile: +61 (404) > 646 486 > > > > > > Linked in: http://www.linkedin.com/in/peterjoint > > > > > > > > > > > > From: [email protected] [mailto:[email protected]] > On Behalf Of Jeremy Coulter > > > Sent: Tuesday, 19 January 2010 7:57 PM > > > To: 'NZ Borland Developers Group - Delphi List' > > > Subject: Re: [DUG] MSSQL with D2007 > > > > > > > > > > > > OR.....your DB server is just really slow J > > > > > > We use the TADO controls, but were possible, or where I am not being > lazy, use non DB bound controls. > > > > > > However I have heard the SDAC controls are meant to be pretty good as > Kyley seems to back up. Just a bit expensive from Memory. > > > > > > > > > > > > jeremy > > > > > > > > > > > > From: [email protected] [mailto:[email protected]] > On Behalf Of David Brennan > > > Sent: Tuesday, 19 January 2010 21:19 > > > To: 'NZ Borland Developers Group - Delphi List' > > > Subject: Re: [DUG] MSSQL with D2007 > > > > > > > > > > > > I should say there is something wrong with your ADO setup if it is taking > that long for 7000 records, unless they are VERY big records. > > > > > > > > > > > > 7000 records of 2-4000 bytes each should be only a second or so (don't > want to be more accurate in my estimate without testing, been a while since > I paid much attention to data transfer times be honest!) > > > > > > > > > > > > David. > > > > > > > > > > > > From: [email protected] [mailto:[email protected]] > On Behalf Of John Bird > > > Sent: Tuesday, 19 January 2010 8:05 p.m. > > > To: NZ Borland Developers Group - Delphi List > > > Subject: [DUG] MSSQL with D2007 > > > > > > > > > > > > Been trying to connect to MSSQL 2005 with DB Express (TSQLConnection and > TSQLQuery), but cannot get it to work, we are getting an access violation at > address 017E0913 in DBXMSS30.DLL read of address 01820000 > > > > > > > > > > > > Using instead ADO (TADOConnection and TADOQuery) works fine, but is slow > - 7000 records takes 30-40 seconds to load. > > > > > > > > > > > > The rest of the components the same (ie TDatasetProvider, TCLientDataSet, > TDatasource) ie have been pointing the Datasetprovider at either the > TADOQuery or the TSQLQuery > > > > > > > > > > > > Also tried the TSimpleDataSet, has the same error as the first sentence. > > > > > > All the queries allow me at design time to set active to true > successfully, but still crash when the dataset is set to open.... > > > > > > > > > > > > Does anyone have any idea what could be going wrong? > > > > > > > > > > > > And any recommendations of best combinations you prefer for MSSQL with > D2007. I am new to connecting D2007 to MSSQL so willing to do it whichever > way works best. > > > > > > > > > > > > John > > > > > > > > > > > > >------------------------------------------------------------------------------ > > > > > > > > > _______________________________________________ > > > NZ Borland Developers Group - Delphi mailing list > > > Post: [email protected] > > > Admin: http://delphi.org.nz/mailman/listinfo/delphi > > > Unsubscribe: send an email to [email protected] with Subject: > unsubscribe > > > > > > > > >_______________________________________________ > > >NZ Borland Developers Group - Delphi mailing list > > >Post: [email protected] > > >Admin: http://delphi.org.nz/mailman/listinfo/delphi > > >Unsubscribe: send an email to [email protected] with Subject: > unsubscribe > > > > Gary Benner MNZCS ITCP > ------------------------------ > > Semantic Limited <http://www.semantic.co.nz> - Online Education, > e-Commerce, Software Development & Systems Design > > 123 Internet Limited <http://www.123.net.nz> - Managed Web Hosting, > Virtualisation, High Availability Systems & Cluster Technologies > > *Mob:* 021 966 992 > > *DDI:* +64 7 543 1206 > > *Email:* [email protected] > > *Skype:* garybenner > > > > Ref#: 41006 > > > _______________________________________________ > NZ Borland Developers Group - Delphi mailing list > Post: [email protected] > Admin: http://delphi.org.nz/mailman/listinfo/delphi > Unsubscribe: send an email to [email protected] with Subject: > unsubscribe > -- Kyley Harris Harris Software +64-21-671-821
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [email protected] with Subject: unsubscribe
