top 1000 added to linked server select taking forever to run

Snargables

SSCrazy Eights

Points: 8855
More actions
March 2, 2017 at 7:26 am

#335424

We have a mysql database that we are referencing in sql server via linked servers. Our report guy have a tableau report that references the linked servers through views we created. He was complaining about long run times. I ran a trace to get his query. After playing with it for a little while I realized that tableau was adding a top 1000 to the query. For some reason this "top 1000" is causing the query to never end.
Has anyone ever experienced this? Is there a setting that I’m missing when I setup the linked server?
Here’s the query basically. This one never completes. When I remove the "top 1000" it finishes in 4 seconds
SELECT top 1000 *
FROM [kace_db_views].[vw_hd_ticket] [vw_hd_ticket]
INNER JOIN [kace_db_views].[vw_hd_category] [vw_hd_category] ON ([vw_hd_ticket].[HD_CATEGORY_ID] = [vw_hd_category].[ID])
INNER JOIN [kace_db_views].[vw_hd_queue] [vw_hd_queue] ON ([vw_hd_ticket].[HD_QUEUE_ID] = [vw_hd_queue].[ID])
INNER JOIN [kace_db_views].[vw_hd_status] [vw_hd_status] ON ([vw_hd_ticket].[HD_STATUS_ID] = [vw_hd_status].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user] ON ([vw_hd_ticket].[OWNER_ID] = [vw_user].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user1] ON ([vw_hd_ticket].[SUBMITTER_ID] = [vw_user1].[ID])
WHERE ([vw_hd_queue].[NAME] IN ('Data & Decision Sciences', 'EBay Analytics', 'Marketing Insights', 'US Retail Analytics', 'Web Analytics'))

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I can't explain why it runs quicker without the TOP 1000, but does he really need every column? That must be an awful lot of data being dragged across the network if he doesn't actually need it all.

Edit - I would guess that it's pulling the whole table results over and then doing the TOP (and possibly also the WHERE) afterwards. Have you looked at the execution plan with and without the TOP?

John

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Is there an ORDER BY along with the TOP 1000?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Snargables SSCrazy Eights Points: 8855 More actions · Answer 3

there is no order by and the actual select that is called is below. For some reason when the "top" clause is added it never completes. It may actually finish however i close it out after a few minutes. without the "top" clause it finished in 4 seconds. The first table has 11,000 records. the others have about 400 records

SELECT TOP 1000 [vw_hd_queue].[ALLOW_ALL_APPROVERS] AS [ALLOW_ALL_APPROVERS],
[vw_hd_queue].[ALLOW_ALL_USERS] AS [ALLOW_ALL_USERS],
[vw_hd_queue].[ALLOW_DELETE] AS [ALLOW_DELETE],
[vw_hd_queue].[ALLOW_OWNERS_VIA_ADMINUI] AS [ALLOW_OWNERS_VIA_ADMINUI],
[vw_hd_queue].[ALLOW_PARENT_CLOSE] AS [ALLOW_PARENT_CLOSE],
[vw_hd_queue].[ALT_EMAIL_ADDR] AS [ALT_EMAIL_ADDR],
[vw_user1].[AMS_ID] AS [AMS_ID (vw_user1)],
[vw_user].[AMS_ID] AS [AMS_ID],
[vw_user1].[API_ENABLED] AS [API_ENABLED (vw_user1)],
[vw_user].[API_ENABLED] AS [API_ENABLED],
[vw_hd_ticket].[APPROVAL] AS [APPROVAL],
CAST([vw_hd_ticket].[APPROVAL_NOTE] as nvarchar(1024)) AS [APPROVAL_NOTE],
[vw_hd_ticket].[APPROVER_ID] AS [APPROVER_ID],
[vw_hd_ticket].[APPROVE_STATE] AS [APPROVE_STATE],
[vw_hd_queue].[ARCHIVE_INTERVAL] AS [ARCHIVE_INTERVAL],
[vw_hd_ticket].[ASSET_ID] AS [ASSET_ID],
[vw_user1].[BUDGET_CODE] AS [BUDGET_CODE (vw_user1)],
[vw_user].[BUDGET_CODE] AS [BUDGET_CODE],
CAST([vw_hd_category].[CC_LIST] as nvarchar(1024)) AS [CC_LIST (vw_hd_category)],
CAST([vw_hd_ticket].[CC_LIST] as nvarchar(1024)) AS [CC_LIST],
[vw_hd_queue].[CONFLICT_WARNING_ENABLED] AS [CONFLICT_WARNING_ENABLED],
[vw_user].[CREATED] AS [CREATED (vw_user)],
[vw_user1].[CREATED] AS [CREATED (vw_user1)],
[vw_hd_ticket].[CREATED] AS [CREATED],
[vw_hd_queue].[CREATE_USERS_ON_EMAIL] AS [CREATE_USERS_ON_EMAIL],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE0] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE0],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE10] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE10],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE11] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE11],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE12] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE12],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE13] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE13],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE14] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE14],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE15] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE15],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE16] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE16],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE17] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE17],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE18] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE18],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE19] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE19],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE1] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE1],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE20] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE20],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE21] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE21],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE22] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE22],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE23] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE23],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE24] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE24],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE25] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE25],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE26] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE26],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE2] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE2],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE3] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE3],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE4] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE4],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE5] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE5],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE6] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE6],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE7] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE7],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE8] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE8],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE9] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE9],
[vw_hd_queue].[DEFAULT_CATEGORY_ID] AS [DEFAULT_CATEGORY_ID],
[vw_hd_queue].[DEFAULT_IMPACT_ID] AS [DEFAULT_IMPACT_ID],
[vw_hd_category].[DEFAULT_OWNER_ID] AS [DEFAULT_OWNER_ID],
[vw_hd_queue].[DEFAULT_PRIORITY_ID] AS [DEFAULT_PRIORITY_ID],
[vw_hd_queue].[DEFAULT_STATUS_ID] AS [DEFAULT_STATUS_ID],
[vw_user1].[DOMAIN] AS [DOMAIN (vw_user1)],
[vw_user].[DOMAIN] AS [DOMAIN],
[vw_hd_ticket].[DUE_DATE] AS [DUE_DATE],
[vw_user1]. AS ,
[vw_user]. AS ,
[vw_hd_queue].[EMAIL_USER] AS [EMAIL_USER],
[vw_hd_ticket].[ESCALATED] AS [ESCALATED],
[vw_user1].[FULL_NAME] AS [FULL_NAME (vw_user1)],
[vw_user].[FULL_NAME] AS [FULL_NAME],
[vw_hd_ticket].[HD_CATEGORY_ID] AS [HD_CATEGORY_ID],
[vw_user1].[HD_DEFAULT_QUEUE_ID] AS [HD_DEFAULT_QUEUE_ID (vw_user1)],
[vw_user].[HD_DEFAULT_QUEUE_ID] AS [HD_DEFAULT_QUEUE_ID],
[vw_user1].[HD_DEFAULT_VIEW] AS [HD_DEFAULT_VIEW (vw_user1)],
[vw_user].[HD_DEFAULT_VIEW] AS [HD_DEFAULT_VIEW],
[vw_hd_ticket].[HD_IMPACT_ID] AS [HD_IMPACT_ID],
[vw_hd_ticket].[HD_PRIORITY_ID] AS [HD_PRIORITY_ID],
[vw_hd_category].[HD_QUEUE_ID] AS [HD_QUEUE_ID (vw_hd_category)],
[vw_hd_status].[HD_QUEUE_ID] AS [HD_QUEUE_ID (vw_hd_status)],
[vw_hd_ticket].[HD_QUEUE_ID] AS [HD_QUEUE_ID],
[vw_hd_ticket].[HD_SERVICE_STATUS_ID] AS [HD_SERVICE_STATUS_ID],
[vw_hd_ticket].[HD_STATUS_ID] AS [HD_STATUS_ID],
[vw_hd_ticket].[HD_USE_PROCESS_STATUS] AS [HD_USE_PROCESS_STATUS],
[vw_user1].[HOME_PHONE] AS [HOME_PHONE (vw_user1)],
[vw_user].[HOME_PHONE] AS [HOME_PHONE],
[vw_hd_category].[ID] AS [ID (vw_hd_category)],
[vw_hd_queue].[ID] AS [ID (vw_hd_queue)],
[vw_hd_status].[ID] AS [ID (vw_hd_status)],
[vw_user].[ID] AS [ID (vw_user)],
[vw_user1].[ID] AS [ID (vw_user1)],
[vw_hd_ticket].[ID] AS [ID],
[vw_hd_ticket].[IS_MANUAL_DUE_DATE] AS [IS_MANUAL_DUE_DATE],
[vw_hd_ticket].[IS_PARENT] AS [IS_PARENT],
[vw_user1].[LDAP_IMPORTED] AS [LDAP_IMPORTED (vw_user1)],
[vw_user].[LDAP_IMPORTED] AS [LDAP_IMPORTED],
[vw_user1].[LDAP_UID] AS [LDAP_UID (vw_user1)],
[vw_user].[LDAP_UID] AS [LDAP_UID],
[vw_user1].[LEVEL] AS [LEVEL (vw_user1)],
[vw_user].[LEVEL] AS [LEVEL],
[vw_user1].[LINKED_APPLIANCE_ID] AS [LINKED_APPLIANCE_ID (vw_user1)],
[vw_user].[LINKED_APPLIANCE_ID] AS [LINKED_APPLIANCE_ID],
[vw_user1].[LOCALE_BROWSER_ID] AS [LOCALE_BROWSER_ID (vw_user1)],
[vw_user].[LOCALE_BROWSER_ID] AS [LOCALE_BROWSER_ID],
[vw_user1].[LOCATION_ID] AS [LOCATION_ID (vw_user1)],
[vw_user].[LOCATION_ID] AS [LOCATION_ID],
[vw_hd_ticket].[MACHINE_ID] AS [MACHINE_ID],
[vw_user1].[MANAGER_ID] AS [MANAGER_ID (vw_user1)],
[vw_user].[MANAGER_ID] AS [MANAGER_ID],
[vw_user1].[MOBILE_PHONE] AS [MOBILE_PHONE (vw_user1)],
[vw_user].[MOBILE_PHONE] AS [MOBILE_PHONE],
[vw_user].[MODIFIED] AS [MODIFIED (vw_user)],
[vw_user1].[MODIFIED] AS [MODIFIED (vw_user1)],
[vw_hd_ticket].[MODIFIED] AS [MODIFIED],
[vw_hd_category].[NAME] AS [NAME (vw_hd_category)],
[vw_hd_status].[NAME] AS [NAME (vw_hd_status)],
[vw_hd_queue].[NAME] AS [NAME],
1 AS [Number of Records],
[vw_hd_status].[ORDINAL] AS [ORDINAL (vw_hd_status)],
[vw_hd_category].[ORDINAL] AS [ORDINAL],
[vw_hd_queue].[OWNERS_ONLY_COMMENTS] AS [OWNERS_ONLY_COMMENTS],
[vw_hd_ticket].[OWNER_ID] AS [OWNER_ID],
[vw_user1].[PAGER_PHONE] AS [PAGER_PHONE (vw_user1)],
[vw_user].[PAGER_PHONE] AS [PAGER_PHONE],
[vw_hd_ticket].[PARENT_ID] AS [PARENT_ID],
[vw_user1].[PASSWORD] AS [PASSWORD (vw_user1)],
[vw_user].[PASSWORD] AS [PASSWORD],
[vw_user1].[PATH] AS [PATH (vw_user1)],
[vw_user].[PATH] AS [PATH],
[vw_user1].[PERMISSIONS] AS [PERMISSIONS (vw_user1)],
[vw_user].[PERMISSIONS] AS [PERMISSIONS],
[vw_hd_queue].[POP_PASSWORD_ENC] AS [POP_PASSWORD_ENC],
[vw_hd_queue].[POP_SERVER] AS [POP_SERVER],
[vw_hd_queue].[POP_SSL] AS [POP_SSL],
[vw_hd_queue].[POP_USERNAME] AS [POP_USERNAME],
[vw_user1].[PRIMARY_DEVICE_ID] AS [PRIMARY_DEVICE_ID (vw_user1)],
[vw_user].[PRIMARY_DEVICE_ID] AS [PRIMARY_DEVICE_ID],
[vw_hd_queue].[PURGE_INTERVAL] AS [PURGE_INTERVAL],
CAST([vw_hd_ticket].[RESOLUTION] as nvarchar(1024)) AS [RESOLUTION],
[vw_user1].[ROLE_ID] AS [ROLE_ID (vw_user1)],
[vw_user].[ROLE_ID] AS [ROLE_ID],
[vw_user1].[SALES_NOTIFICATIONS] AS [SALES_NOTIFICATIONS (vw_user1)],
[vw_user].[SALES_NOTIFICATIONS] AS [SALES_NOTIFICATIONS],
CAST([vw_hd_ticket].[SATISFACTION_COMMENT] as nvarchar(1024)) AS [SATISFACTION_COMMENT],
[vw_hd_ticket].[SATISFACTION_RATING] AS [SATISFACTION_RATING],
[vw_user1].[SECURITY_NOTIFICATIONS] AS [SECURITY_NOTIFICATIONS (vw_user1)],
[vw_user].[SECURITY_NOTIFICATIONS] AS [SECURITY_NOTIFICATIONS],
[vw_hd_ticket].[SERVICE_TICKET_ID] AS [SERVICE_TICKET_ID],
[vw_hd_queue].[SHOW_NEW_TICKET_ATTACHMENTS] AS [SHOW_NEW_TICKET_ATTACHMENTS],
[vw_hd_queue].[SHOW_NEW_TICKET_COMMENTS] AS [SHOW_NEW_TICKET_COMMENTS],
[vw_hd_ticket].[SLA_NOTIFIED] AS [SLA_NOTIFIED],
[vw_hd_queue].[SMTP_PASSWORD_ENC] AS [SMTP_PASSWORD_ENC],
[vw_hd_queue].[SMTP_PORT] AS [SMTP_PORT],
[vw_hd_queue].[SMTP_SERVER] AS [SMTP_SERVER],
[vw_hd_queue].[SMTP_USERNAME] AS [SMTP_USERNAME],
[vw_hd_status].[STATE] AS [STATE],
[vw_hd_ticket].[SUBMITTER_ID] AS [SUBMITTER_ID],
CAST([vw_hd_ticket].[SUMMARY] as nvarchar(1024)) AS [SUMMARY],
[vw_hd_ticket].[TIME_CLOSED] AS [TIME_CLOSED],
[vw_hd_ticket].[TIME_OPENED] AS [TIME_OPENED],
[vw_hd_ticket].[TIME_STALLED] AS [TIME_STALLED],
[vw_hd_ticket].[TITLE] AS [TITLE],
[vw_user1].[USER_NAME] AS [USER_NAME (vw_user1)],
[vw_user].[USER_NAME] AS [USER_NAME],
[vw_hd_category].[USER_SETTABLE] AS [USER_SETTABLE],
[vw_user1].[WORK_PHONE] AS [WORK_PHONE (vw_user1)],
[vw_user].[WORK_PHONE] AS [WORK_PHONE]
FROM [kace_db_views].[vw_hd_ticket] [vw_hd_ticket]
INNER JOIN [kace_db_views].[vw_hd_category] [vw_hd_category] ON ([vw_hd_ticket].[HD_CATEGORY_ID] = [vw_hd_category].[ID])
INNER JOIN [kace_db_views].[vw_hd_queue] [vw_hd_queue] ON ([vw_hd_ticket].[HD_QUEUE_ID] = [vw_hd_queue].[ID])
INNER JOIN [kace_db_views].[vw_hd_status] [vw_hd_status] ON ([vw_hd_ticket].[HD_STATUS_ID] = [vw_hd_status].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user] ON ([vw_hd_ticket].[OWNER_ID] = [vw_user].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user1] ON ([vw_hd_ticket].[SUBMITTER_ID] = [vw_user1].[ID])
WHERE ([vw_hd_queue].[NAME] IN ('Data & Decision Sciences', 'EBay Analytics', 'Marketing Insights', 'US Retail Analytics', 'Web Analytics'))

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 4

Just out of curiosity what happens if you pull all the data into local tables then try running the same two tests(with and without TOP 1000)?

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Snargables - Thursday, March 2, 2017 7:26 AM
We have a mysql database that we are referencing in sql server via linked servers. Our report guy have a tableau report that references the linked servers through views we created. He was complaining about long run times. I ran a trace to get his query. After playing with it for a little while I realized that tableau was adding a top 1000 to the query. For some reason this "top 1000" is causing the query to never end.
Has anyone ever experienced this? Is there a setting that Iâ€™m missing when I setup the linked server?
Hereâ€™s the query basically. This one never completes. When I remove the "top 1000" it finishes in 4 seconds
SELECT top 1000 *
FROM [kace_db_views].[vw_hd_ticket] [vw_hd_ticket]
INNER JOIN [kace_db_views].[vw_hd_category] [vw_hd_category] ON ([vw_hd_ticket].[HD_CATEGORY_ID] = [vw_hd_category].[ID])
INNER JOIN [kace_db_views].[vw_hd_queue] [vw_hd_queue] ON ([vw_hd_ticket].[HD_QUEUE_ID] = [vw_hd_queue].[ID])
INNER JOIN [kace_db_views].[vw_hd_status] [vw_hd_status] ON ([vw_hd_ticket].[HD_STATUS_ID] = [vw_hd_status].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user] ON ([vw_hd_ticket].[OWNER_ID] = [vw_user].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user1] ON ([vw_hd_ticket].[SUBMITTER_ID] = [vw_user1].[ID])
WHERE ([vw_hd_queue].[NAME] IN ('Data & Decision Sciences', 'EBay Analytics', 'Marketing Insights', 'US Retail Analytics', 'Web Analytics'))

My first guess is that MySQL sees the TOP 1000 and doesn't optimize it in any way, and starts doing RBAR internally to count 1,000 rows, whereas without that clause, it's just going to deliver the entire view and it doesn't have to do any thinking. Of course, I could be way off.... I've never liked MySQL very much, and I've never known it to do very well under any kind of load, but that's just my rather limited experience talking.

A quick Google search resulted in my finding this page: http://use-the-index-luke.com/sql/partial-results/top-n-queries

It suggests that a covering index for the order by is essential, but doesn't name MySQL by name on that point. That might just be the issue. Maybe there isn't an ORDER BY in the view? And with no ORDER BY to operate on?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Snargables SSCrazy Eights Points: 8855 More actions · Answer 6

sorry, i should have specified. Each view that u see is querying mysql using linked server w/ open query. It does a select * from each mysql table

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Snargables - Thursday, March 2, 2017 8:28 AM
there is no order by and the actual select that is called is below. For some reason when the "top" clause is added it never completes. It may actually finish however i close it out after a few minutes. without the "top" clause it finished in 4 seconds. The first table has 11,000 records. the others have about 400 records
SELECT TOP 1000 [vw_hd_queue].[ALLOW_ALL_APPROVERS] AS [ALLOW_ALL_APPROVERS],
[vw_hd_queue].[ALLOW_ALL_USERS] AS [ALLOW_ALL_USERS],
[vw_hd_queue].[ALLOW_DELETE] AS [ALLOW_DELETE],
[vw_hd_queue].[ALLOW_OWNERS_VIA_ADMINUI] AS [ALLOW_OWNERS_VIA_ADMINUI],
[vw_hd_queue].[ALLOW_PARENT_CLOSE] AS [ALLOW_PARENT_CLOSE],
[vw_hd_queue].[ALT_EMAIL_ADDR] AS [ALT_EMAIL_ADDR],
[vw_user1].[AMS_ID] AS [AMS_ID (vw_user1)],
[vw_user].[AMS_ID] AS [AMS_ID],
[vw_user1].[API_ENABLED] AS [API_ENABLED (vw_user1)],
[vw_user].[API_ENABLED] AS [API_ENABLED],
[vw_hd_ticket].[APPROVAL] AS [APPROVAL],
CAST([vw_hd_ticket].[APPROVAL_NOTE] as nvarchar(1024)) AS [APPROVAL_NOTE],
[vw_hd_ticket].[APPROVER_ID] AS [APPROVER_ID],
[vw_hd_ticket].[APPROVE_STATE] AS [APPROVE_STATE],
[vw_hd_queue].[ARCHIVE_INTERVAL] AS [ARCHIVE_INTERVAL],
[vw_hd_ticket].[ASSET_ID] AS [ASSET_ID],
[vw_user1].[BUDGET_CODE] AS [BUDGET_CODE (vw_user1)],
[vw_user].[BUDGET_CODE] AS [BUDGET_CODE],
CAST([vw_hd_category].[CC_LIST] as nvarchar(1024)) AS [CC_LIST (vw_hd_category)],
CAST([vw_hd_ticket].[CC_LIST] as nvarchar(1024)) AS [CC_LIST],
[vw_hd_queue].[CONFLICT_WARNING_ENABLED] AS [CONFLICT_WARNING_ENABLED],
[vw_user].[CREATED] AS [CREATED (vw_user)],
[vw_user1].[CREATED] AS [CREATED (vw_user1)],
[vw_hd_ticket].[CREATED] AS [CREATED],
[vw_hd_queue].[CREATE_USERS_ON_EMAIL] AS [CREATE_USERS_ON_EMAIL],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE0] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE0],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE10] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE10],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE11] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE11],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE12] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE12],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE13] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE13],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE14] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE14],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE15] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE15],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE16] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE16],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE17] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE17],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE18] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE18],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE19] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE19],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE1] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE1],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE20] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE20],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE21] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE21],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE22] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE22],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE23] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE23],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE24] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE24],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE25] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE25],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE26] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE26],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE2] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE2],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE3] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE3],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE4] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE4],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE5] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE5],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE6] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE6],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE7] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE7],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE8] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE8],
CAST([vw_hd_ticket].[CUSTOM_FIELD_VALUE9] as nvarchar(1024)) AS [CUSTOM_FIELD_VALUE9],
[vw_hd_queue].[DEFAULT_CATEGORY_ID] AS [DEFAULT_CATEGORY_ID],
[vw_hd_queue].[DEFAULT_IMPACT_ID] AS [DEFAULT_IMPACT_ID],
[vw_hd_category].[DEFAULT_OWNER_ID] AS [DEFAULT_OWNER_ID],
[vw_hd_queue].[DEFAULT_PRIORITY_ID] AS [DEFAULT_PRIORITY_ID],
[vw_hd_queue].[DEFAULT_STATUS_ID] AS [DEFAULT_STATUS_ID],
[vw_user1].[DOMAIN] AS [DOMAIN (vw_user1)],
[vw_user].[DOMAIN] AS [DOMAIN],
[vw_hd_ticket].[DUE_DATE] AS [DUE_DATE],
[vw_user1]. AS ,
[vw_user]. AS ,
[vw_hd_queue].[EMAIL_USER] AS [EMAIL_USER],
[vw_hd_ticket].[ESCALATED] AS [ESCALATED],
[vw_user1].[FULL_NAME] AS [FULL_NAME (vw_user1)],
[vw_user].[FULL_NAME] AS [FULL_NAME],
[vw_hd_ticket].[HD_CATEGORY_ID] AS [HD_CATEGORY_ID],
[vw_user1].[HD_DEFAULT_QUEUE_ID] AS [HD_DEFAULT_QUEUE_ID (vw_user1)],
[vw_user].[HD_DEFAULT_QUEUE_ID] AS [HD_DEFAULT_QUEUE_ID],
[vw_user1].[HD_DEFAULT_VIEW] AS [HD_DEFAULT_VIEW (vw_user1)],
[vw_user].[HD_DEFAULT_VIEW] AS [HD_DEFAULT_VIEW],
[vw_hd_ticket].[HD_IMPACT_ID] AS [HD_IMPACT_ID],
[vw_hd_ticket].[HD_PRIORITY_ID] AS [HD_PRIORITY_ID],
[vw_hd_category].[HD_QUEUE_ID] AS [HD_QUEUE_ID (vw_hd_category)],
[vw_hd_status].[HD_QUEUE_ID] AS [HD_QUEUE_ID (vw_hd_status)],
[vw_hd_ticket].[HD_QUEUE_ID] AS [HD_QUEUE_ID],
[vw_hd_ticket].[HD_SERVICE_STATUS_ID] AS [HD_SERVICE_STATUS_ID],
[vw_hd_ticket].[HD_STATUS_ID] AS [HD_STATUS_ID],
[vw_hd_ticket].[HD_USE_PROCESS_STATUS] AS [HD_USE_PROCESS_STATUS],
[vw_user1].[HOME_PHONE] AS [HOME_PHONE (vw_user1)],
[vw_user].[HOME_PHONE] AS [HOME_PHONE],
[vw_hd_category].[ID] AS [ID (vw_hd_category)],
[vw_hd_queue].[ID] AS [ID (vw_hd_queue)],
[vw_hd_status].[ID] AS [ID (vw_hd_status)],
[vw_user].[ID] AS [ID (vw_user)],
[vw_user1].[ID] AS [ID (vw_user1)],
[vw_hd_ticket].[ID] AS [ID],
[vw_hd_ticket].[IS_MANUAL_DUE_DATE] AS [IS_MANUAL_DUE_DATE],
[vw_hd_ticket].[IS_PARENT] AS [IS_PARENT],
[vw_user1].[LDAP_IMPORTED] AS [LDAP_IMPORTED (vw_user1)],
[vw_user].[LDAP_IMPORTED] AS [LDAP_IMPORTED],
[vw_user1].[LDAP_UID] AS [LDAP_UID (vw_user1)],
[vw_user].[LDAP_UID] AS [LDAP_UID],
[vw_user1].[LEVEL] AS [LEVEL (vw_user1)],
[vw_user].[LEVEL] AS [LEVEL],
[vw_user1].[LINKED_APPLIANCE_ID] AS [LINKED_APPLIANCE_ID (vw_user1)],
[vw_user].[LINKED_APPLIANCE_ID] AS [LINKED_APPLIANCE_ID],
[vw_user1].[LOCALE_BROWSER_ID] AS [LOCALE_BROWSER_ID (vw_user1)],
[vw_user].[LOCALE_BROWSER_ID] AS [LOCALE_BROWSER_ID],
[vw_user1].[LOCATION_ID] AS [LOCATION_ID (vw_user1)],
[vw_user].[LOCATION_ID] AS [LOCATION_ID],
[vw_hd_ticket].[MACHINE_ID] AS [MACHINE_ID],
[vw_user1].[MANAGER_ID] AS [MANAGER_ID (vw_user1)],
[vw_user].[MANAGER_ID] AS [MANAGER_ID],
[vw_user1].[MOBILE_PHONE] AS [MOBILE_PHONE (vw_user1)],
[vw_user].[MOBILE_PHONE] AS [MOBILE_PHONE],
[vw_user].[MODIFIED] AS [MODIFIED (vw_user)],
[vw_user1].[MODIFIED] AS [MODIFIED (vw_user1)],
[vw_hd_ticket].[MODIFIED] AS [MODIFIED],
[vw_hd_category].[NAME] AS [NAME (vw_hd_category)],
[vw_hd_status].[NAME] AS [NAME (vw_hd_status)],
[vw_hd_queue].[NAME] AS [NAME],
1 AS [Number of Records],
[vw_hd_status].[ORDINAL] AS [ORDINAL (vw_hd_status)],
[vw_hd_category].[ORDINAL] AS [ORDINAL],
[vw_hd_queue].[OWNERS_ONLY_COMMENTS] AS [OWNERS_ONLY_COMMENTS],
[vw_hd_ticket].[OWNER_ID] AS [OWNER_ID],
[vw_user1].[PAGER_PHONE] AS [PAGER_PHONE (vw_user1)],
[vw_user].[PAGER_PHONE] AS [PAGER_PHONE],
[vw_hd_ticket].[PARENT_ID] AS [PARENT_ID],
[vw_user1].[PASSWORD] AS [PASSWORD (vw_user1)],
[vw_user].[PASSWORD] AS [PASSWORD],
[vw_user1].[PATH] AS [PATH (vw_user1)],
[vw_user].[PATH] AS [PATH],
[vw_user1].[PERMISSIONS] AS [PERMISSIONS (vw_user1)],
[vw_user].[PERMISSIONS] AS [PERMISSIONS],
[vw_hd_queue].[POP_PASSWORD_ENC] AS [POP_PASSWORD_ENC],
[vw_hd_queue].[POP_SERVER] AS [POP_SERVER],
[vw_hd_queue].[POP_SSL] AS [POP_SSL],
[vw_hd_queue].[POP_USERNAME] AS [POP_USERNAME],
[vw_user1].[PRIMARY_DEVICE_ID] AS [PRIMARY_DEVICE_ID (vw_user1)],
[vw_user].[PRIMARY_DEVICE_ID] AS [PRIMARY_DEVICE_ID],
[vw_hd_queue].[PURGE_INTERVAL] AS [PURGE_INTERVAL],
CAST([vw_hd_ticket].[RESOLUTION] as nvarchar(1024)) AS [RESOLUTION],
[vw_user1].[ROLE_ID] AS [ROLE_ID (vw_user1)],
[vw_user].[ROLE_ID] AS [ROLE_ID],
[vw_user1].[SALES_NOTIFICATIONS] AS [SALES_NOTIFICATIONS (vw_user1)],
[vw_user].[SALES_NOTIFICATIONS] AS [SALES_NOTIFICATIONS],
CAST([vw_hd_ticket].[SATISFACTION_COMMENT] as nvarchar(1024)) AS [SATISFACTION_COMMENT],
[vw_hd_ticket].[SATISFACTION_RATING] AS [SATISFACTION_RATING],
[vw_user1].[SECURITY_NOTIFICATIONS] AS [SECURITY_NOTIFICATIONS (vw_user1)],
[vw_user].[SECURITY_NOTIFICATIONS] AS [SECURITY_NOTIFICATIONS],
[vw_hd_ticket].[SERVICE_TICKET_ID] AS [SERVICE_TICKET_ID],
[vw_hd_queue].[SHOW_NEW_TICKET_ATTACHMENTS] AS [SHOW_NEW_TICKET_ATTACHMENTS],
[vw_hd_queue].[SHOW_NEW_TICKET_COMMENTS] AS [SHOW_NEW_TICKET_COMMENTS],
[vw_hd_ticket].[SLA_NOTIFIED] AS [SLA_NOTIFIED],
[vw_hd_queue].[SMTP_PASSWORD_ENC] AS [SMTP_PASSWORD_ENC],
[vw_hd_queue].[SMTP_PORT] AS [SMTP_PORT],
[vw_hd_queue].[SMTP_SERVER] AS [SMTP_SERVER],
[vw_hd_queue].[SMTP_USERNAME] AS [SMTP_USERNAME],
[vw_hd_status].[STATE] AS [STATE],
[vw_hd_ticket].[SUBMITTER_ID] AS [SUBMITTER_ID],
CAST([vw_hd_ticket].[SUMMARY] as nvarchar(1024)) AS [SUMMARY],
[vw_hd_ticket].[TIME_CLOSED] AS [TIME_CLOSED],
[vw_hd_ticket].[TIME_OPENED] AS [TIME_OPENED],
[vw_hd_ticket].[TIME_STALLED] AS [TIME_STALLED],
[vw_hd_ticket].[TITLE] AS [TITLE],
[vw_user1].[USER_NAME] AS [USER_NAME (vw_user1)],
[vw_user].[USER_NAME] AS [USER_NAME],
[vw_hd_category].[USER_SETTABLE] AS [USER_SETTABLE],
[vw_user1].[WORK_PHONE] AS [WORK_PHONE (vw_user1)],
[vw_user].[WORK_PHONE] AS [WORK_PHONE]
FROM [kace_db_views].[vw_hd_ticket] [vw_hd_ticket]
INNER JOIN [kace_db_views].[vw_hd_category] [vw_hd_category] ON ([vw_hd_ticket].[HD_CATEGORY_ID] = [vw_hd_category].[ID])
INNER JOIN [kace_db_views].[vw_hd_queue] [vw_hd_queue] ON ([vw_hd_ticket].[HD_QUEUE_ID] = [vw_hd_queue].[ID])
INNER JOIN [kace_db_views].[vw_hd_status] [vw_hd_status] ON ([vw_hd_ticket].[HD_STATUS_ID] = [vw_hd_status].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user] ON ([vw_hd_ticket].[OWNER_ID] = [vw_user].[ID])
INNER JOIN [kace_db_views].[vw_user] [vw_user1] ON ([vw_hd_ticket].[SUBMITTER_ID] = [vw_user1].[ID])
WHERE ([vw_hd_queue].[NAME] IN ('Data & Decision Sciences', 'EBay Analytics', 'Marketing Insights', 'US Retail Analytics', 'Web Analytics'))

As you say, those aren't tables. Those are views. Adding the TOP is probably causing all of the views to fully materialize. If each view is across a linked server, that may be compounding the problem with TOP because materialization of the views is probably pulling all of the data from the MySQL tables.

--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)