asked on

crosstab query takes more than 5 minutes rendering data

Hi:
I have a crosstab query which take over 5 minutes rendering data
Table 1 : @TempTable has 1100 records.
Table 2 : Days table has 30 records which is days numbers from 1 to 30 (Arabic month)
--this is my query:
SELECT FullData.day, a.DailyAttendanceID
,FullData.BSC_NO
,bsc_name = FullData.bsc_name
,a .BSC_UNIT
,a .BSC_SECTION_NO
,a .DailyAttendanceTypeID
, PTDattendanceCodes = isnull (a .PTDattendanceCodes,'-')
,a .DailyAttendanceDescriptions
,a .WhoCreated
,a .AttendanceDate
,a .GetDate
,a .myTime
,a .IsFingerPrintExists
,a . myDay

FROM
(select * from Days
CROSS JOIN
(SELECT DISTINCT [BSC_NO],bsc_name from @TempTable) as Data ) as FullData
LEFT JOIN
@TempTable as a on FullData.day = a.myDay
      AND FullData.[BSC_NO] = a.[BSC_NO]
      order by FullData .BSC_NO , FullData .day

any idea please

my output result is like what U C in here:

Ryan Chong

will this make your query runs faster?

;with FullData as
(
	Select a.BSC_NO, a.bsc_name, b.day from @TempTable a, Days b
)
SELECT FullData.day,  a.DailyAttendanceID
	,a.BSC_NO
	,a.bsc_name
	,a.BSC_UNIT
	,a.BSC_SECTION_NO
	,a.DailyAttendanceTypeID  
	,PTDattendanceCodes = isnull(a.PTDattendanceCodes,'-')
	,a.DailyAttendanceDescriptions
	,a.WhoCreated
	,a.AttendanceDate
	,a.GetDate
	,a.myTime  
	,a.IsFingerPrintExists
	,a.myDay
from FullData
left join @TempTable a on a.myDay = FullData.day AND FullData.BSC_NO = a.BSC_NO
order by FullData.BSC_NO , FullData.day

Open in new window

Mohammad Alsolaiman

ASKER

unfortunately, my query was faster with around 30 seconds. Thank my friend for trying helping me.

Ryan Chong

can you share the execution plan for both yours and my queries?

Mohammad Alsolaiman

ASKER

see attached execution plan Execution-Plan--1.sqlplan Execution-Plan--Expert.sqlplan