CODEWITHSUNDEEP
sqlsql-server
user145610
user145610

Reputation: 3035

Performance Improve on SQL Large table

Im having 260 columns table in SQL server. When we run "Select count(*) from table" it is taking almost 5-6 to get the count. Table contains close 90-100 million records with 260 columns where more than 50 % Column contains NULL. Apart from that, user can also build dynamic sql query on to table from the UI, so searching 90-100 million records will take time to return results. Is there way to improve find functionality on a SQL table where filter criteria can be anything , can any1 suggest me fastest way get aggregate data on 25GB data .Ui should get hanged or timeout

Upvotes: 0

Views: 2859

Answers (4)

Michael Green
Michael Green

Reputation: 1491

Investigate horizontal partitioning. This will really only help query performance if you can force users to put the partitioning key into the predicates.

Try vertical partitioning, where you split one 260-column table into several tables with fewer columns. Put all the values which are commonly required together into one table. The queries will only reference the table(s) which contain columns required. This will give you more rows per page i.e. fewer pages per query.

You have a high fraction of NULLs. Sparse columns may help, but calculate your percentages as they can hurt if inappropriate. There's an SO question on this.

Filtered indexes and filtered statistics may be useful if the DB often runs similar queries.

Upvotes: 1

Twelfth
Twelfth

Reputation: 7190

Changing my comment into an answer...

You are moving from a transaction world where these 90-100 million records are recorded and into a data warehousing scenario where you are now trying to slice, dice, and analyze the information you have. Not an easy solution, but odds are you're hitting the limits of what your current system can scale to.

In a past job, I had several (6) data fields belonging to each record that were pretty much free text and randomly populated depending on where the data was generated (they were search queries and people were entering what they basically would enter in google). With 6 fields like this...I created a dim_text table that took each entry in any of these 6 tables and replaced it with an integer. This left me a table with two columns, text_ID and text. Any time a user was searching for a specific entry in any of these 6 columns, I would search my dim_search table that was optimized (indexing) for this sort of query to return an integer matching the query I wanted...I would then take the integer and search for all occourences of the integer across the 6 fields instead. searching 1 table highly optimized for this type of free text search and then querying the main table for instances of the integer is far quicker than searching 6 fields on this free text field.

I'd also create aggregate tables (reporting tables if you prefer the term) for your common aggregates. There are quite a few options here that your business setup will determine...for example, if each row is an item on a sales invoice and you need to show sales by date...it may be better to aggregate total sales by invoice and save that to a table, then when a user wants totals by day, an aggregate is run on the aggreate of the invoices to determine the totals by day (so you've 'partially' aggregated the data in advance).

Hope that makes sense...I'm sure I'll need several edits here for clarity in my answer.

Upvotes: 0

Nizam
Nizam

Reputation: 4699

Things that came me up was:

  1. [SQL Server 2012+] If you are using SQL Server 2012, you can use the new Columnstore Indexes.
  2. [SQL Server 2005+] If you are filtering a text column, you can use Full-Text Search
  3. If you have some function that you apply frequently in some column (like SOUNDEX of column, for example), you could create PERSISTED COMPUTED COLUMN to not having to compute this value everytime.
  4. Use temp tables (indexed ones will be much better) to reduce the number of rows to work on.

@Twelfth comment is very good:

"I think you need to create an ETL process and start changing this into a fact table with dimensions."

Upvotes: 0

Paul Lucaciu
Paul Lucaciu

Reputation: 134

As the guys state in the comments you need to analyse a few of the queries and see which indexes would help you the most. If your query does a lot of searches, you could use the full text search feature of the MSSQL server. Here you will find a nice reference with good examples.

Upvotes: 0

Related Questions