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

Tue May 30 17:06:18 NZST 2006

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: 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
>>
>>
> 
> _______________________________________________
> Delphi mailing list
> Delphi at ns3.123.co.nz
> 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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: neven.vcf
Type: text/x-vcard
Size: 164 bytes
Desc: not available
Url : http://ns3.123.co.nz/pipermail/delphi/attachments/20060530/f17d7c16/neven.vcf