1

We have a SQL SERVER with only one table. This table contains 50 million rows. The table has 31 columns. 1 Identity column and 30 nchar columns with different sizes. The table size is of 15 GB.

The table contains information of the year 2017. Initially there's no going to be inserts in the table. But in the future the client maybe will want to add the year 2018 or maybe 2016. For the time being is for queries.

The queries need minutes to perform. So, I thought that it will be better to divide the table in 1 table per month because the table is too big. But the solution doesn't satisfy the client because he wants to query all the data.

So, given this scenario what would be the best approach to handle this table and make it possible to query fast?

Ángel Javier Mena Espinosa
  • 275
  • 2
  • 19
  • Can you provide any detail about table structures? and what queries are you running? – Ryan Gadsdon Mar 12 '18 at 12:39
  • Does "all the data" means select all no where or meaning coddling unknown requirements – Dalorzo Mar 12 '18 at 12:40
  • 1
    Having 30 string columns is a big red flag. Are these all actual strings, or is it actually data stored with the wrong type? – Jeroen Mostert Mar 12 '18 at 12:40
  • 2
    The obvious answer is indexes. Start one query at a time. Or you can just blast it and put an index on each column. At stated the question is too broad. – paparazzo Mar 12 '18 at 12:40
  • 4
    Breaking the table up doesn't mean the client can't query "all the data", it just means querying it *differently*. But @Paparazzi is correct, your first step should be indexes - after you ensure that all of your data types are correct for what's being stored in each field. 50M records/15GB is not a terribly large table, but 30 `nchar`s is cause for concern with respect to your data model. – alroc Mar 12 '18 at 12:43
  • As I though adding indexes is the first step and the second convert nchar to order smaller types like int or bit when is possible. But I don't think that will be enough. – Ángel Javier Mena Espinosa Mar 12 '18 at 14:27
  • 1
    @ajmena "Thinking" (which is really just guessing) is not an efficient way to solve your issue. The fact that you have a database containing a single table is an indication that you need help with your data model first and foremost. Do you have an option to redesign the table? If not, then a more efficient approach is to post a sample query that performs poorly (and DDL of the table). Otherwise, everybody is guessing. – SMor Mar 12 '18 at 14:52

1 Answers1

2

I would start with obvious which is index. Refer to: What columns generally make good indexes?

At the problem you mention, you could always break down the table into smaller one and in order to query to the whole data set, it could be achieved either from view either from some stored procedures where you could include some logic.

Emka
  • 340
  • 6
  • 16