very slow select

• Jonathan AC Roberts

  SSCoach

  Points: 17601

  More actions

  January 21, 2019 at 8:19 am

  #2019522

  It might be worth trying to rewrite the query. The query below is equivalent to your query and will produce a different execution plan:
  It would be good if you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID. To do this you would have to make the primary key nonclustered.

  SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000018
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000092
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000095
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000096
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000316
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000320
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000329
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000331
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000334
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000335
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000340
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000341
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000603
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000605
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000611
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000613
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000614
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000615
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000616
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000617
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000618
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000620
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000621
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000623
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000625
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000626
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000638
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000639
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000640


• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 21, 2019 at 8:25 am

  #2019524

  Jonathan AC Roberts - Monday, January 21, 2019 8:19 AM

  It might be worth trying to rewrite the query. The query below is equivalent to your query and will produce a different execution plan:
  It would be good it you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID. To do this you would have to make the primary key nonclustered.

  SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000018
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000092
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000095
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000096
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000316
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000320
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000329
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000331
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000334
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000335
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000340
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000341
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000603
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000605
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000611
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000613
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000614
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000615
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000616
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000617
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000618
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000620
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000621
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000623
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000625
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000626
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000638
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000639
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000640


  thank you sir, I'll try out the query above and see how it works.

• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 21, 2019 at 8:30 am

  #2019526

  Tomys - Monday, January 21, 2019 8:25 AM

  Jonathan AC Roberts - Monday, January 21, 2019 8:19 AM

  It might be worth trying to rewrite the query. The query below is equivalent to your query and will produce a different execution plan:
  It would be good it you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID. To do this you would have to make the primary key nonclustered.

  SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000018
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000092
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000095
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000096
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000316
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000320
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000329
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000331
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000334
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000335
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000340
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000341
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000603
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000605
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000611
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000613
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000614
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000615
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000616
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000617
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000618
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000620
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000621
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000623
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000625
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000626
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000638
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000639
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000640


  thank you sir, I'll try out the query above and see how it works.

  Unfortunately I am getting error as :
  Msg 4104, Level 16, State 1, Line 4
  The multi-part identifier "o2a.PROPERTY_ID" could not be bound.

• pietlinden

  SSC Guru

  Points: 63198

  More actions

  January 21, 2019 at 8:42 am

  #2019529

  There query appears to be missing an alias:
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE 
FROM CR2Copy..OBJECT_TO_ATTRIBUTE

  should be
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE 
FROM CR2Copy..OBJECT_TO_ATTRIBUTE AS o2a

• Jonathan AC Roberts

  SSCoach

  Points: 17601

  More actions

  January 21, 2019 at 9:09 am

  #2019531

  pietlinden - Monday, January 21, 2019 8:42 AM

  There query appears to be missing an alias:
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE 
FROM CR2Copy..OBJECT_TO_ATTRIBUTE

  should be
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE 
FROM CR2Copy..OBJECT_TO_ATTRIBUTE AS o2a

  Yes, thanks, I've correct that below:
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000018
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000092
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000095
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000096
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000316
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000320
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000329
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000331
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000334
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000335
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000340
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000341
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000603
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000605
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000611
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000613
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000614
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000615
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000616
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000617
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000618
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000620
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000621
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000623
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000625
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000626
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000638
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000639
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000640

• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 21, 2019 at 7:04 pm

  #2019578

  Jonathan AC Roberts - Monday, January 21, 2019 9:09 AM

  pietlinden - Monday, January 21, 2019 8:42 AM

  There query appears to be missing an alias:
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE 
FROM CR2Copy..OBJECT_TO_ATTRIBUTE

  should be
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE 
FROM CR2Copy..OBJECT_TO_ATTRIBUTE AS o2a

  Yes, thanks, I've correct that below:
  SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000018
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000092
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000095
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000096
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000316
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000320
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000329
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000331
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000334
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000335
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000340
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000341
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000603
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000605
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000611
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000613
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000614
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000615
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000616
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000617
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000618
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000620
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000621
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000623
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000625
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000626
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000638
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000639
UNION
SELECT o2a.SOURCE_ID source_id, o2a.VALUE extdata
FROM CR2Copy..OBJECT_TO_ATTRIBUTE o2a
WHERE o2a.STATUS_ID = 33000000000001
AND o2a.PROPERTY_ID = 1000000060338
AND o2a.SOURCE_ID = 82000000000640

  Thank you , I will also include with no lock and run and see

• frederico_fonseca

  SSCoach

  Points: 15304

  More actions

  January 21, 2019 at 7:14 pm

  #2019579

  did you try the index and the insert into a temp table as I suggested?
  If so what were the results

• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 21, 2019 at 7:47 pm

  #2019580

  Dear sir I tried creating index and it was taking a lot of time so had to stop it. Not sure how long it would run since it is prod server didn’t want to take any risk( will this cause anything to other process? )Please advise what other options are there if any. Thanks

• Jonathan AC Roberts

  SSCoach

  Points: 17601

  More actions

  January 21, 2019 at 8:09 pm

  #2019581

  Tomys - Monday, January 21, 2019 7:47 PM

  Dear sir I tried creating index and it was taking a lot of time so had to stop it. Not sure how long it would run since it is prod server didn’t want to take any risk( will this cause anything to other process? )Please advise what other options are there if any. Thanks

  If you have enterprise SQL Server you can use the ONLINE=ON option which should allow other processes to access the table while the index is being created.

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  January 22, 2019 at 12:04 am

  #2019593

  Jonathan AC Roberts - Monday, January 21, 2019 8:19 AM

  It would be good it you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID.

  Be REAL careful here.  Changing the clustered index to having a such a low cardinality as well as being mutable is a recipe for disaster due to data movement.  We are talking about a 2 billion row table here and as that Status_ID likely changes at least once, that's also going to cause massive page splits, massive fragmentation, and massive overuse of the log file.  The last time I did such a thing on a table of that magnitude, I brought Expedia.com to its knees and that was just with a non-clustered index of similar ilk.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• Jonathan AC Roberts

  SSCoach

  Points: 17601

  More actions

  January 22, 2019 at 4:35 am

  #2019610

  Jeff Moden - Tuesday, January 22, 2019 12:04 AM

  Jonathan AC Roberts - Monday, January 21, 2019 8:19 AM

  It would be good it you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID.

  Be REAL careful here.  Changing the clustered index to having a such a low cardinality as well as being mutable is a recipe for disaster due to data movement.  We are talking about a 2 billion row table here and as that Status_ID likely changes at least once, that's also going to cause massive page splits, massive fragmentation, and massive overuse of the log file.  The last time I did such a thing on a table of that magnitude, I brought Expedia.com to its knees and that was just with a non-clustered index of similar ilk.

  Yes, good point.

• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 22, 2019 at 8:56 am

  #2019652

  Hello sir, thanks for your suggestion I will try out and be careful before doing it.

• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 22, 2019 at 8:56 am

  #2019653

  Okay thank you

• Tomys

  Mr or Mrs. 500

  Points: 560

  More actions

  January 22, 2019 at 9:23 am

  #2019662

  Jeff Moden - Tuesday, January 22, 2019 12:04 AM

  Jonathan AC Roberts - Monday, January 21, 2019 8:19 AM

  It would be good it you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID.

  Be REAL careful here.  Changing the clustered index to having a such a low cardinality as well as being mutable is a recipe for disaster due to data movement.  We are talking about a 2 billion row table here and as that Status_ID likely changes at least once, that's also going to cause massive page splits, massive fragmentation, and massive overuse of the log file.  The last time I did such a thing on a table of that magnitude, I brought Expedia.com to its knees and that was just with a non-clustered index of similar ilk.

  Dear sir, I am not sure if this proper for the columns you mentioned , it shows me in the index key column list section for an index, please see my snip attached, but included columns list tab is empty.

  

• Jonathan AC Roberts

  SSCoach

  Points: 17601

  More actions

  January 22, 2019 at 9:50 am

  #2019671

  Tomys - Tuesday, January 22, 2019 9:23 AM

  Jeff Moden - Tuesday, January 22, 2019 12:04 AM

  Jonathan AC Roberts - Monday, January 21, 2019 8:19 AM

  It would be good it you could have a clustered index on STATUS_ID, PROPERTY_ID, SOURCE_ID.

  Be REAL careful here.  Changing the clustered index to having a such a low cardinality as well as being mutable is a recipe for disaster due to data movement.  We are talking about a 2 billion row table here and as that Status_ID likely changes at least once, that's also going to cause massive page splits, massive fragmentation, and massive overuse of the log file.  The last time I did such a thing on a table of that magnitude, I brought Expedia.com to its knees and that was just with a non-clustered index of similar ilk.

  Dear sir, I am not sure if this proper for the columns you mentioned , it shows me in the index key column list section for an index, please see my snip attached, but included columns list tab is empty.

  

  I suggested you create a CLUSTERED index on those columns. This is probably not a good idea due to the fragmentation/splitting/update issues Jeff pointed out in his comment.
  If you are going to create a non-clustered index then you would also need to have the Value column in the Included columns tab
  CREATE INDEX IX_OBJECT_TO_ATTRIBUTE_1
ON dbo.OBJECT_TO_ATTRIBUTE(STATUS_ID, PROPERTY_ID, SOURCE_ID) INCLUDE (Value)
  If you create a clustered index you would just need to create it on the STATUS_ID, PROPERTY_ID, SOURCE_ID columns. Ideally starting with the most selective column as the leading column of the index. But as you can only have one clustered index on a table you would have to make the primary key nonclustered before you can create a clustered index.