September 17, 2015 at 4:39 am
Hi all
I have a query which has data injected into it, which is returned from a user defined function. This is very slow and I'm looking for a way to speed it up.
The suggestion I'm working with is to have the data, retuned by the function, prepopulated into a temporary table using a FAST_FORWARD cursor.
Having not worked with cursors before I headed to google to find out more and was almost immediately swamped with posts saying "dont use cursors" - "stay away from cursors" - "cursors were designed by the devil to do nasty things to your SQL from behind" etc etc.
Can anyone explain to me whyI shouldn't use this cursor to prepopulate my temp table?
I was under the impression that it would much quicker and more efficient than an equivalent while loop. Have I got this backwards?
Thanks in advance
September 17, 2015 at 4:54 am
There is an huge number of people on here that will be able to give you a better answer, in more detail, than I ever could. The long and the short of it is that SQL Server is set up to handle set based operations rather than the row-by-row operations in a cursor or WHILE loop. There is also the consideration that a WHILE loop is actually a cursor in disguise.
It's highly likely that what you're trying to do can be accomplished without using either a cursor or a WHILE loop. If you can post the code for the function and some sample data I'm sure that some of the real experts around here will be able to help out.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 18, 2015 at 6:16 am
cursors are the best way to slow down an application. This is because SQL Server is optimized for set-based operations where as cursor will work row by row.
So why do they exist?
well when there is a time when you know that a row-by-row approach is needed like restoring many database, at that time you can write cursor that perform restoration of one by one database and only for a one off maintenance/patch-script operation. In this case also you can write while loop, this would also avoid too many locks and would do the job quicker.
However you please post your code so it would be easier to help you in your problem.
September 18, 2015 at 6:30 am
There are those rare occasions where a cursor is necessary, but they're rare. When dealing with data, a set-based solution is usually possible. Like others have said, SQL Server cursors are slow and inefficient. If you post the DDL for your tables and functions involved and some consumable DML to populate some test data, we can see if there's a good solution. The good people on this site are usually pretty good at finding high-performance solutions if one is possible.
I'm going to be out for a long weekend starting in about 30 minutes, but I'll check on this thread when I return. If you have any questions about what we need, please see the link in my signature.
September 18, 2015 at 2:43 pm
james.brown2 (9/17/2015)
Hi allI have a query which has data injected into it, which is returned from a user defined function. This is very slow and I'm looking for a way to speed it up.
The suggestion I'm working with is to have the data, retuned by the function, prepopulated into a temporary table using a FAST_FORWARD cursor.
Having not worked with cursors before I headed to google to find out more and was almost immediately swamped with posts saying "dont use cursors" - "stay away from cursors" - "cursors were designed by the devil to do nasty things to your SQL from behind" etc etc.
Can anyone explain to me whyI shouldn't use this cursor to prepopulate my temp table?
I was under the impression that it would much quicker and more efficient than an equivalent while loop. Have I got this backwards?
Thanks in advance
Prior to SQL Server 2005, there were many things that were more efficient to do in a cursor. However, using a cursor prevents SQL Server from doing things efficiently. Remember that SQL Server will usually work best when processing things in a set-based manner. Using a cursor, or any method that works on rows one-by-one, has been given the term "RBAR", for "Row-By-Agonizing-Row"
An equivalent cursor can be better than a while loop (though remember that a cursor has it's own while loop). While loops will typically have even more I/O activity than the equivalent cursor. However, IMHO, both are still a cursor. Set-based will still blow you away.
Here's an example. Use profiler or Extended Events to see which is better (monitor Duration, CPU, Reads). This uses a cursor, while-loop and set-based to do the same task - get a list of objects from master.sys.objects.
/*
RBAR = Row By Agonizing Row
*/
/*
Use profiler to show these three batches.
*/
GO
-- cursor
USE master;
DECLARE @msg VARCHAR(200), @StartTime DATETIME;
SET @StartTime = GETDATE();
SET @msg = REPLICATE('*', 80) + CHAR(10) + 'CURSOR';
RAISERROR(@msg, 10, 1) WITH NOWAIT;
DECLARE @object_name NVARCHAR(300);
DECLARE cObjs CURSOR LOCAL FAST_FORWARD FOR
SELECT OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id)
FROM sys.objects
ORDER BY object_id;
OPEN cObjs;
FETCH NEXT FROM cObjs INTO @object_name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @object_name;
FETCH NEXT FROM cObjs INTO @object_name;
END;
CLOSE cObjs;
DEALLOCATE cObjs;
SELECT 'CURSOR', DATEDIFF(MILLISECOND, @StartTime, GETDATE()) AS [MilliSeconds];
GO
-- while loop - is it any better?
USE master;
DECLARE @msg VARCHAR(200), @StartTime DATETIME, @StartReads BIGINT;
SET @StartTime = GETDATE();
SET @msg = REPLICATE('*', 80) + CHAR(10) + 'WHILE LOOP';
RAISERROR(@msg, 10, 1) WITH NOWAIT;
DECLARE @object_name NVARCHAR(300),
@object_id INTEGER,
@counter INTEGER;
SELECT @counter = COUNT(*)
FROM sys.objects;
SELECT TOP (1)
@object_id = OBJECT_ID
FROM sys.objects
ORDER BY object_id;
WHILE @counter > 0
BEGIN
SELECT @object_name = OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id)
FROM sys.objects
WHERE object_id = @object_id;
RAISERROR('counter: %i, object_id: %i; object_name: %s', 10, 1, @counter, @object_id, @object_name) WITH NOWAIT;
SELECT TOP (1)
@object_id = object_id
FROM sys.objects
WHERE object_id > @object_id
ORDER BY object_id;
SET @counter -= 1;
END;
SELECT 'WHILE LOOP', DATEDIFF(MILLISECOND, @StartTime, GETDATE()) AS [MilliSeconds];
GO
-- set-based
DECLARE @msg VARCHAR(200), @StartTime DATETIME;
SET @StartTime = GETDATE();
SET @msg = REPLICATE('*', 80) + CHAR(10) + 'SET BASED';
RAISERROR(@msg, 10, 1) WITH NOWAIT;
SELECT OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id)
FROM sys.objects
ORDER BY object_id;
SELECT 'SET BASED', DATEDIFF(MILLISECOND, @StartTime, GETDATE()) AS [MilliSeconds];
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 21, 2015 at 4:14 am
thanks for the responses - the query I am currently faced with is this:
SELECT --TOP 100
S.s_id, MEQ_Core.e_id,
Group_id = ATT.ma_activitymodule,
StudentID = S.s_studentreference,
ULN = PD.pd_uniquelearnerno,
Surname = P.p_surname,
Forenames = P.p_forenames,
DoB = cast(P.p_dob as date),
AgeAtStart = cast(round(abs(dbo.monthsbetween(P.p_dob,MEG.e_start)/12),0,1) as int),
HomePostCode = ISNULL(AD.a_reference,''),
LDD1924 = CASE WHEN FC.fc_reference = '14' THEN 'Y' ELSE 'N' END,
InILR = CASE WHEN EIQ.ei_enrolmentisr = -1 THEN 'Y' ELSE 'N' END,
Overseas = CASE WHEN FC.fc_name = 'Overseas' THEN 'Y' ELSE 'N' END,
Hrs = COALESCE ( EIY.ei_ilrplanlearnhours, 0 ) + COALESCE ( EIY.ei_ilrplaneephours, 0 ),
NoFormalQuals = CASE WHEN QL.NoFormalQuals = 1 THEN 'Y' ELSE 'N' END,
FS_Eng = MX.FS_Eng,
FS_Maths = MX.FS_Mat,
GCSE_Eng = MX.GCSE_Eng,
GCSE_Eng_Lit = MX.GCSE_Eng_Lit,
GCSE_Eng_LitAndLang = MX.GCSE_Eng_LitAndLang,
GCSE_Maths = MX.GCSE_Mat,
GCSE_Biology = MX.GCSE_Biol,
GCSE_Chemistry = MX.GCSE_Chem,
GCSE_Physics = MX.GCSE_Phys,
GCSE_Science = MX.GCSE_Sci,
GCSE_Science_Double = MX.GCSE_Sci_Double,
GCSE_Science_Triple = MX.GCSE_Sci_Triple,
GCSE_Additional_Science = MX.GCSE_AddSci,
GCSE_Additional_Science_Double = MX.GCSE_AddSci_Double,
GCSE_Additional_Science_Triple = MX.GCSE_AddSci_Triple,
iGCSE_Eng = MX.iGCSE_Eng,
iGCSE_Maths = MX.iGCSE_Mat,
BKSB_Eng = null,
BKSB_Maths = null,
CoreAim = Q_Core.m_reference, -- this is the course in which the learner will be placed in the report
CoreAimTitle = Q_Core.m_name,
CoreFac = DEPT.d_reference,
CoreTeam = LEFT(TEAM.s_reference, CHARINDEX('-', TEAM.s_reference) - 1),
CoreCC = TEAM.s_reference,
GroupCode = GRP.m_reference,
GroupName = GRP.m_name,
GroupCC = SEC.s_reference,
ProgCode = PRG.m_reference,
AimType = case when MEG.e_id= GC.dbo.FUN_MainAim_GRP ('2015-16',S.s_studentreference) then 0
when GRP.m_reference like 'GCSEENG%' then 2
when GRP.m_reference like 'GCSEMAT%' then 3
when GRP.m_reference like 'FSENG%' then 4
when GRP.m_reference like 'FSNUM%' then 5
when GRP.m_reference like 'EEP%' then 6
when GRP.m_reference like 'T0%' then 7
when GRP.m_reference like 'WEXMOD%' then 8
when GRP.m_reference like 'PPD%' then 9
when GRP.m_reference like 'GCC%' then 10
else 1 end
,
MARKED = sum(ATT.MARKED),
UNMARKED = sum(ATT.UNMARKED),
POSITIVE = sum(ATT.POSITIVE)
FROM dbo.capd_student S with (nolock)
INNER JOIN dbo.capd_person P with (nolock)
ON P.p_id=S.s_id
LEFT JOIN dbo.capd_personaldetails PD with (nolock)
ON PD.pd_id=P.p_id
LEFT JOIN ulive.dbo.capd_address AD with (nolock)
ON AD.a_id = P.p_personaddress
INNER JOIN dbo.capd_moduleenrolment MEG with (nolock)
ON MEG.e_student=S.s_id
INNER JOIN dbo.capd_moduleenrolment MEY with (nolock)
ON MEY.e_id=MEG.e_parent
INNER JOIN dbo.capd_moduleenrolment MEQ with (nolock)
ON MEQ.e_id=MEY.e_parent
INNER JOIN dbo.capd_module GRP with (nolock)
ON GRP.m_id=MEG.e_module
INNER JOIN dbo.capd_section SEC with (nolock)
ON SEC.s_id=GRP.m_modulesection
INNER JOIN dbo.capd_offering LINKYG with (nolock)
ON LINKYG.o_destination=GRP.m_id
INNER JOIN dbo.capd_module YR with (nolock)
ON YR.m_id=LINKYG.o_source
INNER JOIN dbo.capd_offering LINKQY with (nolock)
ON LINKQY.o_destination=YR.m_id
INNER JOIN dbo.capd_module Q with (nolock)
ON Q.m_id=LINKQY.o_source
INNER JOIN dbo.capd_offering LINKPQ with (nolock)
ON LINKPQ.o_destination=Q.m_id
INNER JOIN dbo.capd_module PRG with (nolock)
ON PRG.m_id=LINKPQ.o_source
INNER JOIN dbo.capd_feeconcession FC with (nolock)
ON FC.fc_id=MEY.e_feeconcession
INNER JOIN dbo.capd_enrolmentisr EIY with (nolock)
ON EIY.ei_id=MEY.e_id
INNER JOIN dbo.capd_enrolmentisr EIQ with (nolock)
ON EIQ.ei_id=MEQ.e_id
LEFT JOIN GC.dbo.newRegCap_TEST ATT with (nolock)
ON ATT.rm_student=S.s_id
AND ATT.ma_activitymodule=GRP.m_id
AND ATT.s_academicyear=LEFT('2015-16',4)
INNER JOIN dbo.capd_moduleenrolment MEQ_Core with (nolock)
-- This is the function that is taking too long
ON MEQ_Core.e_id=GC.dbo.FUN_MainAim_Q ('2015-16',S.s_studentreference)
AND MEQ_Core.e_student=S.s_id
INNER JOIN dbo.capd_module Q_Core with (nolock)
ON Q_Core.m_id=MEQ_Core.e_module
INNER JOIN dbo.capd_department DEPT with (nolock)
ON DEPT.d_id = Q_Core.m_moduledept
INNER JOIN dbo.capd_section TEAM with (nolock)
ON TEAM.s_id=Q_Core.m_modulesection
LEFT JOIN GC.dbo.tblMaxGradeForEngMathSci MX with (nolock)
ON MX.s_studentreference = S.s_studentreference
LEFT JOIN GC.dbo.QOE_Learners QL with (nolock)
ON QL.StudentID=S.s_studentreference
WHERE 1=1
AND YR.m_type = 'Yr'
AND GRP.m_type = 'G'
AND YR.m_start <= dbo.FN_GetStartEnd(LEFT('2015-16',4), 'E')
AND YR.m_end >= dbo.FN_GetStartEnd(LEFT('2015-16',4), 'S')
AND MEG.e_status NOT IN ('4','9')
AND MEY.e_status NOT IN ('4','9')
AND MEQ.e_status NOT IN ('4','9')
AND DEPT.d_reference IN (N'CNEB',N'DOSS',N'FRCH',N'MWDC',N'SBUS',N'SXTH',N'VPRS')
AND LEFT(TEAM.s_reference, CHARINDEX('-', TEAM.s_reference) - 1) IN (N'ALFC',N'ALSP',N'AMWC',N'BISM',N'CONS',N'EEMV',N'EQAR',N'ERCH',N'FCBT',N'FRCH',N'HABT',N'HCAT',N'HLGF',N'PRTL',N'PRTW',N'PSSO',N'RSPT',N'SCLN',N'SDEV',N'SKIN',N'SKLF',N'SPLG',N'TCCH',N'TTGN',N'VCFC',N'VCSP',N'WKBL')
AND PRG.m_reference IN (N'A200LF0A001P-151601',N'A200LF0A001P-151601',N'AC01LF1A001P-151601',N'AC02LF1A005P-151601',N'AC02LP1A005P-141601',N'AC02LP1A005P-151601',N'AC04LF1A001P-151601',N'AC05LF1A001P-151601',N'AC05LF1A002P-151601',N'AC08LF1A001P-151601',N'AC10LF0A001P-151601',N'AC10LF1A001P-141502',N'AC10LF1A001P-151601',N'AC10LF1A001P-151602',N'AC11LF1A001P-151601',N'AC12LF1A001P-151601',N'AC12LF1A002P-151601',N'AL00LP0A003P-151601',N'AS00LF0A001P-151601',N'AS00LF0A001P-151601',N'BE14LB1A001P-151601',N'BE14LM1A654P-151601',N'BE14LS1S101P-151601',N'BE14LS1S203P-151601',N'BE14LS1S305P-151601',N'BE14LS1S407P-151601',N'BE14LS1S509P-151601',N'BE14LS1S601P-151601',N'BE14LS1S603P-151601',N'BE14LS1S605P-151601',N'BE14LS1S607P-151601',N'BE14LS1S609P-151601',N'BE14LS1S801P-151601',N'BE14LS1S802P-151601',N'BS14LF1A009P-151601',N'BS14LF1A011P-151601',N'BS14LF1A012P-151601',N'BS14LF1A013P-151601',N'BS14LF1A014P-151601',N'BS14LF1A015P-151601',N'BS14LF1A016P-151601',N'BS14LF1A018P-151601',N'CG02LF1A010P-151601',N'CG02LF1A015P-151601',N'CN08LP1A006P-151601',N'CN12LF0A003P-151601',N'EC02LP1A001P-151601',N'EC11LF1A001P-151601',N'ED02LF1A001P-151701',N'ED02LF1A003P-151601',N'ED02LF1A004P-151601',N'ED02LF1A005P-151601',N'ED02LF1A006P-151601',N'ED02LF1A007P-151601',N'ED02LF1A008P-151601',N'ED02LF1A009P-151601',N'ED03LF1A001P-141601',N'ED05LF1A001P-151601',N'ED08LF1A001P-151601',N'ED08LF1A002P-151601',N'ED10LF1A001P-151601',N'ED11LF1A001P-151601',N'ED11LF1A003P-151601',N'ED11LF1A004P-151601',N'ED11LF1A005P-151601',N'ED11LF1A006P-151601',N'ED11LF1A007P-151601',N'ED11LF1A008P-151601',N'ER0003P-141601',N'ER0025P-141601',N'ER0025P-141602',N'ER0027P-131601',N'FCADB-151601',N'FCAPPSCPROG3-151701',N'FCBUSP-D-141601',N'FCBUSP-D-141601',N'FCLEVEL2_P-151601',N'FCLEVEL2_P-151601',N'FCLEVEL3P_A2-151601',N'FCLEVEL3P_A2-151601',N'FCLEVEL3P_AL-151701',N'FCLEVEL3P_AL-151701',N'FCLEVEL3P_AS-151601',N'FCLEVEL3P_AS-151601',N'FCLEVEL3P_DP-151601',N'FCLEVEL3P_ED-141601',N'FCLEVEL3P_ED-141601',N'FD02LF1A001P-151601',N'FD02LF1A003P-151601',N'FD02LF1A004P-151601',N'FD02LF1A006P-151601',N'FD02LF1A008P-151601',N'FD05LF1A001P-151601',N'FD06LF1A001P-151601',N'FD10LF1A001P-151601',N'FD10LF1A002P-151601',N'LC05LP1A001P-151601',N'LC05LP1A002P-151601',N'LC05LP1A003P-151601',N'LC05LP1A005P-151601',N'LC06LP1A001P-151601',N'LC07LP1A002P-151601',N'LC08LP1A001P-151601',N'LC10LP1A002P-151601',N'LC10LP1A005P-151601',N'LC10LP1A006P-151601',N'LC10LP1A007P-151601',N'LC10LP1A008P-151601',N'LC10LP1A011P-151601',N'LC10LP1A013P-151601',N'LC10LP1A014P-151601',N'LC10LP1A015P-151601',N'LC10LP1A016P-151601',N'LC10LP1A017P-151601',N'LC10LP1A018P-151601',N'LC14LP1A001P-151601',N'LC14LP1A003P-151601',N'LC14LP1A004P-151601',N'LC14LP1A005P-151601',N'LC14LP1A006P-151601',N'LC14LP1A007P-151601',N'LC14LP1A008P-151601',N'LC14LP1A009P-151601',N'LC14LP1A010P-151601',N'LC14LP1A011P-151601',N'LC14LP1A012P-151601',N'NA02LF0A007P-151601',N'NC02LF1A005P-151601',N'NC02LP1D005P-141601',N'NC02LP1D005P-151701',N'ND02LF1A001P-141601',N'ND02LF1A004P-141601',N'ND02LF1A004P-141601',N'ND02LF1A007P-141601',N'ND02LF1A009P-141601',N'ND02LF1A009P-151601',N'ND02LF1A010P-141601',N'ND02LF1A014P-141601',N'ND02LF1A014P-141601',N'ND02LF1A015P-141601',N'ND02LF1A016P-141601',N'ND02LF1A016P-151601',N'ND02LF1A017P-141601',N'ND02LF1A017P-151601',N'ND05LF1A001P-141601',N'ND05LF1A001P-141601',N'ND06LF1A001P-141601',N'ND06LF1A001P-141601',N'ND08LF1A001P-141601',N'ND08LF1A003P-141601',N'ND08LF1A004P-151601',N'ND10LF1A001P-141601',N'ND10LF1A004P-151601',N'ND10LF1A004P-151601',N'ND11LF1A003P-141601',N'ND11LF1A014P-141601',N'ND11LF1A015P-141601',N'ND11LF1A017P-141601',N'NV02LP1A011P-141601',N'NV02LP1A011P-151701',N'NV02LP1D010P-141601',N'NV02LP1D010P-151701',N'NV03LF0A001P-151601',N'NV03LF0A001P-151601',N'NV03LP1A010P-141601',N'NV03LP1A010P-151701',N'NV03LP1B015P-141601',N'NV03LP1B015P-151701',N'NV04LF0A001P-151601',N'NV04LF1A002P-151601',N'NV04LF1A003P-151601',N'NV04LP1A004P-151701',N'NV04LP3A004P-151601',N'NV08LF0A002P-151601',N'NV08LP1A002P-141601',N'NV09LF0A006P-151601',N'NV09LF1A002P-151601',N'NV09LF1A003P-151601',N'NV09LF1A004P-151601',N'NV10LP1A006P-151601',N'OA04LP1A001P-151601',N'OC02LP0A035P-151601',N'OC02LP0A037P-151601',N'OC02LP1A002P-151601',N'OC02LP1A007P-151601',N'OC02LS1A038P-151601',N'OC02LS1A038P-151602',N'OC02LS1A038P-151603',N'OC03LE1A026P-151601',N'OC03LE1A027P-151601',N'OC03LF0A007P-151601',N'OC03LF0A008P-151601',N'OC03LF0A010P-151601',N'OC03LF0A012P-151601',N'OC03LF0A014P-151601',N'OC03LF1A019P-151601',N'OC03LP1A015P-141601',N'OC03LP1A015P-151701',N'OC03LP1A024P-141501',N'OC03LP1A024P-151601',N'OC03LP3A015P-151601',N'OC04LE1A002P-151601',N'OC04LE1A003P-151601',N'OC04LE1A012P-151601',N'OC06LF0A001P-151601',N'OC09LF0A001P-151601',N'OC09LF1A001P-151601',N'OC09LF1A004P-151601',N'OC10LF1A003P-151601',N'OC11LF1A001P-151601',N'OC14LF0A003P-151601',N'OC14LP1A001P-151601',N'OD01LF0A001P-141601',N'OD01LF0A015P-151601',N'OD02LF0A004P-151601',N'OD02LF0A005P-151601',N'OD02LF0A007P-151601',N'OD02LF1A004P-141601',N'OD02LF1A004P-151601',N'OD02LF1A011P-151601',N'OD02LF1A016P-141601',N'OD02LF1A017P-151601',N'OD02LF1A018P-151601',N'OD02LF1A019P-151601',N'OD02LF1A026P-141601',N'OD02LF1A029P-151601',N'OD02LF1A030P-151601',N'OD02LF1A031P-151601',N'OD02LF1A032P-151601',N'OD02LF1A033P-151601',N'OD02LF1A034P-151601',N'OD02LP0A002P-141602',N'OD02LP0A002P-151601',N'OD02LP1A011P-141601',N'OD03LF1A019P-151601',N'OD03LF1A020P-151601',N'OD03LF1A021P-151601',N'OD04LF0A001P-151601',N'OD04LF1A001P-141601',N'OD04LF1A002P-151601',N'OD04LF1A004P-151601',N'OD05LF1A008P-151601',N'OD06LF1A004P-151601',N'OD06LF1A005P-151601',N'OD08LF0A002P-151601',N'OD08LF0A004P-151601',N'OD08LF0A005P-151601',N'OD08LF1A001P-151601',N'OD08LF1A001P-151601',N'OD08LF1A002P-151601',N'OD08LF1A004P-151601',N'OD08LF1A007P-151601',N'OD08LF1A008P-151601',N'OD08LF1A010P-151601',N'OD08LF1A011P-151601',N'OD08WP1A007P-131601',N'OD09LE1A003P-151601',N'OD09LF0A002P-151601',N'OD09LF1A001P-151601',N'OD09LF1A004P-151601',N'OD09LF1A005P-151601',N'OD09LF1A006P-141601',N'OD09LP1A003P-151601',N'OD09LP1A006P-151601',N'OD10LF1A002P-141601',N'OD10LF1A002P-151701',N'OD10LF1A007P-141601',N'OD10LF1A008P-151601',N'OD10LF1A009P-151601',N'OD10LF1A010P-151601',N'OD11LF0A002P-141601',N'OD11LF0A002P-141601',N'OD11LF0A003P-151601',N'OD11LF0A004P-141601',N'OD11LF0A005P-151601',N'OD11LF1A002P-151601',N'OD11LF1A004P-151601',N'OD11LF1A005P-151601',N'OD11LF1A006P-151601',N'OD11LF1A007P-151601',N'OD11LF1A008P-151601',N'OD11LF1A009P-151601',N'OD11LF1A010P-151601',N'OD14LF0A001P-151601',N'PC03LE1A004P-151601',N'PC03LE1A005P-151601',N'PTPL14LS001P-151601',N'PTPL14LS002P-151601',N'PTPL14LS003P-151601',N'PTPL14LS004P-151601',N'PTPL14LS005P-151601',N'PTPL14LS006P-151601',N'PTPL14LS007P-151601',N'PTPL14LS008P-151601',N'PTPL14LS009P-151601',N'PTPL14LS010P-151601',N'PTPL14LS011P-151601',N'PTPL14LS012P-151601',N'PTPN14LS001P-151601',N'PTPN14LS002P-151601',N'PTPN14LS003P-151601',N'PTPN14LS004P-151601',N'PTPN14LS005P-151601',N'PTPN14LS006P-151601',N'PTPN14LS007P-151601',N'PTPN14LS008P-151601',N'PTPN14LS009P-151601',N'PTPN14LS010P-151601',N'PTPN14LS011P-151601',N'PTPN14LS012P-151601',N'RDC10LP0A02P-141601',N'RIWESOL001P-151601',N'RIWESOL002P-151601',N'SD04LF1A001P-151601',N'SD11LF1A001P-151601',N'SPORTIV8002P-141601',N'TESTPROG-151601')
GROUP BY S.s_id, MEQ_Core.e_id,
S.s_studentreference,
P.p_surname,
P.p_forenames,
ATT.ma_activitymodule,
MEG.e_id,
MX.FS_Eng,
MX.FS_Mat,
MX.GCSE_Eng,
MX.GCSE_Mat,
MX.GCSE_Biol,
MX.GCSE_Chem,
MX.GCSE_Phys,
MX.GCSE_Sci,
MX.GCSE_Sci_Double,
MX.GCSE_Sci_Triple,
MX.GCSE_AddSci,
MX.GCSE_AddSci_Double,
MX.GCSE_AddSci_Triple,
Q_Core.m_reference,
Q_Core.m_name,
DEPT.d_reference,
TEAM.s_reference,
GRP.m_reference,
GRP.m_name,
SEC.s_reference,
PRG.m_reference,
PD.pd_uniquelearnerno,
P.p_dob,
MEG.e_start,
AD.a_reference,
FC.fc_reference,
EIQ.ei_enrolmentisr,
EIY.ei_ilrplanlearnhours,
EIY.ei_ilrplaneephours,
FC.fc_name,
QL.NoFormalQuals,
MX.iGCSE_Eng,
MX.iGCSE_Mat,
MX.GCSE_Eng_Lit,
MX.GCSE_Eng_LitAndLang
And the function that is taking too long to run is:
CREATE FUNCTION [dbo].[FUN_MainAim_Q] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS Numeric(16, 0)
AS
BEGIN
Declare @EnrolID Numeric(16, 0)
SET @EnrolID = (
SELECT TOP 1
MEQ.e_id
FROM ulive.dbo.capd_moduleenrolment MEY
INNER JOIN ulive.dbo.capd_module YR
ON YR.m_id = MEY.e_module
INNER JOIN ulive.dbo.capd_enrolmentisr EIY
On EIY.ei_id = MEY.e_id
INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
ON MEQ.e_id = MEY.e_parent
INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
ON MEQ.e_id = EIQ.ei_id
INNER JOIN ulive.dbo.capd_student S
ON S.s_id = MEY.e_student
WHERE
YR.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
AND YR.m_end >= LEFT ( @Session, 4 ) + '-08-01'
AND YR.m_type = 'Yr'
AND MEY.e_status NOT IN ( '0', '5', '6', '9' )
AND S.s_studentreference = @StudentRef
AND (
EIQ.ei_enrolmentisr = -1
)
ORDER BY
CASE WHEN EIQ.ei_ilra15 = '' THEN '99' ELSE EIQ.ei_ilra15 END,
CASE WHEN EIQ.ei_ilraimtype = '2' THEN '4.5' ELSE EIQ.ei_ilraimtype END DESC,
MEY.e_status ASC,
COALESCE ( EIY.ei_ilrplanlearnhours, 0 ) + COALESCE ( EIY.ei_ilrplaneephours, 0 ) DESC,
MEY.e_start ASC,
MEQ.e_start ASC
)
RETURN @EnrolID
END
The solution I was originally looking at was to create a temporary table:
CREATE TABLE [dbo].[cl_temporarytable](
[cl_sid] [numeric](16, 0) NOT NULL,
[cl_sreference] [varchar](20) NULL,
[cl_eid] [numeric](16, 0) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20150917-174709] ON [dbo].[cl_temporarytable]
(
[cl_sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Use the cursor to populate this table with the data from the function:
DECLARE @StudentID numeric(16, 0)
DECLARE @StudentRef varchar(20)
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT
S.s_id,
S.s_studentreference
FROM ulive.dbo.capd_student S
INNER JOIN ulive.dbo.capd_moduleenrolment MEP
ON MEP.e_student=S.s_id
WHERE MEP.e_start >= '01-AUG-2015'
ORDER BY
S.s_id
OPEN cur
FETCH NEXT FROM cur INTO
@StudentID,
@StudentRef
while @@FETCH_STATUS = 0 BEGIN
INSERT INTO dbo.cl_temporarytable
SELECT
cl_sid = @StudentID,
cl_sreference = @StudentRef,
cl_eid = GC.dbo.FUN_MainAim_Q ('2015-16',@StudentRef)
FETCH NEXT FROM cur INTO
@StudentID,
@StudentRef
END
CLOSE cur
DEALLOCATE cur
And the alter the original query to look at the temporary table instead of the function:
INNER JOIN dbo.capd_moduleenrolment MEQ_Core with (nolock)
-- ON MEQ_Core.e_id=GC.dbo.FUN_MainAim_Q ('2015-16',S.s_studentreference)
ON MEQ_Core.e_id in (select cl_eid from dbo.cl_temporarytable where cl_sid = S.s_id)
AND MEQ_Core.e_student=S.s_id
But if there is a better way of being able to populate this temporary table, or deliver the function's data to the query, I am more than happy to investigate!
September 21, 2015 at 4:34 am
Could you cross apply [capd_moduleenrolment], instead of inner join (including the tables that then JOIN to it, AND there relevant Where clause bits and pieces)?
September 21, 2015 at 8:49 am
You could change your function to an iTVF (inline table valued function) which should be faster.
Something like this:
--Added an i as prefix to prevent code malfunctions where the original function is used.
CREATE FUNCTION [dbo].[iFUN_MainAim_Q] (@Session VARCHAR(10), @StudentRef VARCHAR(20)) RETURNS TABLE
AS
RETURN SELECT TOP 1
MEQ.e_id
FROM ulive.dbo.capd_moduleenrolment MEY
INNER JOIN ulive.dbo.capd_module YR
ON YR.m_id = MEY.e_module
INNER JOIN ulive.dbo.capd_enrolmentisr EIY
On EIY.ei_id = MEY.e_id
INNER JOIN ulive.dbo.capd_moduleenrolment MEQ
ON MEQ.e_id = MEY.e_parent
INNER JOIN ulive.dbo.capd_enrolmentisr EIQ
ON MEQ.e_id = EIQ.ei_id
INNER JOIN ulive.dbo.capd_student S
ON S.s_id = MEY.e_student
WHERE
YR.m_start <= LEFT ( @Session, 2 ) + RIGHT ( @Session, 2 ) + '-07-31'
AND YR.m_end >= LEFT ( @Session, 4 ) + '-08-01'
AND YR.m_type = 'Yr'
AND MEY.e_status NOT IN ( '0', '5', '6', '9' )
AND S.s_studentreference = @StudentRef
AND (
EIQ.ei_enrolmentisr = -1
)
ORDER BY
CASE WHEN EIQ.ei_ilra15 = '' THEN '99' ELSE EIQ.ei_ilra15 END,
CASE WHEN EIQ.ei_ilraimtype = '2' THEN '4.5' ELSE EIQ.ei_ilraimtype END DESC,
MEY.e_status ASC,
COALESCE ( EIY.ei_ilrplanlearnhours, 0 ) + COALESCE ( EIY.ei_ilrplaneephours, 0 ) DESC,
MEY.e_start ASC,
MEQ.e_start ASC
GO
And you would call it differently.
--...
CROSS APPLY GC.dbo.iFUN_MainAim_Q ('2015-16',S.s_studentreference) SomeAlias
INNER JOIN dbo.capd_moduleenrolment MEQ_Core with (nolock)
-- This is the function that is taking too long
ON MEQ_Core.e_id=SomeAlias.e_id
AND MEQ_Core.e_student=S.s_id
--...
By the way, unless you are fine with inconsistent information (missing, duplicate or additional rows) try to remove all the NOLOCK hints. There are several problems associated with them and they're not the go-faster magic switch. More info:
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply