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

Wed May 31 12:02:38 NZST 2006

DBX in D2006 appears great for what we want... fast CRUD + a few other bits and pieces!
We will still investigate further, but speed-wise it beats ADO.

Regards
Paul McKenzie
Wellington
New Zealand

Neven MacEwan wrote:
> Paul
> 
> Originally (D6) there was no dbx driver for ms sql (big eff up on 
> borlands part inho).
> 
> Neven
> 
> Paul McKenzie wrote:
> 
>> I have been playing with the ADO options ... Returning small 
>> ResultSets - 200-250 records I can get the speed to equal DBX (not 
>> better) but when the ResultSets increase ADO gets Slower compared to DBX!
>>
>> I am asking why would anyone go with ADO over DBX ?
>>
>> Regards
>> Paul McKenzie
>> Wellington
>> New Zealand
>>
>>
>> Neven MacEwan wrote:
>>
>>> 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
>>>>
>>>>
>>>
>>> _______________________________________________
>>> 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