• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

How do I safely and efficiently delete/drop large tables and recreate in overnight process?

First - I am NOT a SQL Server DBA.  I've very limited experience with T-SQL - just queries and views.  My SQL experience is from FoxPro/Access.  I'm contracting to do primarily SSRS.  But, a report I'm building is so complicated and takes so long to run, that it is impractical to run it from a view.  There are 4 different datasets that I need - each one creates a table ~22MB of data space (100,000 rows).  I would like to create tables using select statements, then delete them and re-create them overnight.  I have several questions:

1.  can I simply DROP TABLE?
2.  can I do this in a stored procedure?
3.  will I also have to re-create the indexes or is there a way to save them?
4.  are there any storage issues I should be concerned about?  (this process is on a "helper" server, not the server running the ERP - or the Report Server).
5.  is there anything else I should be concerned about.

Thank you for good advice!
0
sarahellis
Asked:
sarahellis
  • 12
  • 9
1 Solution
 
MuffyBunnyCommented:
1. Yes you can
2. Yes you can
3. Nope. When you drop a table, everything associated with it goes too.
4. Storage will depend on how much you are copying
5. You should always worry when dropping and re-creating tables

I'm assuming your goal is to end up with a smaller dataset that only includes what you need for your reports. Is the plan to hit that set for your reports then kill that set in the name of saving space?
0
 
MuffyBunnyCommented:
Do you have a DBA? Have you thought about replication?
0
 
sarahellisAuthor Commented:
#4 each table is ~22MB of data space (100,000 rows)
#5 ??

The dataset I need for the reports has to be pretty big because there are user input parameters.  It's just that the data comes from many tables with LOTS of either Case statements or Unions, depending upon how it's written.  I've tried it both ways - both are slow.  It's a very complicated query.  The purpose of deleting the table is so I can re-create it daily with updated info.

We have a DBA - that's who I'm contracting for.  I need to bring him a complete solution.  Replication will not increase the speed.  It's not about space - it's about speed.  

Thanks.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
MuffyBunnyCommented:
Have you tried temp tables and/or table datatypes? They're more efficient than views.
0
 
MuffyBunnyCommented:
If you're tied to creating physical tables, create a stored proc that drops the tables from the night before and then creates the new tables for the next day. Have the DBA schedule the proc through the sql server agent.

Another way would be to create your tables and let them exist all the time. Use SSAS to truncate them every night and re-populate. That way you wont have to rebuild the indexes and everything every time.
0
 
sarahellisAuthor Commented:
Yikes.  I'll have to read about temp tables and table datatypes.  I'm not familiar with them.  And unfortunately, I'm even more clueless about using SSAS.  I'll look into it.  I'm still trying to figure out everything I need to write a stored procedure.  This is what I've got so far.  Would you mind glancing through it to see if there are any glaring problems? (Not necessarily the query that builds the table - I've tried several ways to pull the info.   Not sure if this is the version I'll settle on.)  Thank you!

USE [MSCRM_Helper]            
GO
CREATE PROCEDURE [MSCRM_Helper].uspBuildCampaignActSummLetterDirect
AS
drop table [MSCRM_Helper].dbo.CampaignActSummLetterDirect
Go      
            
Select * INTO [MSCRM_Helper].dbo.CampaignActSummLetterDirect            
from (            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,3,datediff(d, 0 ,IX.activedate)) as Dte,
             'GA-Grad-P' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE LA1.Line1 IS NOT NULL AND L.emailaddress1 NOT LIKE '%@%'  AND LX.prospecttype=3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
UNION            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,3,datediff(d, 0 ,IX.activedate)) as Dte,
             'GA-Grad-C'  as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE  LA2.Line1 IS NOT NULL AND LA1.Line1 IS NULL AND L.emailaddress1 NOT LIKE '%@%' AND LX.prospecttype=3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
UNION            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,3,datediff(d, 0 ,IX.activedate)) as Dte,
             'UA-Soph/UGrad-C' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE   LA1.Line1 IS NOT NULL   AND LX.prospecttype<>3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
UNION            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,3,datediff(d, 0 ,IX.activedate)) as Dte,
             'UA-Soph/UGrad-P' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE  LA2.Line1 IS NOT NULL AND LA1.Line1 IS NULL  AND LX.prospecttype<>3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
UNION            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,3,datediff(d, 0 ,IX.activedate)) as Dte,
             'UP-Soph/UGrad-P' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE LA1.Line1 IS NOT NULL AND LA2.Line1 IS NOT NULL  AND LX.prospecttype<>3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -4
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
UNION            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,24,datediff(d, 0 ,IX.activedate)) as Dte,
             'UC-Soph/UGrad-C' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE  LA1.Line1 IS NOT NULL  AND A.customersizecode-199999 = 1 AND LX.prospecttype<>3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
UNION            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,24,datediff(d, 0 ,IX.activedate)) as Dte,
             'UC-Soph/UGrad-P' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.leadid = IX.leadid
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON LX.primarychapterid = CX.chapterid
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.universityaccountid = A.AccountId
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON MX.SeasonId = SX.seasonId
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON CX.ucodeid = U.ucodeId
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.associatedirectorid = S.SystemUserId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON LX.stateProvinceID=STX1.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and LA1.addressnumber = 1
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and LA2.addressnumber = 2
      WHERE   LA2.Line1 IS NOT NULL AND LA1.Line1 IS NULL  AND A.customersizecode-199999 = 1 AND LX.prospecttype<>3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND coalesce(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
)as temp            
order by dte ,chapter, template            
Go            
Create index ActivityDate on [MSCRM_Helper].dbo.CampaignActSummLetterDirect (dte);            
Go            
0
 
MuffyBunnyCommented:
SSAS can be pretty complex. If you don't already know it, consider it a last resort. Look at temps and table datatypes. They're a way of creating a table on the fly that doesn't actually exist anywhere but the server's memory. Really fast, really efficient. I'll take a look at your code and see if it can be optimized.
0
 
sarahellisAuthor Commented:
Thank you.
0
 
MuffyBunnyCommented:
You weren't kidding about the complexity. This is a juicy one!

If you truncate the table instead of dropping it, you wont have to re-do your indexes. If you don't have any auto-increment fields, you don't even have to truncate, you could just delete everything.
Where is the table getting re-created after dropping it?
Outer Joins are pricey. Only use them when you're sure you have to.
In your join statements, put ()s around the criteria. ie. inner join table1 ON (whatever)
COALESCE is also pricey. ISNULL(LX.InvitationCount,0) produces the same result without the overhead. COALESCE should only be used when you have more than 1 column to consider.
0
 
MuffyBunnyCommented:
btw, what is it that's taking too long? Your table population, or the select for the report?
0
 
sarahellisAuthor Commented:
re: last question:  I had created a view with this info, and the query for the report would take forever.  But, if I first went in and directly select * from view, and re-ran the report, it would go faster.  This morning, after the migration team had updated more data, it took 5 full minutes just to select * from view - much less run a parameter report from it!  

•If you truncate the table instead of dropping it, you wont have to re-do your indexes. If you don't have any auto-increment fields, you don't even have to truncate, you could just delete everything.
no auto-increment fields.  I like the idea of truncating!


•Where is the table getting re-created after dropping it?I don't understand the question.  Everything is being done on different server and different database than the CRM database.


•Outer Joins are pricey. Only use them when you're sure you have to.I have to make sure I get ALL the records from Invitations & Leads, and then whatever records are available from the other tables.  I don't know how else to do that.

•In your join statements, put ()s around the criteria. ie. inner join table1 ON (whatever) OK

•NOLOCK - again, really pricey. Only use them if you have to (http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx)
Absolute requirement from my DBA.  These tables also are the backend of a web app that could be accessed at any time.

•COALESCE is also pricey. ISNULL(LX.InvitationCount,0) produces the same result without the overhead. COALESCE should only be used when you have more than 1 column to consider.Funny - that was a function my DBA recommended.  I'll try yours.

Thank you.  So, is the rest of the stored procedure OK if I change it to...

USE [MSCRM_Helper]            
GO
CREATE PROCEDURE [MSCRM_Helper].uspBuildCampaignActSummLetterDirect
AS
truncate table [MSCRM_Helper].dbo.CampaignActSummLetterDirect
Go  

insert into [MSCRM_Helper].dbo.CampaignActSummLetterDirect
select.....blah blah


Assuming I create the index once it will stay???  And doesn't belong in the stored procedure?

0
 
MuffyBunnyCommented:
Don't hit the bad side of your DBA. If ISNULL doesn't give you a significant improvement, do it the DBA's way. I'm also a DBA. The fact of the matter is you could ask 20 different DBAs about COALESCE vs ISNULL and get 20 different answers. Just try both and see which is faster. You may find it didn't make any difference at all, in which case.. do what your DBA said.

In your previous code, you were dropping the table but not re-creating it. Given that you're going to switch to truncating, that's irrelevant.

Because this is going to be a stored procedure that will empty your table and then re-populate it and this proc will be scheduled to run nightly, you don't have to be incredibly concerned about how long it takes to run (within reason). With that fact... index, index, index. Indexes are costly when writing to the table, but give a huge speed boost when reading from the table. Because the table will be written to, hopefully, in the middle of the night, you don't have to worry about the overhead so much.

I would index every column that's included in a join, especially those outers.

So for
LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.membershipdriveId = MX.MembershipDriveId

Open in new window

Index IX.membershipdriveId and MX.MembershipDriveId

and so on....

Anyplace there's a string comparison, you could index those columns as well, but just do the joins 1st and see where you're at
0
 
sarahellisAuthor Commented:
I really like the ISNULL thing.  I've only ever used it as an SQL function to evaluate a field.  I didn't know T-SQL used it to replace null values.  Very cool.  My DBA won't mind that.

I'm not sure if I can index the source tables (MSCRM.dbo.membershipdriveExtensionBase).  I was talking about indexing the resulting table.  I think CRM is pretty picky about doing ANYTHING in its database.  My report only pulls based on the "dte" field.  So I was only going to index it on that.  Am I misunderstanding something?  This is where I am now...

USE [MSCRM_Helper]            
GO            
            
truncate table [MSCRM_Helper].dbo.CampaignActSummLetterDirect ;            
GO            
            
INSERT INTO [MSCRM_Helper].dbo.CampaignActSummLetterDirect            
from (            
      SELECT  dateadd(d, 0, datediff(d, 0 ,IX.activedate)) as ActiveDate,       
            DateAdd(d,3,datediff(d, 0 ,IX.activedate)) as Dte,
             'GA-Grad-P' as Template,
            IX.name as Invitation,
            U.name as UCODE,
            S.FirstName + ' ' + S.LastName as AD,
            CX.chaptername as Chapter,
            L.FirstName + ' ' + L.LastName as LeadName,
            SX.seasoncode as Season,
            LA1.Line1 as Addr1C, LA1.Line2 as Addr2C, LA1.City as CityC,STX1.PostalAbbreviation as StateC, LA1.PostalCode as ZipC,
            LA2.Line1 as Addr1P, LA2.Line2 as Addr2P, LA2.City as CityP,STX2.PostalAbbreviation as StateP, LA2.PostalCode as ZipP
      FROM MSCRM.dbo.InvitationExtensionBase IX WITH (NOLOCK)       
            INNER JOIN MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON (LX.leadid = IX.leadid)
            INNER JOIN MSCRM.dbo.LeadBase L WITH (NOLOCK) ON (LX.LeadId = L.LeadId)
            LEFT OUTER JOIN MSCRM.dbo.ChapterExtensionBase CX WITH (NOLOCK) ON (LX.primarychapterid = CX.chapterid)
            LEFT OUTER JOIN MSCRM.dbo.AccountBase A WITH (NOLOCK) ON (CX.universityaccountid = A.AccountId)
            LEFT OUTER JOIN MSCRM.dbo.membershipdriveExtensionBase MX  WITH (NOLOCK) ON (IX.membershipdriveId = MX.MembershipDriveId)
            LEFT OUTER JOIN MSCRM.dbo.seasonExtensionBase SX WITH (NOLOCK) ON (MX.SeasonId = SX.seasonId)
            LEFT OUTER JOIN MSCRM.dbo.ucode U WITH (NOLOCK) ON (CX.ucodeid = U.ucodeId)
            LEFT OUTER JOIN MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON (CX.associatedirectorid = S.SystemUserId)
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX1 WITH (NOLOCK) ON (LX.stateProvinceID=STX1.stateprovinceId)
            LEFT OUTER JOIN MSCRM.dbo.stateprovinceExtensionBase STX2 WITH (NOLOCK) ON (LX.PermanentAddressStateProvinceIDse=STX2.stateprovinceId)
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON (LX.LeadId = LA1.ParentId and LA1.addressnumber = 1)
            LEFT OUTER JOIN MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON (LX.LeadId = LA2.ParentId and LA2.addressnumber = 2)
      WHERE LA1.Line1 IS NOT NULL AND L.emailaddress1 NOT LIKE '%@%'  AND LX.prospecttype=3       
            AND IX.invitationtype = 1
            AND LX.countryid in ('74F27E7E-70F9-DE11-BC84-0018FE280F2B', '44F37E7E-70F9-DE11-BC84-0018FE280F2B') --USA & Bahamas GUIDs
            AND  ISNULL(LX.InvitationCount,0)<3
            AND datediff(d, getdate(),IX.activedate)  > -100
            AND IX.invitationaccepted = 0
            AND L.statecode = 0
etc.....

)
Go
)
Go


And one time I should run...

Create index ActivityDate on [MSCRM_Helper].dbo.CampaignActSummLetterDirect (dte);
Go
?

Is that right?
0
 
MuffyBunnyCommented:
No, you're right... you'd only worry about indexes on the table you read from for your report. My bad.

sidenote.. there's also a sql function opposite ISNULL that goes like this..

NULLIF(fieldname, '')  it comes in handy when you know you have a bunch of empty values and want to see them as NULLs. the '' could be virtually anything that you'd rather have represented as NULL

Create the index outside of your proc. It never needs to be dropped and re-created. If you were going to be having a lot of indexes or the data wasn't going to be getting refreshed every night, you'd have to worry about refreshing the indexes from time to time, but that's a different topic and not relevant in this circumstance.

So you should be set. Let me know if your report query speeds up at all.
0
 
sarahellisAuthor Commented:
Oh boy, I'm getting so many errors...
0
 
MuffyBunnyCommented:
Does CampaignActSummLetterDirect already exist? If not, create it and its index 1st.
0
 
sarahellisAuthor Commented:
Narrowed down the errors.  The table does exists but apparently I don't have permission to index it.  I can create it and delete it but not index it?  : P

This is what I'm getting...
Msg 156, Level 15, State 1, Procedure espBuildCampaignActSummLetterDirect, Line 4
Incorrect syntax near the keyword 'truncate'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.


USE [MSCRM_Helper]            
GO            
create procedure [MSCRM_Helper].espBuildCampaignActSummLetterDirect
AS
(      
truncate table [MSCRM_Helper].dbo.CampaignActSummLetterDirect             
            
INSERT INTO [MSCRM_Helper].dbo.CampaignActSummLetterDirect            
      
            SELECT ....

Go
)
Go
0
 
MuffyBunnyCommented:
Why the () after the AS?

USE [MSCRM_Helper]            
GO            
create procedure [MSCRM_Helper].espBuildCampaignActSummLetterDirect
AS
TRUNCATE TABLE [MSCRM_Helper].dbo.CampaignActSummLetterDirect

INSERT INTO [MSCRM_Helper].dbo.CampaignActSummLetterDirect
SELECT....
....
....

GO

You'll have to have your DBA create the index. TRUNCATE can be a tricky one. It's moody! On the msdn page for it, about 1/2 way down the page, you'll see the restrictions. I don't think you have any of the restricted scenarios going on, but go take a look and double check

http://msdn.microsoft.com/en-us/library/ms177570.aspx
0
 
sarahellisAuthor Commented:
That's it!  Thank you, thank you, thank you for donating your valuable time to help me with this.
0
 
MuffyBunnyCommented:
No problem
0
 
sarahellisAuthor Commented:
Fantastic assistance!  MuffyBunny Rocks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now