SELECT DISTINCT ON 4.500.000 RECORDS

[sal21]

I need a good tips to select (in order of save time) a distinct selection on one field with 4.500.xxx records approx

I use this connection and SqlServer 2005

Code: Select all

  Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='myserver';" & _
        "Initial Catalog='mydb';Integrated Security='SSPI';"
    Cnxn.Open strCnxn

[HansV]

Make sure that there is an index on that field (in SQL Server). That will greatly speed up the query.

[cecil]

I am not a MS SQL user. That said, you may want to try a group by rather than distinct. The code parser may see it the same, maybe not. There are other things we do in Oracle, like use query hints to tell it which index to use. A quick Google search tells me that MSSQL also has query hints. Of couse this assumes, like Hans point out, that you have an index on the column.

http://msdn.microsoft.com/en-us/library/ms181714.aspx" onclick="window.open(this.href);return false;