Good afternoon everyone, I have written a view that pulls customer demographic infomration as well as pulling data from multiple scalar-valued functions. I am using this view to pull and send data from one database to another in the same SQL server. The problem that I am having is that I am running this import as a scheduled job in windows. The job is taking almost 24 hours to complete this task. The total number of records that are being pulled is around 21,000+. I have tried removing the functions from the view and it only takes the view 20 seconds to pull the demographic information from the same 21,000+ records but when I add the function calls this is where the time to complete goes through the roof. Has anyone encountered this before if so what would you suggest doing? Any help would be appreciated.
Here is the syntax for my view:
SELECT TOP 100PERCENT CUS_EMAILAS Email, CUS_CUSTNUMAS MemberID, CUS_PREFIXAS Prefix, CUS_FNAMEAS FirstName, CUS_LNAMEAS LastName, CUS_SUFFIXAS Suffix, CUS_TITLEAS Title, CUS_STATEAS State, CUS_COUNTRYAS Country, CUS_ZIPAS ZipCode, CUS_SEXAS Gender,CAST(CUS_DEMCODEAAS nvarchar(20)) +',' +CAST(CUS_DEMCODEBAS nvarchar(20)) +',' +CAST(CUS_DEMCODECAS nvarchar(20)) +',' +CAST(CUS_DEMCODEDAS nvarchar(20))AS DemoCodes, dbo.GetSubScribedDateMLA(CUS_CUSTNUM, CUS_EMAIL)AS MLASubscribedDate, dbo.GetSubScribedDateMLP(CUS_CUSTNUM, CUS_EMAIL)AS MLPSubscribedDate, dbo.GetSubScribedDateLDC(CUS_CUSTNUM, CUS_EMAIL)AS LDCSubscribedDate, dbo.GetMLAExpiration(CUS_CUSTNUM, CUS_EMAIL)AS MLASubExpireDate, dbo.GetMLPExpiration(CUS_CUSTNUM, CUS_EMAIL)AS MLPSubExpireDate, dbo.GetLDCExpiration(CUS_CUSTNUM, CUS_EMAIL)AS LDCSubExpireDate, dbo.IsProspect(CUS_CUSTNUM, CUS_EMAIL)AS AGMProspect, dbo.IsCurrentCustomer(CUS_CUSTNUM, CUS_EMAIL)AS AGMCurrentCustomer, dbo.IsMLAMember(CUS_CUSTNUM, CUS_EMAIL)AS MLAMember, dbo.IsMLPMember(CUS_CUSTNUM, CUS_EMAIL)AS MLPMember, dbo.IsLDCMember(CUS_CUSTNUM, CUS_EMAIL)AS LDCMember, dbo.CalculateTotalRevenue(CUS_CUSTNUM, CUS_EMAIL)AS AGMTotalRevenue, dbo.GetPubCodes(CUS_CUSTNUM, CUS_EMAIL)AS ProductsPurchased, dbo.GetEmailType(CUS_CUSTNUM, CUS_EMAIL, CUS_RENT_EMAIL)AS EmailType, CUS_COMPANYAS Company, CUS_CITYAS CityFROM dbo.CUSWHERE (CUS_EMAILISNOT NULL)AND (CUS_EMAIL <>'')AND (CUS_EMAIL_VALID ='Y')AND (CUS_EMAILLIKE'%@.%.%')AND (CUS_RENT_EMAIL ='Y'OR CUS_RENT_EMAIL ='R'OR CUS_RENT_EMAIL ='I')AND (CHARINDEX(' ', CUS_EMAIL) = 0)AND (CUS_EMAILNOT LIKE'@.%') Thanks in advance
Michael Reyeros
Windows based functions are not for moving data between databases, you use SQL Server Agent Jobs for that. Run a search for SQL Server Agent Job in SQL Server BOL (books online). Hope this helps.
|||Well actually what I have done is create a dtsx package in SQL 2005. This package is calling the above view to pull all the records from one database table and then for each one ofthe records return I am sending it to a stored procedure in another database. I then built a windows application that simply calls the dtsx package and runs it. This exe I have set it to run in the windows scheduler as a scheduled task.|||
Look for an extended stored proc called xp_cmdshell, it is disabled by default in 2005 enable it and use the instructions in the thread below and post again if it is not working.
http://forums.asp.net/thread/1358665.aspx
|||But I am not sure exactly what this is supposed to do or how I hsould implement this in my siutation?
|||That is what is used to move data between database in all platforms with DTS not Windows service, it can move gigs in one day for you nothing works better, if it is there I would have known. Hope this helps.|||If I need to schedule this to run on a nightly basis, how would I run this and from where?|||
The first two links show you how to use the Agent to schedule Jobs for most admin tasks in SQL Server. The last link shows you system stored procedures you can use to create schedules and Jobs. I worked for a bank that used it to move deposits four hours a day five days a week your data is very small. Post again if you still need help, get it work and you can automate most operations in your application. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms139805.aspx
http://msdn2.microsoft.com/en-us/library/ms141701.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp
|||OK the only problem that I have is that two of the packages that I am running rely on an ODBC connection to a very antiquated system, QuickFill, that is being used here in the office. When I try to run the job in SQL server as an agent job this is not allowed because the ODBC connection relies on two mapped drives and this does not seem to be allowed in SQL server.|||Try using UNC instead of mapped drive you can create a proxy account with your admin context for the Agent to run it and the permissions are covered in the links in the thread I gave you. Try the link below and read up one how to get the correct mapping with ODBC. Sorry forgot to add the link. Hope this helps.
http://www.sqlteam.com/item.asp?ItemID=125
sql