Professional Web Applications Themes

Tables across databases - Microsoft SQL / MS SQL Server

Hi all, In Sql Server 2000 I am having one master table in myMaster database and detail table in myDetail database as I can not have references across the databases, but I can implement logic in front end. I am having the mythird database where I am having these two tables. I wanted to know which will be optimized solution, which soln will give me best results? Please explain me Kishor -- Kishor Pise Hurix Systems Pvt. Ltd. 231 Solitaire Corporate Park, 151 Andheri Kurla Road, Andheri(E), Mumbai 400 093 Maharashtra, INDIA. Phone- +91-22-56923890 Fax- +91-22-28265948 www.hurix.com...

  1. #1

    Default Tables across databases

    Hi all,

    In Sql Server 2000 I am having one master table in myMaster database and
    detail table in myDetail database as I can not have references across the
    databases, but I can implement logic in front end. I am having the mythird
    database where I am having these two tables.



    I wanted to know which will be optimized solution, which soln will give me
    best results?



    Please explain me



    Kishor

    --
    Kishor Pise
    Hurix Systems Pvt. Ltd.
    231 Solitaire Corporate Park,
    151 Andheri Kurla Road,
    Andheri(E), Mumbai 400 093
    Maharashtra, INDIA.

    Phone- +91-22-56923890
    Fax- +91-22-28265948
    www.hurix.com


    Kishor Guest

  2. #2

    Default Re: Tables across databases

    Hi Kishor.

    I'm not 100% clear on the question you're asking here, but it sounds like
    you're asking whether to have:

    (a) master / detail tables in the same database, with foreign key (DRI)
    references between the tables

    (b) master detail tables in different databases with logic implemented at
    the application layer (front end)

    Keeping both tables (master / detail) in the same database would most likely
    be the better solution both in terms of efficiency AND accuracy. There are
    various problems with (b) - particularly in terms of accuracy if there are
    multiple users using the front end at once.

    I'm not too sure what question you're really asking here though.

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP

    "Kishor" <com> wrote in message
    news:phx.gbl... 


    Greg Guest

  3. #3

    Default Re: Tables across databases

    are all your databases on the same SQL 2000 instance? If on the same
    instance then generally the performance would be fine, no 2PC/DTC needs to
    be involved and the data cache is shared. On multiple instances you are
    likely to hit some performance issues (linked servers, DTC, optimizer, intra
    query traffic, etc) - I can not say more given lack of knowledge of what you
    are trying to achieve.

    btw you *can* implement references from one database to another by using
    computed columns in turn invoking user-defined functions which can go to
    another database (on same or a different SQL instance). But please note that
    such constraints will be uni-directional so you might have to define
    complimentary constraints on both databases. There is a white-paper on MSDN
    on this topic.

    Yuri (MSFT)

    "Kishor" <com> wrote in message
    news:phx.gbl... 


    Yuri Guest

  4. #4

    Default Re: Tables across databases

    Hi Kishor

    Please reply in the newsgroup so everyone can help you.

    When you said "implement logic in the front end", I assumed you meant
    referential integrity logic (but I couldn't be sure because your post was
    not clear).

    If your front end is going to perform the referential integrity checking (ie
    check that a record exists in the master table before inserting into the
    detail table) then the front end will need to hold locks against the
    database whilst it inserts detail rows (so that header rows are not deleted
    between the time it checked for them and the time it inserts the detail
    row/s). If the front end requests the server for these locks, the
    architecture will be slower than a server side solution & if it doesn't hold
    the locks the architecture will be less reliable (accurate) because other
    connections may delete header rows etc..

    I didn't want to elaborate too much on my answer because your post wasn't
    100% clear & I wasn't sure I even understood your question fully...

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP

    HI Greg,
    you are right, what you have written in a and b is correct and this is what
    I want to ask. But I still dont understand why there will be a problem of
    accuracy?.

    Kishor


    "Greg Linwood" <com> wrote in message
    news:phx.gbl... 
    likely [/ref]
    the [/ref]
    mythird [/ref]
    me 
    >
    >[/ref]


    Greg Guest

  5. #5

    Default Re: Tables across databases

    Hi Greg,

    Now I understand why u said there is problem of accuracy but in my case
    there will be almost no deletion in master table.

    question here is which solution will be faster for searching and sorting and
    Join queries as my both tables are going to have millions of rows.


    1 Having different databases for both master and detail

    2 having only one database for both tables.



    regards

    Kishor










    Kishor Guest

  6. #6

    Default Re: Tables across databases

    Hi Kishor.

    I think this question has already been answered by Yuri - the main issue
    will be whether the two databases are on the same instance of SQL Server. If
    so, then there's probably not much difference. I'd qualify this as assuming
    you're not using cross database ownership chaining though, which might
    introduce optimizer delays depending on your logon architecture.

    Regards,
    Greg Linwood
    SQL Server MVP

    "Kishor" <com> wrote in message
    news:#phx.gbl... 
    and 


    Greg Guest

Similar Threads

  1. tables vs databases
    By cronoklee@hotmail.com in forum MySQL
    Replies: 13
    Last Post: September 5th, 12:00 PM
  2. populating dropdown using tables from two databases
    By cfdyn in forum Coldfusion Database Access
    Replies: 6
    Last Post: August 20th, 06:51 AM
  3. QUERY two tables from different databases
    By WhozitsPop in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 25th, 03:15 PM
  4. Joining Tables Across Databases
    By Wilkinson, Marcus in forum Informix
    Replies: 3
    Last Post: October 31st, 11:04 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139