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

Wed May 31 10:58:15 NZST 2006

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
> 
> 

-- 
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/20060531/74babe53/neven-0001.vcf