Expected hardware?  
Author Message
bigobely





PostPosted: Tue Dec 19 10:29:39 CST 2006 Top

SQL Server Developer >> Expected hardware?

Hi all!

I have uncommon question. I have SQL Server 2000 EE. I am working on
database that will contain contextual dictionary and that means storing
about a million words that are connected to about a thousand keywords.
My question now is this: What is expected hardware for this?

I have only 4 tables in key database:

- table that has unconnected words (id pk identity, word nvarchar(250))
(about 5-6 mil records)
- table that has connected words (id pk identity, word nvarchar(250))
(about 1-2 mil records)
- table that has keywords (id pk identity, word nvarchar(250)) (about
1000 records)
- table that has connection points (fk on connected words id, id pk
identity, conn_weight int, fk on keywords id) (about 20 mil records)

It will have to do at least 10-15 select statements a minute. Ofc it
will use only lower 3 tables in join select. It will work like this:

SELECT a.keyword, b.word, c.conn_weight FROM keyword_Table as a INNER
JOIN conn_points_table as c ON a.id = c.keywords_id INNER JOIN
connected_words as b ON c.connected_words_id=b.id WHERE a.keyword IN
(some list of words)

this data will be sent into temp table then this will be performed on it:

SELECT keyword, sum(conn_weight) as all_sum FROM temptable WHERE word in
(some list of words) group by keyword ORDER BY sum(conn_weight) desc

Other info:

"some list of words" may contain 500-5000 words. Should i send my list
of words directly by sql statement or store it into temp table? If store
it is better, which is the fastest way to store 5000 words in a table?

Thanks you all for at least reading this :D ...

SQL Server273  
 
 
AlterEgo





PostPosted: Tue Dec 19 10:29:39 CST 2006 Top

SQL Server Developer >> Expected hardware? lord,

Some comments:

1. The best way to determine your performance is to prototype your queries.
Load up your data, and run some tests.
2. 10-15 select statements a minute doesn't seem like much of a load on the
server. Even though you have millions of rows, proper indexing should
provide reasonable response times. What kind of response time are you
looking for? 2 seconds, 5, 10?
3. Use a READ UNCOMMITTED transaction level for your queries if you can. It
will mitigate locking contention.
4. Have you looked into full text search? It includes many keyword matching
benefits such as word stemming, plurals, etc. Check BOL.

-- Bill



> Hi all!
>
> I have uncommon question. I have SQL Server 2000 EE. I am working on
> database that will contain contextual dictionary and that means storing
> about a million words that are connected to about a thousand keywords. My
> question now is this: What is expected hardware for this?
>
> I have only 4 tables in key database:
>
> - table that has unconnected words (id pk identity, word nvarchar(250))
> (about 5-6 mil records)
> - table that has connected words (id pk identity, word nvarchar(250))
> (about 1-2 mil records)
> - table that has keywords (id pk identity, word nvarchar(250)) (about 1000
> records)
> - table that has connection points (fk on connected words id, id pk
> identity, conn_weight int, fk on keywords id) (about 20 mil records)
>
> It will have to do at least 10-15 select statements a minute. Ofc it will
> use only lower 3 tables in join select. It will work like this:
>
> SELECT a.keyword, b.word, c.conn_weight FROM keyword_Table as a INNER JOIN
> conn_points_table as c ON a.id = c.keywords_id INNER JOIN connected_words
> as b ON c.connected_words_id=b.id WHERE a.keyword IN (some list of words)
>
> this data will be sent into temp table then this will be performed on it:
>
> SELECT keyword, sum(conn_weight) as all_sum FROM temptable WHERE word in
> (some list of words) group by keyword ORDER BY sum(conn_weight) desc
>
> Other info:
>
> "some list of words" may contain 500-5000 words. Should i send my list of
> words directly by sql statement or store it into temp table? If store it
> is better, which is the fastest way to store 5000 words in a table?
>
> Thanks you all for at least reading this :D ...