Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Linked Server Connection to Sybase

    Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.

    Any ideas???

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did I mention I hate sybase...

    Just had a project thrown at me...never touched it before...

    I know there "cousins", but man is it quirky...

    Saw a developer do

    SELECT Col1, MAX(Col2), SUM(Col3)
    FROM Table
    WHERE Col1 = 'A'


    AND IT RAN!

    I couldn't believe it...and it gave back complete garbage....

    Can you post your statement?

    I'll try and link that server up and see what happens...

    (No wonder they lost market share)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    BA, Argentina
    Posts
    39

    Re: Linked Server Connection to Sybase

    Hi, I had a similar problem with ASE6, using a linked server in SQL server 2000.
    The problem was I had a very poor performance (10 secs per query), and I had to make an update in 200,000 regs using that query for every record... also I had problems in my linked server too (it worked when it wanted, i never discovered the cause...)

    My solution was very desesperate: instead of connecting SQL server to the ODBC linked to the ASE server, I made a program in Visual Basic with a connection to the ODBC. I wrote the update procedure with ADO.
    The results: the whole update was finished in 15 secs (remember: in SQL server each query took 10 secs, multiplied by 200,000 records...).

    I dont understand why this happens, a SQL server should be faster accessing data behind an ODBC than a VB program...

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    Here's the SQL:

    select *
    from [32tlsql2-dreamdb].dreamdb.dbo.drt_contract
    where i_con_contract = '000Q019999'
    and c_company = 'P00'
    and c_mkt_division = 'IPD'

    Here's the table DDL (please don't make jokes about the crappy design of the table. Unfortunately I got stuck with it):

    /****************** CREATE ORDER ******************/
    create table dbo.drt_contract
    (
    i_client numeric(10,0) not null ,
    i_con_contract char(10) not null ,
    c_company char(3) not null ,
    c_mkt_division char(3) not null ,
    c_level numeric(2,0) Default 1 not null ,
    c_interface_company char(6) null ,
    c_form char(8) not null ,
    c_con_contract_type char(2) not null ,
    c_agent_type char(3) null ,
    c_con_status char(2) null ,
    d_final_disposition datetime null ,
    d_lifecom_updated datetime null ,
    c_lifecom_status char(1) null ,
    d_con_terminate datetime null ,
    d_con_da_report_terminate datetime null ,
    d_con_signed datetime null ,
    d_con_effective datetime null ,
    c_con_line_of_business char(1) null ,
    i_con_rsm char(10) null ,
    c_con_publicity char(1) null ,
    c_con_uw_eligiblity char(1) null ,
    c_con_premium_remission char(1) null ,
    q_con_years_experience char(1) null ,
    c_con_pay char(2) null ,
    c_con_report char(2) null ,
    c_con_review char(1) null ,
    c_con_additional_aa_trailer char(8) null ,
    x_con_text varchar(250) null ,
    c_con_terminate_reason char(2) null ,
    f_con_assign_commissions char(1) null ,
    n_con_assign_commissions varchar(50) null ,
    i_con_financial_owner char(10) null ,
    c_con_financial_owner_level char(1) null ,
    i_con_address_owner char(10) null ,
    c_con_address_owner_level char(1) null ,
    c_con_mail char(1) null ,
    c_con_hire_type char(2) null ,
    c_con_change_type char(2) null ,
    c_zipcode_processing char(1) null ,
    n_con_sub_name varchar(30) null ,
    d_con_nasd_hire datetime null ,
    c_con_nasd_status char(2) null ,
    i_con_alt_rep1 char(10) null ,
    i_con_alt_rep2 char(10) null ,
    i_con_alt_rep3 char(10) null ,
    i_con_branch char(10) null ,
    c_con_rep_class char(2) null ,
    i_con_old_rep_nbr varchar(10) null ,
    i_con_old_branch char(10) null ,
    i_user_last_change char(8) null ,
    h_last_change datetime null
    )
    Lock Datarows on "default"
    go


    /****************** ALTER ORDER CONSTRAINTS ******************/
    alter table dbo.drt_contract
    add constraint pk_contract primary key clustered ( i_con_contract asc ,c_company asc ,c_mkt_division asc )
    go
    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_i_client on dbo.drt_contract ( i_client asc )
    go

    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_c_con_status on dbo.drt_contract ( c_con_status asc )
    go

    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_i_con_financial_owner on dbo.drt_contract ( i_con_financial_owner asc )
    go

    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_i_con_address_owner on dbo.drt_contract ( i_con_address_owner asc )
    go

    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_c_mkt_division on dbo.drt_contract ( c_mkt_division asc )
    go

    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_i_con_alt_rep3 on dbo.drt_contract ( i_con_alt_rep3 asc )
    go

    /****************** CREATE INDEX ******************/
    create Nonclustered index ix_c_company on dbo.drt_contract ( c_company asc )
    go

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You want crappy design?

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26547

    It looks like it should use the clustered index...but it still has to go to the datapage because of SELECT *

    Which, in an application aint a good idea...do you need all of the columns?

    What data do you actually need?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    Yep, you're right but I'm doing some quick testing so I didn't want to type all of the column names. The select * won't be used by any application or process. Anyways, I've specified the all of the column names and also only the 5-6 columns that I care to review and it still executes in the same amount of time. Oh yeah, Sybase nor Microsoft will provide support for OLEDB linked server connections to Sybase...SWEET

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't know if sybase has index intersection, but did you try and create a covered index with the 6 columns that you need

    Sybase should at least use that index.....

    Or, can you create a sproc on sybase and call that?

    My guess is that it's dragging way mopre data across than it needs to and sql server is figuring out the final results....

    I'm not sure...

    Gotta read up on how linked servers manage the data....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    The SQL statement only returns 1 row. So you think that a larger set of data is being sent to SQL Server and it has to narrow the set down to the final row? Hmm... remember I'm only encountering the problem when I use a string in the where clause. Integers work fine. I'll let you know how the covered index works.

  9. #9
    Join Date
    Sep 2003
    Posts
    364
    Here's the solution, use openquery() to execute the sql.

    select * from openquery(linked server connection, sql stetament)

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks for the info!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •