[DUG] MSSQL with D2007

Wed Jan 20 18:31:11 NZDT 2010

Gary/John

Firstly, Have you compared the 'load time' for the query from ADO versus 
Query Analyser? If they are the same (QA is odbc based) then the perf 
issue is Server/Query, if not then
its the ADO, check the ADODataSet (or equiv IIRC these are all wrappers 
around tha ADO Command object) , make sure you use CursorLocation = 
clUseServer and CursorType = ctOpenForwardOnly, otherwise you are 
possibly loading all the data into a client com based dataset and then 
pulling it from there (double handling) and for your purposes LockType 
could be read only.

Another thing NEVER EVER use * in a query, if you do you deserve all the 
shit you bring down on yourself.

My choice if I was starting again would probably be kbmMemtable and some 
TDataset OBDC wrapper, Memtable has its own custom 'resolver' class, 
I've found all the others problematic.

FWIIW I just loaded  819984 in 68 sec in QA on my loocal machine (12K 
rows per sec) off a single table,

select top 10000 * from
ApplicatorAction a JOIN
Applicator b ON b.ApplicatorID = a.ApplicatorID
order by rate, Note

loaded 10,000 in 4 secs, 2,500 per sec, NOTE 100,000 takes many minute 
so the degradation is non-linear, Lesson keep your results sets as small 
as poss

Have you run an 'execution plan' (its an option under 'Query' in QA), if 
you are having problem with tempdb blowing out it could be a  join build 
a disk based hash table (caused by an unindexed foreign key), you should 
generally aim for nessted loop joins and table index scan as this 
combination is more performance 'deterministic'

HTH
Neven

> *[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 <mailto: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