February 21, 2019 at 10:21 am
This query (I've simulated it to show you. Otherwise, it is longer with many more lists of columns, some more tables joined; and finally to sort by every column as required by the user) takes too long, and can timeout from the UI sometimes. I was trying to optimize the stored procedure to reduce loading time without affecting the retrieved lists. I was considering to select * from the temporary table and then Order from there. That could help somehow. I also considered using a dynamic sql without specifying the @sortidr (DESC/ASC), but the user should have the flexibility to order either way.
I was wondering if anyone can suggest better ways of optimizing it.
CREATE PROCEDURE get_lists
@employeeid int
@ColumnName varchar(100) = NULL,
@sortidr varchar(4) = NULL,
@start_date datetime = NULL
AS
CREATE #tempemp
(emp_id int, first_name varchar(20), last_name varchar(20), SSN int)
INSERT INTO #tempemp
SELECT team_action, rec_id, total_records, cfirst_name, efirst_name, has_desc, category_name
FROM
(SELECT COALESCE(lt.description, lt.action) AS team_action
,cl.rec_id
,total_records = count(*) Over()
,c.cfirst_name
,e.efirst_name
, has_desc = cld.changelog_id IS NOT NULL THEN 1 ELSE 0 END
, ct.category_name
FROM claimll cll
JOIN itemtype lt ON cll.clLog_id = lt.clLog_id
LEFT JOIN categgory ct ON ct.id=clc.cat_id
LEFT JOIN clients c ON ct.client_id = c.client_id
LEFT JOIN employees e ON cll.emp_id = e.emp_id
LEFT JOIN detail d ON d.change_id = cll.change_id
WHERE cll.change_date > @start_date
AND cll.emp_id = @employeeid OR cll.by_employeeid = @employeeid
) t
ORDER BY
CASE WHEN @sortidr = 'asc' AND @ColumnName='team_action' THEN team_action END,
CASE WHEN @sortidr = 'desc' AND @ColumnName = 'team_action' THEN team_action END DESC,
CASE WHEN @sortidr = 'asc' AND @ColumnName='rec_id' THEN rec_id END,
CASE WHEN @sortidr = 'desc' AND @ColumnName = 'rec_id' THEN rec_id END DESC,
CASE WHEN @sortidr = 'asc' AND @ColumnName='total_records' THEN total_records END,
CASE WHEN @sortidr = 'desc' AND @ColumnName = 'total_records' THEN total_records END DESC,
CASE WHEN @sortidr = 'asc' AND @ColumnName='cfirst_name' THEN cfirst_name END,
CASE WHEN @sortidr = 'desc' AND @ColumnName = 'cfirst_name' THEN cfirst_name END DESC,
CASE WHEN @ColumnName IS NULL THEN first_name END DESC
February 21, 2019 at 10:44 am
Are you sure it's the sort that's taking the time? Have you tried it without the sort and if so is it much faster?
February 21, 2019 at 10:47 am
Don't sort the query, sort the result table instead, which is more useful anyway. Even if sort the query, you will still have to sort again when reading from the temp table. Personally I'd allow an entire sort string to be passed in, for example "team_action ASC, rec_id DESC". Since you're doing it dynamically anyway, the sort order can be as complex as they'd like.
If you specify the exact order sequence when reading from the table, no actual sort will be required, since the clus index is already in that order.
CREATE PROCEDURE get_lists
@employeeid int
@ColumnName varchar(100) = NULL,
@sortidr varchar(4) = NULL,
@start_date datetime = NULL
AS
SET NOCOUNT ON;
DECLARE @sql varchar(8000)
CREATE #tempemp
(emp_id int, first_name varchar(20), last_name varchar(20), SSN int)
SET @sql = 'CREATE CLUSTERED INDEX tempemp__CL ON #tempemp ( ' +
ISNULL(@ColumnName, 'first_name') + ' ' + ISNULL(@sortidr, 'ASC') +
' ) WITH ( FILLFACTOR = 99 )';
EXEC(@sql)
INSERT INTO #tempemp
SELECT team_action, rec_id, total_records, cfirst_name, efirst_name, has_desc, category_name
FROM
(SELECT COALESCE(lt.description, lt.action) AS team_action
,cl.rec_id
,total_records = count(*) Over()
,c.cfirst_name
,e.efirst_name
, has_desc = cld.changelog_id IS NOT NULL THEN 1 ELSE 0 END
, ct.category_name
FROM claimll cll
JOIN itemtype lt ON cll.clLog_id = lt.clLog_id
LEFT JOIN categgory ct ON ct.id=clc.cat_id
LEFT JOIN clients c ON ct.client_id = c.client_id
LEFT JOIN employees e ON cll.emp_id = e.emp_id
LEFT JOIN detail d ON d.change_id = cll.change_id
WHERE cll.change_date > @start_date
AND cll.emp_id = @employeeid OR cll.by_employeeid = @employeeid
) t
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2019 at 12:07 pm
Jonathan AC Roberts - Thursday, February 21, 2019 10:44 AMAre you sure it's the sort that's taking the time? Have you tried it without the sort and if so is it much faster?
Yes. It is better without the order by. I think, it is because it has to sort dynamically by the choice of the user.
February 21, 2019 at 12:08 pm
ScottPletcher - Thursday, February 21, 2019 10:47 AMDon't sort the query, sort the result table instead, which is more useful anyway. Even if sort the query, you will still have to sort again when reading from the temp table. Personally I'd allow an entire sort string to be passed in, for example "team_action ASC, rec_id DESC". Since you're doing it dynamically anyway, the sort order can be as complex as they'd like.If you specify the exact order sequence when reading from the table, no actual sort will be required, since the clus index is already in that order.
CREATE PROCEDURE get_lists
@employeeid int
@ColumnName varchar(100) = NULL,
@sortidr varchar(4) = NULL,
@start_date datetime = NULLAS
SET NOCOUNT ON;DECLARE @sql varchar(8000)
CREATE #tempemp
(emp_id int, first_name varchar(20), last_name varchar(20), SSN int)SET @sql = 'CREATE CLUSTERED INDEX tempemp__CL ON #tempemp ( ' +
ISNULL(@ColumnName, 'team_action') + ' ' + ISNULL(@sortidr, 'ASC') +
' ) WITH ( FILLFACTOR = 99 )';
EXEC(@sql)INSERT INTO #tempemp
SELECT team_action, rec_id, total_records, cfirst_name, efirst_name, has_desc, category_name
FROM
(SELECT COALESCE(lt.description, lt.action) AS team_action
,cl.rec_id
,total_records = count(*) Over()
,c.cfirst_name
,e.efirst_name
, has_desc = cld.changelog_id IS NOT NULL THEN 1 ELSE 0 END
, ct.category_name
FROM claimll cll
JOIN itemtype lt ON cll.clLog_id = lt.clLog_id
LEFT JOIN categgory ct ON ct.id=clc.cat_id
LEFT JOIN clients c ON ct.client_id = c.client_id
LEFT JOIN employees e ON cll.emp_id = e.emp_id
LEFT JOIN detail d ON d.change_id = cll.change_id
WHERE cll.change_date > @start_date
AND cll.emp_id = @employeeid OR cll.by_employeeid = @employeeid
) t
Ok, thank you. I will try to apply your suggestion and see.
February 22, 2019 at 11:43 am
I am just curious...
In the presented query the WHERE Clause looks a little beet strange.
Was it intentionally done that there is the OR operator and no parenthesis.
WHERE cll.change_date > @start_date
AND cll.emp_id = @employeeid OR cll.by_employeeid = @employeeid
in my opinion you will get a lot more records then you need and will cause your performance to degrade.
logically thinking I would risk to say that most likely you wanted to say something like this:
WHERE cll.change_date > @start_date
AND (cll.emp_id = @employeeid OR cll.by_employeeid = @employeeid )
maybe I am wrong 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply