Paul
Everybody has had a play with ado to get it to perform.
The Elephant stand on your foot? Play with the CursorLocation and
CursorType, definately avoid dynamic and keyset, I use Client/static
(which is a where ado caches all the data locally and doesn't refer to
the server until updates are applied), keyset caches the pk's (or what
it thinks is the pk) locally and dynamic fetches each row off the server
as you move about and both show serverside (or other user changes)
I'd play with forward only cursors and the recommendation for any large
ms sql app s to have the cursor management done client side
HTH
Neven
Paul McKenzie wrote:
I have just finished some preliminary testing...
D2006-Ent Win32 Database access to MS SQL-Server 2005
Client: DBGrid displaying all records from TDataSource from TClientDataSet
SocketServer Connection
ADO
- AppServer: TDataSetProvider accessing TSQLQuery connecting to
TSQLConnection
DBX
- AppServer: TDataSetProvider accessing TADOQuery connecting to
TADOConnection
[The only differences being TADOQuery/TADOConnection vs
TSQLQuery/TSQLConnection with default settings]
SQL joining 3 tables returning 100,000 rows approx:
The result:
- The DBX version took approx 3.8 seconds
- The ADO version took approx 47.6 seconds (Server CursorLocation -
Client @ ~128 seconds)
SQL joining 2 tables returning 225 rows approx:
- The DBX version took approx 0.156 seconds
- The ADO version took approx 0.265 seconds
Has anyone else looked into the performance difference ?
Know if this is normal between ADO and DBX ?
Regards
Paul McKenzie
Wellington
New Zealand
Kyley Harris wrote:
Hi Paul.
My software is middle tiered, but perhaps not the same way. I use code
generators, and object layer mapping to the database, So all my stored
procedures were easy to change via the generator. I use the database
purely as datastorage, not as a business logic processor, so all my
business logic is just Delphi code. Also, I don't actually use a
TDataset, or TIBQuery directly in my middle tier. That is also wrapped
up so that I never have to do a big search and replace of that type of
object when I change things. My wrapper is a limited to a subset of what
TDataset has that allows me to fulfill my needs, which is retrieve
one-direction data rows.
The only real issue, as neven mentioned, would be migration of stored
proc sources, and any select statements that refer to joining etc. SQL
Server has a lot more power in what you can do with SQL, but the syntax
is a wee bit different for some things.
One amusing thing I found, which may or may not be part of a spec, is
that some SQL that would return EOF and null in IB because of a wrong
sql statement would work correctly in SQL server. It seemed to intuit
what I wanted out of sql statements better with the way it joins data
etc.
The other main difference is Generators don't exist in MSSQL. You have
AutoIntegers, but they don't really do the same thing. I could not find
any way to do anything like, genid() in MSSQL, and found a handy snippet
of code that makes an isolated transaction on a table of keys via a
stored procedure to replicate the genid() and generator functionality.
Hope that helps.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Paul McKenzie
Sent: Monday, 29 May 2006 3:27 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Migration from IBX to Interbase to SQL-Server 2005
How easy/quick was the migration from IBX - much involved, or wast it
just replacing the TIBQueries and touching up the SQL ?
Regards
Paul McKenzie
Wellington
New Zealand
Kyley Harris wrote:
Hi Paul.
I just migrated and started using components from
http://www.crlab.com/
Go have a browse. They have many options.
There is only one pain in the ass so far. They said they support SQL
2005 server. And they do.. partially. No VarBinary(max) support yet.
Still. Worth looking at. The SDAC controls connect to OLEDB directly.
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
--
Neven MacEwan (B.E. E&E)
Ph. 09 620 1356 Mob. 027 4749 062
New Address Details
===================
MWK Computer Systems
1 Taumata Rd
Sandringham
Auckland
Ph 620 1356
Fx 620 1336
begin:vcard
fn:Neven MacEwan
n:MacEwan;Neven
email;internet:[EMAIL PROTECTED]
tel;work:649 620 1356
tel;fax:649 620 1336
tel;cell:0274 749 062
version:2.1
end:vcard
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
