[DUG] MSSQL with D2007

Wed Jan 20 09:34:22 NZDT 2010

How big are your rows? Still sounds painfully slow to me unless there are
decent sized blobs in each row.

Neven has a good point about cursor type too although I'm not sure any of
the options should be that slow. I could do some proper benchmarking if you
wanted but I know we definitely load at least a thousand small-medium sized
records a second (probably more) over a 100mbs network, and that is often
into a client dataset too.

David.

From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of John Bird
Sent: Wednesday, 20 January 2010 12:42 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL with D2007

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. |  <http://www.embarcadero.com/>
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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20100120/d22278aa/attachment.html 