[DUG] Migration from IBX to Interbase to SQL-Server 2005

[Paul McKenzie]

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: delphi-bounces at ns3.123.co.nz [mailto:delphi-bounces at ns3.123.co.nz]
> 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
>>Delphi at ns3.123.co.nz
>>http://ns3.123.co.nz/mailman/listinfo/delphi
>>
>>
> 
> 
> _______________________________________________
> Delphi mailing list
> Delphi at ns3.123.co.nz
> http://ns3.123.co.nz/mailman/listinfo/delphi
> 
> 
> 
> _______________________________________________
> Delphi mailing list
> Delphi at ns3.123.co.nz
> http://ns3.123.co.nz/mailman/listinfo/delphi
> 
>