March 2, 2017 at 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'))
March 2, 2017 at 7:36 am
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
March 2, 2017 at 8:01 am
Is there an ORDER BY along with the TOP 1000?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 2, 2017 at 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'))
March 2, 2017 at 8:44 am
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)?
March 2, 2017 at 10:44 am
Snargables - Thursday, March 2, 2017 7:26 AMWe 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)
March 2, 2017 at 2:28 pm
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
March 2, 2017 at 7:12 pm
Snargables - Thursday, March 2, 2017 8:28 AMthere 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 recordsSELECT 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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply