<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor=white lang=EN-NZ link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>How big are your rows? Still sounds painfully slow to me unless
there are decent sized blobs in each row.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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 <b>at least</b> a thousand
small-medium sized records a second (probably more) over a 100mbs network, and
that is often into a client dataset too.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>David.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>John Bird<br>
<b>Sent:</b> Wednesday, 20 January 2010 12:42 a.m.<br>
<b>To:</b> NZ Borland Developers Group - Delphi List<br>
<b>Subject:</b> Re: [DUG] MSSQL with D2007<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal><span lang=EN-AU style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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.</span><span lang=EN-AU><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>The slowness was the main reason I wanted to check DBExpress or
simpledataset to see how fast they were.</span><span lang=EN-AU><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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.</span><span lang=EN-AU><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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..</span><span lang=EN-AU><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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).</span><span
lang=EN-AU><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>Question 2 - How does ADO compare in speed to BDE/DBX etc?</span><span
lang=EN-AU><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-AU>John<o:p></o:p></span></p>
</div>
<blockquote style='border:none;border-left:solid black 1.5pt;padding:0cm 0cm 0cm 4.0pt;
margin-left:3.75pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt'>
<p class=MsoNormal><span lang=EN-AU style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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) <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-AU style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Arial","sans-serif";color:#333333'>Peter Joint</span></b><span lang=EN-US
style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#333333'><br>
</span><span lang=EN-US style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#333333'>Embarcadero Technologies, Inc. | <a
href="http://www.embarcadero.com/"><span style='color:#333333'>www.embarcadero.com</span></a>
<br>
<a href="mailto:peter.joint@embarcadero.com">peter.joint@embarcadero.com</a><br>
Office: +61 (3) 9018-7752 | Fax: +61 (2) 8296-5831 | Mobile: +61 (404)
646 486<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#333333'>Linked in: <a href="http://www.linkedin.com/in/peterjoint">http://www.linkedin.com/in/peterjoint</a></span><span
lang=EN-AU style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p></o:p></span></p>
</div>
<p class=MsoNormal><span lang=EN-AU style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>Jeremy Coulter<br>
<b>Sent:</b> Tuesday, 19 January 2010 7:57 PM<br>
<b>To:</b> 'NZ Borland Developers Group - Delphi List'<br>
<b>Subject:</b> Re: [DUG] MSSQL with D2007<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><span lang=EN-AU><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>OR.....your DB server is just really slow </span><span
style='font-size:11.0pt;font-family:Wingdings;color:#1F497D'>J</span><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>We use the TADO controls, but were possible, or where I am not
being lazy, use non DB bound controls.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>jeremy<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>David Brennan<br>
<b>Sent:</b> Tuesday, 19 January 2010 21:19<br>
<b>To:</b> 'NZ Borland Developers Group - Delphi List'<br>
<b>Subject:</b> Re: [DUG] MSSQL with D2007<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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!)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>David.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> delphi-bounces@delphi.org.nz
[mailto:delphi-bounces@delphi.org.nz] <b>On Behalf Of </b>John Bird<br>
<b>Sent:</b> Tuesday, 19 January 2010 8:05 p.m.<br>
<b>To:</b> NZ Borland Developers Group - Delphi List<br>
<b>Subject:</b> [DUG] MSSQL with D2007<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>Using instead ADO (TADOConnection and TADOQuery) works fine, but
is slow - 7000 records takes 30-40 seconds to load.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>The rest of the components the same (ie TDatasetProvider,
TCLientDataSet, TDatasource) ie have been pointing the Datasetprovider at
either the TADOQuery or the TSQLQuery</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>Also tried the TSimpleDataSet, has the same error as the first
sentence.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>All the queries allow me at design time to set active to true
successfully, but still crash when the dataset is set to open....</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>Does anyone have any idea what could be going wrong?</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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.</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>John<o:p></o:p></p>
</div>
<div class=MsoNormal align=center style='text-align:center'><span lang=EN-AU>
<hr size=2 width="100%" align=center>
</span></div>
<p class=MsoNormal><span lang=EN-AU>_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: delphi@delphi.org.nz<br>
Admin: http://delphi.org.nz/mailman/listinfo/delphi<br>
Unsubscribe: send an email to delphi-request@delphi.org.nz with Subject:
unsubscribe<o:p></o:p></span></p>
</blockquote>
</div>
</body>
</html>