We setup Metabase to point towards our production DB and noticed a huge performance hit where basically 50% of the CPU was taken up by queries like:
SELECT TOP 301 “dbo”.“Contact”.“SOCIAL_TWITTER” AS “SOCIAL_TWITTER” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“SOCIAL_TWITTER” ORDER BY “dbo”.“Contact”.“SOCIAL_TWITTER” ASC
SELECT “dbo”.“Contact”.“ADDRESS_MAIL_CITY” AS “field” FROM “dbo”.“Contact” ORDER BY “dbo”.“Contact”.“CONTACT_ID” ASC OFFSET 500 ROWS FETCH NEXT 500 ROWS ONLY
SELECT TOP 301 “dbo”.“Contact”.“PHONE_MOBILE” AS “PHONE_MOBILE” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“PHONE_MOBILE” ORDER BY “dbo”.“Contact”.“PHONE_MOBILE” ASC
SELECT “dbo”.“Contact”.“EMAIL_ADDRESS” AS “field” FROM “dbo”.“Contact” ORDER BY “dbo”.“Contact”.“CONTACT_ID” ASC OFFSET 0 ROWS FETCH NEXT 500 ROWS ONLY
SELECT TOP 301 “dbo”.“Contact”.“PHONE_HOME” AS “PHONE_HOME” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“PHONE_HOME” ORDER BY “dbo”.“Contact”.“PHONE_HOME” ASC
SELECT TOP 301 “dbo”.“Contact”.“FIRST_NAME” AS “FIRST_NAME” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“FIRST_NAME” ORDER BY “dbo”.“Contact”.“FIRST_NAME” ASC
SELECT TOP 301 “dbo”.“Contact”.“PHONE_FAX” AS “PHONE_FAX” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“PHONE_FAX” ORDER BY “dbo”.“Contact”.“PHONE_FAX” ASC
SELECT TOP 301 “dbo”.“Contact”.“SOCIAL_FACEBOOK” AS “SOCIAL_FACEBOOK” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“SOCIAL_FACEBOOK” ORDER BY “dbo”.“Contact”.“SOCIAL_FACEBOOK” ASC
SELECT TOP 301 “dbo”.“Contact”.“SOCIAL_FACEBOOK” AS “SOCIAL_FACEBOOK” FROM “dbo”.“Contact” GROUP BY “dbo”.“Contact”.“SOCIAL_FACEBOOK” ORDER BY “dbo”.“Contact”.“SOCIAL_FACEBOOK” ASC
SELECT “dbo”.“Contact”.“ADDRESS_OTHER_CITY” AS “field” FROM “dbo”.“Contact” ORDER BY “dbo”.“Contact”.“CONTACT_ID” ASC OFFSET 2500 ROWS FETCH NEXT 500 ROWS ONLY
SELECT TOP 301 “dbo”.“Comment”.“BODY_SUMMARY” AS “BODY_SUMMARY” FROM “dbo”.“Comment” GROUP BY “dbo”.“Comment”.“BODY_SUMMARY” ORDER BY “dbo”.“Comment”.“BODY_SUMMARY” ASC
SELECT TOP 301 “dbo”.“CalendarEvent”.“TITLE” AS “TITLE” FROM “dbo”.“CalendarEvent” GROUP BY “dbo”.“CalendarEvent”.“TITLE” ORDER BY “dbo”.“CalendarEvent”.“TITLE” ASC
So we had to stop them. I’m wondering:
- Is it recommended to target a production DB or use another method?
- We run SQL 2016 in High Available Group and notice there isn’t a way to make it target the read node. Any workaround?
- Does Metabase perform these queries once and then update on a regular interval with less impact?
- When running a Metabase query does it target its local metabase db it created or the target?
Looking for assistance, thanks.