Блог

Я решаю проблему времени, затрачиваемого SQL-запросом на выполнение. Я провел тщательный поиск, чтобы уменьшить время выполнения. Я имею дело с миллионами записей (очевидно!).

Мой запрос получает количество лекций из внутренней таблицы посещаемости, к которой присоединяются 4 другие таблицы. Проблема в том, что выполнение занимает 1-2 минуты. И этот запрос используется в цикле, количество которых является динамическим (<10). И время ожидания соединения истекает. так что это не сработает.

Мой запрос :

 CREATE procedure [dbo].[Sp_TotalLecture_DateWise]
    (@CollegeId int = null,
     @AcademicYrId int = null,
     @Degreeid int = null,
     @StreamId int = null,
     @Semester nvarchar(50) = null,
     @SectionId int = null,
     @Course_Code nvarchar(100) = null,
     @frmdate nvarchar(50) = null,
     @ToDate nvarchar(50) = null)
as 
begin
    begin try
        Select 
            Count(at.StAttnd_Id) as TotalLecture 
        into 
            #t 
        from 
            ERP_AttendanceDetail as at 
        inner join 
            ERP_StudentAcadmicDetail as sad on at.Academic_Id = sad.Academic_Id 
        inner join 
            ERP_SectionAssign as Sa on sad.Section_ID = sa.Section_ID  
        inner join 
            ERP_StdntDetail as sd On sad.Stdnt_Id = sd.Stdnt_Id 
        inner join 
            ERP_Course as cd on sad.Course_Id = cd.Course_Id 
        Where 
            at.College_Id = @CollegeId 
            and SAD.AcademicYr_Id = @AcademicYrId
            and sa.Degree_Id = @Degreeid 
            and sa.Stream_Id = @StreamId 
            and sad.Semester = @Semester 
            and sad.Section_ID = @SectionId
            and cd.Course_Code = @Course_Code
            and at.Attnd_Date between convert(date, @frmdate, 106) 
                                  and convert(date, @ToDate, 106)    // If i remove search through date criteria, it will work fine and gives answer in 2-3 seconds.
        group by 
            sd.Stdnt_RegNo, sd.Stdnt_Name, sad.Stdnt_Id

    select max(TotalLecture) as TotalLecture 
    from #t

    drop table #t
end try
begin catch
end catch
end
  

То, что я уже сделал, это:

1. Используемая процедура, временная таблица (как вы можете видеть)

2. Устанавливает первичный ключ для всех этих таблиц, упомянутых с кластеризованным индексом (как обычно)

3. Создан другой индекс в поле первичного ключа с некластеризованной индексацией

И самая забавная часть в этом коде заключается в том, что если я удалю критерий поиска по дате, он работает идеально и выдает ответ через 2-3 секунды (но я не могу это удалить).

И если для этого нет подходящего решения. Пожалуйста, скажите мне, как поддерживать соединение в течение длительного времени, потому что оно теряется между ними и данные не отображаются. Я сохранил тайм-аут команды для этой конкретной процедуры со страницы .cs равным 2000000000.

Спасибо.

Попробуйте приведенный ниже запрос, надеюсь, это сработает

 Select sd.Stdnt_RegNo, sd.Stdnt_Name, sad.Stdnt_Id, at.StAttnd_Id Into #t 
from ERP_AttendanceDetail as at 
inner join ERP_StudentAcadmicDetail as sad  on  at.Acadmic_Id=sad.Acadmic_Id 
inner join ERP_SectionAssign as Sa on sad.Section_ID=sa.Section_ID 
inner join ERP_StdntDetail as sd On sad.Stdnt_Id=sd.Stdnt_Id 
inner join ERP_Course as cd on sad.Course_Id=cd.Course_Id 
Where at.College_Id=@CollegeId 
and SAD.AcadmicYr_Id=@AcademicYrId
and sa.Degree_Id=@Degreeid 
and  sa.Stream_Id=@StreamId 
and  sad.Semester=@Semester 
and sad.Section_ID=@SectionId
and cd.Course_Code=@Course_Code
and at.Attnd_Date between convert(date,@frmdate,106) and convert(date,@ToDate,106)


Select Count(StAttnd_Id) StAttnd_Id into #t1 from #t
Group by Stdnt_RegNo, Stdnt_Name, Stdnt_Id


Select MAX(StAttnd_Id) StAttnd_Id from #t1
  

Производительность зависит от структуры всех таблиц и объема данных. Без поиска идеального решения немного туманно.

попробуйте другой метод, заполните 2 или 3 обязательных поля каждой таблицы в таблицах #temp1, #temp2 ….#temp5. Создайте индекс для них, которые также используются в поле join create index on date, и убедитесь, что convert date конвертирует (date,@frmdate,106) во время заполнения #temp, а затем напишите запросы join.

не используйте convert(date,@frmdate,106) во время фильтрации, поскольку вы уже преобразовали date в #temp

Фильтр будет выглядеть следующим образом….

  and (at.Attnd >=@frmdate and at.Attnd<=@ToDate)
  

и после этого сохраните результат запроса объединения в #t

 Select Count(StAttnd_Id) StAttnd_Id into #t1 from #t
Group by Stdnt_RegNo, Stdnt_Name, Stdnt_Id


Select MAX(StAttnd_Id) StAttnd_Id from #t1
  

Еще одна вещь …. попробуйте заполнить #temp1, #temp2….. #Temp5 перед запуском цикла, если возможно