0

I have a table Customers with Millions of Records on 701 attributes ( columns ). I receive a csv file that has one row and 700 columns. Now on the basis of these 700 column values I have to extract the ids from the table Customers.

Now one way is obvious that i fire a select query with all 700 values in where clause.

My question is that if I first fetch a smaller table using only one attribute in where clause and then fetching again on the basis of second attribute in where clause ... and repeating this process for all attributes, would it be any faster ? Or can you suggest any other method that could make it faster ?

Desperado
  • 5
  • 4

1 Answers1

0

Try to understand the logic of those 700 attributes. There might be dependencies between them that can help reduce the number of the attributes to something more "realistic".

I would then use the same technique to see if I can run smaller queries which would benefit from indexes on the main table. Each time I would store the result in a temporary table (reducing the number or rows in the tmp table), index the temp table for the next step and do it again till I have the final result. Example: if you have date attributes: try to isolate all record for the year, then the day, etc.

Try to keep complex requests for the end as they will be running against smaller tmp tables.

Stephane Paquet
  • 2,315
  • 27
  • 31
  • Scene 1 I use all attributes in one where clause. Scene 2 i first get a smaller table using one attribute in where clause and repeating this process for 700 attributes. Which one will be faster ? – Desperado Jul 14 '17 at 03:56
  • I don't know your server configuration, but #2 seems to be the one that will not crash. – Stephane Paquet Jul 14 '17 at 05:20
  • Running a Where clause with 700 attributes on millions of rows does not seen to be workable – Stephane Paquet Jul 14 '17 at 05:21