1

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 ?

Amir Ismail
  • 3,865
  • 3
  • 20
  • 33
Mironline
  • 2,755
  • 7
  • 35
  • 61
  • Would you be able to give sort of an example of your table and some queries? I understand you can't really just post your schema but if we had some way to see how the current data was arranged and then searched on, I bet you could get some more specific help on actually getting a design that works for you without having to do one big table. – shelleybutterfly Aug 14 '11 at 11:26

3 Answers3

2

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.

Community
  • 1
  • 1
Daniel B
  • 2,877
  • 18
  • 18
1

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.

Tomas Jansson
  • 22,767
  • 13
  • 83
  • 137
  • Tomas ,I didn't get the picture ,you mean I have to make the duplicate data in one database , one set (so I have to use some tables to storing instead of one table) for reporting and on set for working with application ? – Mironline Aug 14 '11 at 10:17
  • yes, that is right. Or you could use views in your database, but I'm not sure if they are faster in MSSQL. But using two sets of tables (or two databases) where the relational one will make sure your application data has the right state and the denormalized one will increase performance for querying. – Tomas Jansson Aug 14 '11 at 10:19
0

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
Lucero
  • 59,176
  • 9
  • 122
  • 152