[DUG] MSSQL with D2007

Wed Jan 20 09:58:59 NZDT 2010

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 <gary at benner.co.nz> 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 : delphi at delphi.org.nz
>
> >CC :
>
> >From: John Bird, johnkbird at paradise.net.nz
>
> >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
>
> > peter.joint at embarcadero.com
>
> > Office: +61 (3) 9018-7752 | Fax: +61 (2) 8296-5831 | Mobile: +61 (404)
> 646 486
>
> >
>
> > Linked in: http://www.linkedin.com/in/peterjoint
>
> >
>
> >
>
> >
>
> > From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz]
> 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: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz]
> 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: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz]
> 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: delphi at delphi.org.nz
>
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
>
> > Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>
> >
>
> >
>
> >_______________________________________________
>
> >NZ Borland Developers Group - Delphi mailing list
>
> >Post: delphi at delphi.org.nz
>
> >Admin: http://delphi.org.nz/mailman/listinfo/delphi
>
> >Unsubscribe: send an email to delphi-request at delphi.org.nz 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:* gary at benner.co.nz
>
> *Skype:* garybenner
>
>
>
> Ref#: 41006
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>

-- 
Kyley Harris
Harris Software
+64-21-671-821
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20100120/b8349c53/attachment-0001.html 