StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Query is taking too long to execute
text
Body
copied!<p>Hi All below is my query written for SQL 2008. It takes more than 2 hours to insert 500000 records. Could any one suggest a way to improve performance?</p> <pre><code>INSERT INTO tblUserFile SELECT CASE WHEN UD.IdentityStatus = 'A' THEN 'ACTIVE' WHEN UD.IdentityStatus in ('T','') THEN 'INACTIVE' WHEN UD.IdentityStatus IS NULL THEN '' END, --'UD.IS' AS "Status", ISNULL(UD.HltID,'') AS "USERID", ISNULL(UD.HltID,'') AS "USERNAME", ISNULL(UD.FirstName,'') AS "FIRSTNAME", ISNULL(UD.LastName,'') AS "LASTNAME", ISNULL(UD.MiddleInitials,'') AS "MI", '' AS "GENDER", ISNULL(UD.EmailAddress,'') AS "EMAIL", CASE WHEN SU.UserType = 'C' THEN ISNULL(MCU.Manager, '') ----look into this WHEN SU.UserType = 'R' THEN 'From LMS SuperViser' WHEN SU.UserType IS NULL OR SU.UserType = '' THEN '' END, '' AS HR, '' AS "DEPARTMENT", '' AS "JOBCODE", '' AS "DIVISION", ISNULL(UD.Office,'') AS "LOCATION", '' AS "TIMEZONE", '' AS "HIREDATE", ISNULL(UD.Title,'') AS "TITLE", ISNULL(UD.StreetAddress,'') AS "ADDR1", '' AS "ADDR2", ISNULL(UD.City,'') AS "CITY", ISNULL(UD.State,'') AS "STATE", ISNULL(UD.Zip,'') AS "ZIP", ISNULL(UD.CountryCode,'') AS "COUNTRY", '' AS "REVIEW_FREQ", '' AS "LAST_REVIEW_DATE", ISNULL(UD.EmployeeType,'') AS "Custom01", '' AS "Custom02", CASE WHEN SU.UserType = 'C' THEN '' WHEN SU.UserType = 'R' THEN ISNULL(FSBD.Name,'') WHEN SU.UserType IS NULL OR SU.UserType = '' THEN '' END, '' AS "Custom04", '' AS "Custom05", '' AS "Custom06", '' AS "Custom07", '' AS "Custom08", CASE WHEN SU.UserType = 'C' THEN 'Corporate' WHEN SU.UserType = 'R' THEN 'Hotel' WHEN SU.UserType IS NULL OR SU.UserType = '' THEN '' END, ISNULL(UD.EmpId,'') AS "Custom11", '' AS "Custom13", '' AS "Custom14", '' AS "Custom15", '' AS "PositionCode", ISNULL(SU.HomeFacility, '') AS "HomeFacility", 'NPS' AS PSFlag FROM Search..UserData UD LEFT JOIN Search..ManagerForCorpUsers MCU ------ look into this ON MCU.EmpID = UD.EmpId AND UD.EmpId != '' AND UD.EmpId IS NOT NULL LEFT JOIN Search..securityUsers SU ------ look into this ON UD.UserId = SU.UserID AND UD.UserId != '' AND UD.UserId IS NOT NULL LEFT JOIN EIS.dbo.NewQueryFilter NQ ON SU.HomeFacility = NQ.FCNB AND SU.HomeFacility != '' AND SU.HomeFacility IS NOT NULL LEFT JOIN Facility..fcSubBrandDesc FSBD ON NQ.SubBrand = FSBD.SubBrand AND NQ.SubBrand != '' AND NQ.SubBrand IS NOT NULL WHERE ISNULL(UD.IdentityStatus,'') NOT IN ('D','U','L') AND ISNULL(UD.EmployeeType,'') NOT IN ('O','V','') AND ISNULL(UD.HltId,'') != '' AND ISNULL(UD.EmpId,'') NOT IN (SELECT DISTINCT UserId FROM Search..CurrentUserFile) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload