your opinion on the following probem/situation

Results 1 to 2 of 2

Thread: your opinion on the following probem/situation

  1. #1
    Join Date
    Dec 1969

    Default your opinion on the following probem/situation

    Hi,<BR><BR>below I have sketched a situation by architecture has to be capable of.<BR>the things i would like to know are:<BR>1) what techniques allow me to do the following queries in the fastest possible time?<BR>2) what do certain dbms offer to further enhance the speed of this particular situation<BR>3) is it even possible with today&#039;s systems. (Because i think it&#039;s some heavy stuff)<BR><BR>situation:<BR>a dbms has about 1,000,000 items A (not object-oriented, but in the relational sense)<BR>Each item A can have a relationship with upto 200 items B<BR>Each item B can have a relationship with upto 50 items C<BR>These relationships are all injective (meaning: an item B only belongs to one item A, and an item C only belongs to one item B and transitive one item C only belongs to one item A)<BR><BR>the most important select-queries are of the following form:<BR><BR>1)a subset S of all items A must be partitioned in subsets a1,a2,..,aN according to the value of an item C. If an item A doesn&#039;t contain that particular item C, that item must be ignored.<BR><BR>2)starting with a subset of all items A, select those items A for which a particular contained item C has a particular value. (Obviously, the resultset is again a subset of all items A)<BR><BR>the system has to be capable of processing about 10,000 of these queries simultaniously (through the internet). A search of about 2 seconds is acceptable.<BR><BR>There is nothing known in advance which items are selected, so the select-queries are all ad-hoc queries. <BR><BR>I wanted to tackle this situation (i have NO knowledge of databases in practise, only know something about it in theory) with 10,000 indexes with upto 1,000,000 rows. Each row being a 2-tuple of the form: (itemAiD,itemCValue) Is this possible? or are there better solutions?<BR><BR>item C is just a value:boolean,string,int etc.<BR>item B is a collection of items C (not important here) item B is nessecary for other operations.<BR><BR>At the same time items A,B and C will be added and must be taken into account also. this may happen with a slight delay (+/- upto 1 hour.)<BR><BR>I&#039;ve read that SQL Server, (and i think Oracle and IBM also) have the opportunity to have a write only server, feed it&#039;s new data continuously to a couple of read only servers. Is this a solution to the problem, or are other, better alternatives available?<BR><BR>I hope someone can help me get started by answering these questions? if something is unclear, please ask and i will try to make it so.<BR><BR>tia,<BR><BR>Tjeerd<BR>The Netherlands

  2. #2
    Join Date
    Dec 1969

    Default Fairly simple -- User SQL

    For example...<BR><BR>TableA<BR>-----------------------------<BR>int_AID -- Counter<BR>txt_Username -- varchar (15)<BR>txt_password -- varchar (15)<BR><BR>TableB<BR>-----------------------------<BR>int_BID -- Counter<BR>int_AID -- Numeric (Foreign key)<BR>txt_ItemBname -- Varchar (15)<BR>txt_itemBPrice -- Numeric<BR><BR>TableC<BR>-----------------------------<BR>int_CID -- Counter<BR>int_BID -- Numeric (Foreign Key)<BR>byt_istrue -- BIT (Yes/No -- True/False)<BR><BR>Then you can do queries like this...<BR><BR>select * from TableA where int_AID in (select int_AID from TableB where int_BID in (select int_BID from tableC where bytistrue=true)))<BR><BR>You can concatinate this string with other strings to make your query ad-hoc like this:<BR><BR>sql = "select * from TableA where int_AID in (select int_AID from TableB where int_BID in (select int_BID from tableC where bytistrue=" & request("isittrue") & ")))"<BR><BR>This will replace "request("isittrue")" with the value of the isittrue form element from the requesting form.<BR><BR>This query is not THAT intensive, but it can, if the amount of data grows larger, become a sort of a resource hog. But, for what it sounds like you are doing, this would work well.<BR><BR>-- Whol

Posting Permissions

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