Solved

SQL Distinct List for fields in very large tables

Posted on 2014-11-02
4
166 Views
Last Modified: 2014-11-03
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
Comment
Question by:murbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40418824
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
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40418866
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
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 250 total points
ID: 40418920
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
 

Author Closing Comment

by:murbro
ID: 40419367
Thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question