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

[Paul McKenzie]

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