Need help in creating Table

Question

Need help in creating Table

Dipesh Neema

SSC Rookie

Points: 28
More actions
November 29, 2010 at 11:26 pm

#229688

I have one table which will have around 15million records. I currently have 50 columns in it.
I have full text search enable on 6 columns. I need to use order by and group by also in the query.
for the performance purpose what will be the better choice keeping structure same or split it into two tables.
Quick Reply will be very helpful.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

It depends. 50 columns is pretty wide. Will you be returning all of the fields? Some of the fields? Different sets of fields for different queries?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Dipesh Neema SSC Rookie Points: 28 More actions · Answer 2

I am returning around 15 fields , some are using in the order by , one in group by , 6 in fulltext search and 10in where clause

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Is there a difference in the data stored in that table such that it seems like it could be separated out, in your opinion?

The base function of this table, is it for OLTP or Warehouse type of data operations?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Dipesh Neema SSC Rookie Points: 28 More actions · Answer 4

I have normalized the tables and i have around 10 tables which have data. for performance improvement I created one table which have all the required data from all these tables so that I can get data from one place.

This table is using for OLTP.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

It seems to me that this setup is likely the best solution for your environment.

If the table were normalized, and you queried the data - how does performance compare to the current setup?

I would probably leave the table as is at this point.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Dipesh Neema SSC Rookie Points: 28 More actions · Answer 6

Thanks for your reply.

One more thing

I am using fulltext search for the searching the data from that table but it is taking around 2 mins for some of the search words while normally takes 10 - 30 secs for giving results.

Should i use like operator for that ? i am searching in the 6 varchar fields and table size is 15mil records.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

Dipesh Neema (11/30/2010)
Should i use like operator for that ? i am searching in the 6 varchar fields and table size is 15mil records.

Well , it depends if you require matching data and do you have any other option to filter the data other than LIKE?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)