CODEWITHSUNDEEP
c#sql-serverindexing
Mironline
Mironline

Reputation: 2799

Question about database design

I'm working for real-estate company , we are about to develop new version of our windows application with C#.

Here is the abstract of current situations :

We have got 4 million record and increasing ,we use SQL Server 2005 to store these records in one table with 52 column. Almost all end users use at least 30 to 40 column on each search .

I know that it is not standard design , But I've tried many Scenario, I've split these 52 column to other tables and made the relation between theme , but the performance is still better when using one table ( even without primary key !) I've added the index ,but it is not logical to add index to all columns.

We have got other limitation, the users hardware ,many of them still have got Pentium II.

In the other side, we have got the Google Desktop Search GDS . I've tested this application on their computer ,performance is still good.

What is the difference between SQL server and GDS engine?

Is it possible to use GDS like engine to storing my data? And what is the name of these kind of storing ?

Upvotes: 1

Views: 120

Answers (3)

Lucero
Lucero

Reputation: 60236

GDS and SQL are not alike. However, SQL Server has (as an optional component) a feature called Full-Text Search, which may help achieve what you need.

In general, I guess that the following could be a good solution:

  • Normalize your database - if you didn't get better performance with a normalized DB then you quite certainly didn't have the proper primary and foreign keys set.
  • Use the mentionned FTS on text fields which need to be searched

Upvotes: 0

Daniel B
Daniel B

Reputation: 2887

Is it possible to use GDS like engine to storing my data ? and what is the name of these kind of storing ?

Yes, these are broadly referred to as NoSQL, and there are dozens of different "databases" that specialise in non-relational data storage.

Having said this, in the greater scheme of things 4 million records is not even a lot, it's almost certainly the design of your database that is at fault here. There are very few cases where a single table design is the fastest, engines such as SQL Server are very good at working with relational data. Have a look at discussions such as this one, and perhaps learn a bit more about database design and optimisation before you make any decisions.

Upvotes: 2

Tomas Jansson
Tomas Jansson

Reputation: 23472

It really depends on how you are applying your indexes. Also, you could have one set of tables that you use for your application and one set that you uses for reporting. That way you can increase the performance for reporting and still have your data correct. So everytime you get an update to your relational data structure you have a process that takes that data and migrate it to your database as well which is faster for querying.

Upvotes: 1

Related Questions