• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

SQL Distinct List for fields in very large tables

Hi

I need to create a distinct list of values for columns in tables that are between 5 and 10 million records.
These lists can be up to 200,000 records long. At the moment I am creating them while my app runs and
it takes a long time. How do I get around this? Should I create views or tables that have a distinct list?
0
Murray Brown
Asked:
Murray Brown
2 Solutions
 
PortletPaulfreelancerCommented:
There is no detail information to work with, so a generic answer would be:

Views will probably be faster than what you do now; Tables would be faster than views.

Indexes on the source table will have a big influence on performance.
0
 
HuaMin ChenSystem AnalystCommented:
Hi,
1. put "distinct" to the statement
2. You can then select the records into other table, on which there is table constraint that is ensuring uniqueness of all records.
0
 
Valliappan ANSenior Tech ConsultantCommented:
You can use (NOLOCK) attribute to your select query something like this, to avoid locking of all those records while fetching, which will dramatically give good performance. Something like this:

Select DISTINCT field1, field2,...,fieldn FROM [yourtable] (NOLOCK)
JOIN [anothertable] (NOLOCK) ON <join condition>
 
HTH.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now