dynamic WHERE clause, Performance issue
-
I have lots of reports in which there are many fields that are used in dynamic WHERE clause with dynamic sort (ORDER BY field with asc or desc). Following are fields defined in user interface to put required criteria:
1- FirstName
2- LastName
3- FromDade
4- ToDate
5- Department
6- Subject
7- Topic
8- Location
9- StudentID (i.e. Loc+Dept+FirstName+LastName)= 25 characters
To identify every student from where location he/she belonging.
FirstName / LastName
May be ‘deborah’ or ‘de’ so all starting with firstName (LIKE or =) ‘deborah’ or ‘de%’ may appear in WHERE clause. If there is no input it should not use FirstName in WHERE clause. Same with Last name. it might be combination of both FirstName and Last Name for example Deborah M%. all names should match with user criteria.
FromDate / ToDate
Same like above criteria, usually we can mostly use FromDate and toDate and calculate from User Interface to force its expected to receive these two parameters at-least. But if not given, like above example, User want to see all Trainings session for those whose only first Name is Deborah. That’s it. So it may search without FromDate and ToDate.
Location --> Department --> Subject -- > Topic
It is possible for user to input criteria with or without hierarchy like given above. User can select only Location and or Department and or subject and or Topic.
These all fields are defined in user interface to search anything (combination of any fields).
There are more than 6 tables require JOIN to get summarized Training LOG information.
I already have millions of trainings sessions (history of each training attended by students) in my Training Log history. And there are above 85 thousands of students in STUDENT Table.
I have recently joined this organization and I have to fix the performance issue on priority bases because these reports sometimes does not return results and went down – need to reopen browser. Few reports estimate they comes in 20 minutes time approximately.
The organization is joining with another one and they expect Students that require training would be 100 of thousands of new Students joining this database.
Following were my first suggestions to fix the problem.
1- Put indexing in where clause
2- Extend server (processor and RAM) that will not be approved till we all migrate our web application on .NET 2.0- that aleast takes 6 to 9 months.
3- Check stored procedures for better performance gain. Right - the application is using stored procedures for all reportings.
4- And all of you guru’s suggestions from this forum.
I would like to know how others guys face such scenario and gain performance in results with given resources only.
If there is any question, let me know.
-
The problem is likely not the Dynamic SQL... nor is it likely the fact that you are joining 6 tables... but it's real hard to figure out what it could be without you posting the offending code and the DDL that creates the related tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff for your reply and taking interest in my problem.
Well, I won’t give you exact scenario according to existing database/nature of business. But I can give you an example to work around as follow:
List of Tables involved.
Students
- StudentID varchar (as I mentioned above how it is generatred)
- FirstName varchar
- LastName varchar
Locations
- LocationID tinyint
- LocationName varchar
Subnets
- SubnetID int
- Subnet varchar
- LocationID tinyint
Departments
- DepartmentID int
- DepartmentName varchar
- SubnetID tinyint
Subjects
- SubjectID int
- SubjectName varchar
- DepartmentID int
Topics
- TopicID int
- TopicName varchar
- SubjectID int
TrackerLog
- TrakcerLogID int (Training Session ID)
- SessionStart DateTime
- SessionEnd DateTime
- StudentID int
- TopicID int
- IP varchar
- And many otherFields Like user completed this session or NOT
Schedules
- ScheduleID int
- TrainingStart DateTime
- TrainingEnd DateTime
We have usually schedules, TrackingLog and User machine IP that is checked in Subnet Table to see from which location user belongs and saved in TrackerLog and the main source of information from where we get scores for students. This training is based on sessionStart and SessionEnd and the students score is updated per Topic.
I hope, now you will get a better idea about the scenario and requirements. These reports are basically used to evaluate students grade according to their participation per schedule. or how much a Topics was browsed mostly. Most wanted topics, locations, subjects, Locations, departments. at what time mostly users hit TrackerLog during 24 hrs. and so many other queries.
I just want a simple solution how to face such condition if someone has done any related task may share his/her experience, I need some hints that which technique should we use in this situation.
Shamshad Ali.
-
Could you post one of the offending queries please, as well as the existing indexes on the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Shamshad,
You forgot to include the offending code like I asked...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
In short I'm sending you a sp similar (tables and fileds are changed) like below:
ALTER PROCEDURE [dbo].[SP_caller_Usage]
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=2, @SortType ='desc', @GroupLevel=1 -- (95358) in 1:16/18 (Administration)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=7, @SortType ='desc', @GroupLevel=2 -- (95887) in 1:24/22 (Region)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=7, @SortType ='desc', @GroupLevel=3 -- (97062) in 1:32/28 (Facility)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=4 -- (95271) in 1:10/15 (Department)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=5 -- (3) in 0:49 (Administration)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=6 -- (34) in 1:22 (Region)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=7 -- (216) in 1:42 (Facility)
-- SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=8 -- (1) in 0:37 (Department)
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 2, @SortType='desc', @GroupLevel =1
-- (95389) in 3:40 (Administration) - done
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 7, @SortType='desc', @GroupLevel =2
-- (95918) in 4:00 (Region) - done
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 7, @SortType='desc', @GroupLevel =3
-- (97093) in 4:25 (Facility) - done
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 5, @SortType='desc', @GroupLevel =4
-- (95303) in 3:02 (Department) - done
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 5, @SortType='desc', @GroupLevel =5
-- (3) in 0:43 (Administration) - done
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 5, @SortType='desc', @GroupLevel =6
-- (34) in 1:15 (Region) - done
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 5, @SortType='desc', @GroupLevel =7
-- (216) in 1:33 (Facility)
-- SP_Caller_Usage @ToDate='12-Jul-2007', @FromDate=NULL, @Last_Name='', @First_Name ='', @Login_ID ='', @Administration_id =0, @Region_ID =0, @Facility_Id =0, @sort = 5, @SortType='desc', @GroupLevel =8
-- (1) in 0:22 (Department) - done
@ToDate datetime ,
@FromDate datetime ,
@Last_Name varchar(100)=NULL,
@First_Name varchar(100)=NULL,
@Login_ID varchar(100)=NULL,
@Administration_id smallint=0,
@Region_ID smallint=0,
@Facility_Id smallint=0,
@sort smallint=6,
@SortType varchar(10)='DESC',
@GroupLevel smallint = 1
AS
BEGIN
SET NOCOUNT ON
set @First_Name = case @First_Name when '' then Null else @First_Name end
set @Last_Name = case @Last_Name when '' then Null else @Last_Name end
SET @Login_ID = case @Login_ID when '' then Null else @Login_ID end
set @Administration_id = case @Administration_id when 0 then Null else @Administration_id end
set @Region_ID = case @Region_ID when 0 then Null else @Region_ID end
set @Facility_Id = case @Facility_Id when 0 then Null else @Facility_Id end
---------------------------------------- Group by Administration WHEN @GroupLevel = 1
IF @GroupLevel = 1
BEGIN
SELECT m.Login_ID,
m.Administration,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
AND cv.First_Name is not null
AND cv.First_Name LIKE COALESCE(@First_Name + '%', cv.First_Name)
AND cv.Last_Name LIKE COALESCE('%' + Replace(@Last_Name,' ','') + '%', cv.Last_Name)
GROUP BY
m.Login_ID,
m.Administration,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
-- order by Lib_Visits desc
-- order by Channel_Visits desc
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 2 THEN Administration
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END
DESC,
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 2 THEN Administration
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Region WHEN @GroupLevel = 2
IF @GroupLevel = 2
BEGIN
SELECT m.Login_ID,
m.Administration,
m.Region,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
AND cv.First_Name is not null
AND cv.First_Name LIKE COALESCE(@First_Name + '%', cv.First_Name)
AND cv.Last_Name LIKE COALESCE('%' + Replace(@Last_Name,' ','') + '%', cv.Last_Name)
GROUP BY
m.Login_ID,
m.Administration,
m.Region,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 2 THEN Administration
WHEN 3 THEN Region
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END
DESC,
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 2 THEN Administration
WHEN 3 THEN Region
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Facility WHEN @GroupLevel = 3
IF @GroupLevel = 3
BEGIN
SET NOCOUNT ON
SELECT m.Login_ID,
m.Administration,
m.Region,
m.Facility,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
AND cv.First_Name is not null
AND cv.First_Name LIKE COALESCE(@First_Name + '%', cv.First_Name)
AND cv.Last_Name LIKE COALESCE('%' + Replace(@Last_Name,' ','') + '%', cv.Last_Name)
GROUP BY
m.Login_ID,
m.Administration,
m.Region,
m.Facility,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 2 THEN Administration
WHEN 3 THEN Region
WHEN 4 THEN Facility
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END
DESC,
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 2 THEN Administration
WHEN 3 THEN Region
WHEN 4 THEN Facility
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Department WHEN @GroupLevel = 4
IF @GroupLevel = 4
BEGIN
SELECT m.Login_ID,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
AND cv.First_Name is not null
AND cv.First_Name LIKE COALESCE(@First_Name + '%', cv.First_Name)
AND cv.Last_Name LIKE COALESCE('%' + Replace(@Last_Name,' ','') + '%', cv.Last_Name)
GROUP BY
m.Login_ID,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END
DESC,
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 1 THEN Login_Id
WHEN 9 THEN Login_ID
WHEN 10 THEN Department
WHEN 11 THEN Guest_ID
END
END,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Administration Without UserInfo WHEN @GroupLevel = 5
IF @GroupLevel = 5
BEGIN
SELECT m.Administration_ID,
Administration,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
GROUP BY
m.Administration_ID,
Administration
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Region Without UserInfo WHEN @GroupLevel = 6
IF @GroupLevel = 6
BEGIN
SELECT m.Administration_ID,
Administration,
m.Region_ID,
m.Region,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
GROUP BY
m.Administration_ID,
Administration,
m.Region_ID,
m.Region
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Facility Without UserInfo WHEN @GroupLevel = 7
IF @GroupLevel = 7
BEGIN
SELECT m.Administration_ID,
Administration,
m.Region_ID,
m.Region,
m.Facility_ID,
m.Facility,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
GROUP BY
m.Administration_ID,
Administration,
m.Region_ID,
m.Region,
m.Facility_ID,
m.Facility
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
---------------------------------------- Group by Department Without UserInfo WHEN @GroupLevel = 8
IF @GroupLevel = 8
BEGIN
SELECT
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Login_Name LIKE COALESCE(@First_Name + '%', Login_Name)-- 105101 in 55 Sec.
AND m.Login_Name LIKE COALESCE('%' + ' ' + @Last_Name + '%', Login_Name)
AND m.Login_ID LIKE COALESCE(@Login_ID + '%', Login_ID)
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)
AND m.Region_ID BETWEEN COALESCE(@Region_ID, Region_ID) AND COALESCE(@Region_ID, Region_ID)
AND m.Facility_ID BETWEEN COALESCE(@Facility_ID, m.Facility_ID) AND COALESCE(@Facility_ID, m.Facility_ID)
AND m.Start_DateTime BETWEEN COALESCE(@FromDate, m.Start_DateTime) AND COALESCE(@ToDate, m.Start_DateTime)
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0
ORDER BY
CASE lower(@SortType)
WHEN 'desc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
DESC,
CASE lower(@SortType)
WHEN 'asc' THEN
CASE @sort
WHEN 5 THEN sum(isnull(c.Channel_Visits,0))
WHEN 6 THEN sum(isnull(l.Lib_Visits,0))
WHEN 7 THEN sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0))
WHEN 8 THEN sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0))
END
END
END
------------------------------------------ END OF PROCEDURE
SET NOCOUNT OFF
END
-
Wow.
OK, I haven't read through all of that, maybe over lunch, but one thing caught my eye
IF
@GroupLevel = 1
BEGIN
-- Do something
END
IF @GroupLevel = 2
BEGIN
-- Do something
END
A stored procedure structured like that, with multiple possible execution branches can end up with execution plans that are optimal for one of the branches and not for the others. A stored proc has a single plan and the first time it compiles, all of the possible execution paths are compiled and optimised, based on the parameters passed for the first execution.
I usually recomend that people split collossal procs like that into smaller procs that can be called from the master proc. Something like this
IF @GroupLevel = 1
BEGIN
EXEC SubProc_1 @params ....
END
IF @GroupLevel = 2
BEGIN
EXEC SubProc_2 @params ....
END
That way each of the sub procs gets its own plan. You can also run the sub procs seperatly to see which is the slow one
p.s. Existing indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Few of procs were already written as you mentioned above like calling sub procedues but it does not produce any desired performance that we say YES, here is the performance gain:
IF @GroupLevel = 1
BEGIN
EXEC SubProc_1 @params ....
END
IF @GroupLevel = 2
BEGIN
EXEC SubProc_2 @params ....
END
The slow one is the one which is retrieving more columns and has more where clauses. You can see the output results and time it takes from following (remarks in proc above).
If I call following proc with parameters it produces 95358 rows in 1:16 mins (1 minute and 16 seconds.
SP_Caller_Usage @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=2, @SortType ='desc', @GroupLevel=1 -- (95358) in 1:16/18 (Administration)
Indexes are usually defined on Primary and Foreing keys for joins. Thats it.
If you think there is any better solution to write where clause and can optimize further then how?
based on all my queries If there is any lake of knowledge that I require let me guide aswell.
Shamshad Ali.
-
Is it possible to pre-process the results of the grouped subqueries into separate tables?
Doing aggregate functions on derived tables (themselves having aggregate functions) is implicitly going to be very costly, and the performance usually will *suck*. I'm talknig about these:
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
I don't know how often these stats change, how often they're accessed, or if your users could live with "near-real-time" data, but if you could process those sub-queries into "real tables" with actual live indexes - performance should go up. At least somewhat, since you have a HAVING predicate, and a CASE statement in the order by (I don't think you can avoid them): each of these tend to make this run slower, but having them ALL in there at the same time is going to make this thing churn.
The nice part about the pre-processing, especially if your users can deal with recent data, you can do that in an offline state (meaning, every x hours, instead of when they want to see the report). It will make their report "pop" a lot faster.
If you have disk space to burn/spare - look at doing as much pre-processing as you can. Something like - put the results of your group by , but excluding the HAVING and the ORDER by statements to a table, then run a SELECT * from <myworking table> where <the logic for the HAVING statement> ORDER BY <your ORDER BY statement>.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
We can't go on pre-processing (I can't forward this idea to upper management), also there is no specific time for pulling out report. We don't know when and from which location these reports are requested so we can't fix a time for pre-processing. Also the changes in tracking tables (you marked above) are inserted upon site access 24X7.
Shamshad Ali.
-
Have you considered indexed views to replace your derived tables? It will result in a slow down of inserts/updates but that trade off may be acceptable considering your reporting needs.
Is session_id indexed in your *_visits tables?
Is there an index on tbl_cv_user.Firstname?
Is there an index on tbl_Tracking_mstr.Start_DateTime.
If not, I would start by adding those, see if it makes any difference. Compare costs as given in the execution plan, rather than time as it's less prone to fluctuations due to server load.
Have a look at the execution plan for the query and identify where the high costs are. Will likely be in scans (table or clustered index), hash aggregates or sorts
Given the branching nature of your proc and the form of the where clauses, there is no way this proc can possibly have a consistent plan. I would recomend, if you can't split it out, mark the procedure to recompile on every execution. At least that way there's no chance of getting a wildly inappropraite plan from the plan cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I understand not being able to pre-process outside of this procedure, but have you considered just using actual tables that get rebuilt with this data (at the tope of the proc before the 7 IF statements)? meaning - dump the intermediate summaries to actual tables and use the tables in the final report? If nothing else - you might be able to move some of the work out of TempDB, or take advantage of some indexing. You can sometimes achieve substantial gains that way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
You're simply trying to do too much at once in each section... you must "Divide and Conquer" if you expect to get any performance out of this.
First thing I notice is that the WHERE clauses are all the same... and none of them are capable of using an Index SEEK... the best you can get out of this is an Index SCAN which can be worse than a Table Scan if BookMarks are used.
Since the WHERE clause is what limits the data and the tables involved are the smallest of the lot, start with a simple query to populate a TempTable with the data from all columns used in all SELECTs from the tbl_Tracking_mstr and tbl_cv_user tables based on the criteria in the WHERE clause. BE SURE TO INCLUDE THE SESSIONID, as well. Notice that you must NOT do any aggragation of data for this first step... it'll just slow things down... you're just trying to get a smaller list of things to aggragate when it's time. Notice that this temp table will serve as the basis for ALL the reports...
Next, do your aggragates and populate the temp table with them. These aggragates should be done as updates and in pairs based on the tables that the counts are coming from. You'll be surprised at how fast these run because, guess what?... They'll be able to use indexes on the larger tables and the temp table will be really small!!!!
Last, but not least, do a SELECT/Aggragate on the comparitively small temp table to display the data according to the inputs for sort order and the like. You should only have one of these.
Overall, this will also make maintenance of the code much easier because there will only be 3 main sections to the code instead of all the ones you currently have.
Also, go back and review the criteria in the WHERE clause for logic errors... here's a dandy one I found...
AND m.Administration_ID BETWEEN COALESCE(@Administration_id, Administration_ID) AND COALESCE(@Administration_id, Administration_ID)... ya wanna tell me why the heck you need a BETWEEN on that???
Think about it!--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For the time, after tunning database tables using proper indexing, if there are lots of WHERE and they are dynamic (depends on user's selection) I use dynamic query to solve the problem, those results that were taking 1:30 sec, now comes only in 20 sec.
I'm using block of code which depends in paramters and may vary, some block of code is always static or CASE, so I'm concatinating strings to retrieve final query and it really produces nice results in the sense of performance.
As for Jeff pointed me above BETWEEN clause with same range was unknow to me and i have fixed that too. Thanks.
I'm still not sure that this is a final solutions or still I'm going wrong. I would like comments from all who really faced such situation and produced nice solution in production.
/*****************************************************/
set @SQLString = @SQLString + @WhereString + @GroupByString + @OrderByString
exec sp_executesql @SQLString
/*****************************************************/
Alter proc SP_Visitor_Usage_Dept_3
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @login_ID='VHAAUGSTONER', @FromDate = '01-Jan-2000', @sort=2, @SortType ='desc' -- (95358) in 1:16/18 (Administration)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=2, @SortType ='desc' -- (95358) in 1:16/18 (Administration)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @First_Name ='deborah', @FromDate = '01-Jan-2000', @sort=2, @SortType ='desc' -- (95358) in 1:16/18 (Administration)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @GroupLevel=1, @First_Name ='deborah', @Last_Name ='mar', @FromDate = '01-Jan-2000', @sort=10, @SortType ='desc' -- (95358) in 1:16/18 (Administration)
-- SP_Visitor_Usage @ToDate = '12-July-2007', @FromDate = '11-22-2005', @sort=11, @SortType ='desc', @GroupLevel=4 -- (95358) in 1:16/18 (Administration)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=2, @SortType ='desc', @GroupLevel=1 -- (95358) in 1:16/18 (Administration)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=7, @SortType ='desc', @GroupLevel=2 -- (95887) in 1:24/22 (Region)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=7, @SortType ='desc', @GroupLevel=3 -- (97062) in 1:32/28 (Facility)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=4 -- (95271) in 1:10/15 (Department)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=5 -- (3) in 0:49 (Administration)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=6 -- (34) in 1:22 (Region)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=7 -- (216) in 1:42 (Facility)
-- SP_Visitor_Usage_Dept_3 @ToDate = '12-July-2007', @FromDate = '01-Jan-2000', @sort=5, @SortType ='desc', @GroupLevel=8 -- (1) in 0:37 (Department)
@ToDate datetime ,
@FromDate datetime ,
@Last_Name varchar(100)=NULL,
@First_Name varchar(100)=NULL,
@Login_ID varchar(100)=NULL,
@Administration_id smallint=0,
@Region_ID smallint=0,
@Facility_Id smallint=0,
@sort smallint=6,
@SortType varchar(10)='DESC',
@GroupLevel smallint=4
AS
BEGIN
-- SET NOCOUNT ON
set @First_Name = case @First_Name when '' then Null else @First_Name end
set @Last_Name = case @Last_Name when '' then Null else @Last_Name end
SET @Login_ID = case @Login_ID when '' then Null else @Login_ID end
set @Administration_id = case @Administration_id when 0 then Null else @Administration_id end
set @Region_ID = case @Region_ID when 0 then Null else @Region_ID end
set @Facility_Id = case @Facility_Id when 0 then Null else @Facility_Id end
Declare @SQLString varchar(4000), @GroupByString varchar(1000), @WhereString varchar(2000), @OrderByString varchar(1000)
Declare @FinalQueryToRun nvarchar(4000)
----------------------------- group level = 1
IF @GroupLevel = 1
BEGIN
set @SQLString =
'
SELECT m.Login_ID,
m.Administration,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' + ''' ''' + ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Login_ID,
m.Administration,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 2
IF @GroupLevel = 2
BEGIN
set @SQLString =
' SELECT m.Login_ID,
m.Administration,
m.Region,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' + ''' ''' + ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Login_ID,
m.Administration,
m.Region,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 3
IF @GroupLevel = 3
BEGIN
set @SQLString =
' SELECT m.Login_ID,
m.Administration,
m.Region,
m.Facility,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' + ''' ''' + ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Login_ID,
m.Administration,
m.Region,
m.Facility,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 4
IF @GroupLevel = 4
BEGIN
set @SQLString =
'
SELECT m.Login_ID,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed,
cv.First_name + ' + ''' ''' + ' + cv.Last_name as Login_Name,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest,
m.Login_ID as [User_ID],
m.Login_Type,
m.Guest_ID AS Guest_Exist
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
LEFT OUTER JOIN tbl_cv_user cv ON m.Login_ID = cv.cv_user_name
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Login_ID,
m.Login_Type,
m.Guest_ID,
cv.First_name,
cv.Last_name,
cv.Department,
cv.Guest
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 5
IF @GroupLevel = 5
BEGIN
set @SQLString =
'SELECT m.Administration_ID,
Administration,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Administration_ID,
Administration
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 6
IF @GroupLevel = 6
BEGIN
set @SQLString =
' SELECT m.Administration_ID,
Administration,
m.Region_ID,
m.Region,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Administration_ID,
Administration,
m.Region_ID,
m.Region
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 7
IF @GroupLevel = 7
BEGIN
set @SQLString =
' SELECT m.Administration_ID,
Administration,
m.Region_ID,
m.Region,
m.Facility_ID,
m.Facility,
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' GROUP BY
m.Administration_ID,
Administration,
m.Region_ID,
m.Region,
m.Facility_ID,
m.Facility
having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
----------------------------- group level = 8
IF @GroupLevel = 8
BEGIN
set @SQLString =
' SELECT
sum(isnull(l.Lib_Visits,0)) as Lib_Visits,
sum(isnull(l.Lib_Completed,0)) as Lib_Completed,
sum(isnull(c.Channel_Visits,0)) as Channel_Visits,
sum(isnull(c.Ch_Prog_Completed,0)) as Ch_Prog_Completed,
sum(isnull(l.Lib_Visits,0)) + sum(isnull(c.Channel_Visits,0)) as tot_Visits,
sum(isnull(l.Lib_Completed,0)) + sum(isnull(c.Ch_Prog_Completed,0)) as tot_Completed
FROM tbl_Tracking_mstr m
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Lib_Visits,
SUM(completed) AS Lib_Completed FROM tbl_Tracking_Library_Content group by session_ID
) l ON l.Session_Id = m.Session_Id
left JOIN
(
SELECT session_id, COUNT(Session_Id) AS Channel_Visits,
SUM(completed) AS Ch_Prog_Completed FROM tbl_Tracking_Channel_Program group by session_ID
) c ON c.Session_Id = m.Session_Id
where
m.Start_DateTime BETWEEN ''' + convert(varchar, @FromDate, 107) + ''' AND ''' + convert(varchar, @ToDate, 107) + '''
'
set @GroupByString =
' having
sum(isnull(l.Lib_Visits,0)) >0 or
sum(isnull(l.Lib_Completed,0)) >0 or
sum(isnull(c.Channel_Visits,0)) >0 or
sum(isnull(c.Ch_Prog_Completed,0)) >0 '
END
------------------------------------- where case
set @WhereString = ' '
if @First_Name <> '' or @First_Name is NOT NULL
Begin
set @First_Name = @First_Name + '%'
set @WhereString = @WhereString + ' AND m.Login_Name LIKE ''' + @First_Name + ''''
end
if @Last_Name <> '' or @Last_Name is NOT NULL
Begin
set @Last_Name = '% ' + @Last_Name + '%'
set @WhereString = @WhereString + ' AND m.Login_Name LIKE ''' + @Last_Name + ''''
end
if @Login_ID <> '' or @Login_ID is NOT NULL
Begin
set @Login_ID = @Login_ID + '%'
set @WhereString = @WhereString + ' AND m.Login_ID LIKE ''' + @Login_ID + ''''
end
if @Administration_id <> '' or @Administration_id is NOT NULL
set @WhereString = @WhereString + ' AND m.Administration_ID = ' + convert(varchar, @Administration_id) + ''
if @Region_ID <> '' or @Region_ID is NOT NULL
set @WhereString = @WhereString + ' AND m.Region_ID = ' + convert(varchar, @Region_ID ) + ''
if @Facility_ID <> '' or @Facility_ID is NOT NULL
set @WhereString = @WhereString + ' AND m.Facility_ID = ' + convert(varchar, @Facility_ID) + ''
-------------------------------- Order by
set @OrderByString = 'ORDER BY '
IF @sort = 1
set @OrderByString = @OrderByString + 'Login_Id'
else if @sort = 2
set @OrderByString = @OrderByString + 'Administration'
else if @sort = 3
set @OrderByString = @OrderByString + 'Region'
else if @sort = 4
set @OrderByString = @OrderByString + 'Facility'
else if @sort = 9
set @OrderByString = @OrderByString + 'Login_ID'
else if @sort = 10
set @OrderByString = @OrderByString + 'Department'
else if @sort = 11
set @OrderByString = @OrderByString + 'Guest_ID'
else if @sort = 5
set @OrderByString = @OrderByString + 'sum(isnull(c.Channel_Visits,0))'
else if @sort = 6
set @OrderByString = @OrderByString + 'sum(isnull(l.Lib_Visits,0)) '
else if @sort = 7
set @OrderByString = @OrderByString + 'sum(isnull(l.Lib_Visits,0))+ sum(isnull(c.Channel_Visits,0)) '
else if @sort = 8
set @OrderByString = @OrderByString + 'sum(isnull(l.Lib_Completed,0))+ sum(isnull(c.Ch_Prog_Completed,0)) '
if @SortType = 'DESC'
set @OrderByString = @OrderByString + ' DESC'
else
set @OrderByString = @OrderByString + ' ASC'
set @SQLString = @SQLString + @WhereString + @GroupByString + @OrderByString
-- print @SQLString
-- set @FinalQueryToRun = @SQLString
-- print @FinalQueryToRun
exec sp_executesql @SQLString
END
/*****************************************************/
Shamshad Ali
-
It was mentioned that you can't pre-calc the data. Actually have you considered using temp tables for this. Most of the subqueries were group by's That would be perfect to insert into a temp table with a PK on the group by fields. (session id). The cost is the temp table, but I am sure you are using temp anyways. Advantage is that you will now be joining to an indexed table (the inline view probably is not).
I have recently had to do something very similar. 400M rows of data spread across 3 tables with an 8 table join to get the end result. I basically do the 5 table part first and write the results to a temp table. That is then joined to the 3 big tables to get me my final result. Worst case still takes 20min to run, but most are more like the 20 second variety.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply