July 29, 2015 at 3:36 pm
Hi,
I have this script, it is taking long time to execute, what can I do to make it execute faster?
DECLARE @C varchar(10)
SET @C = '%A1%'
select *
from tblo o (nolock)
left join tblb b (nolock) on o.binid = b.binid
left join tblp p (nolock) on p.pid = o.pid
left join tblof o1 (nolock) on o1.pid = p.pid
left join tblJ J (nolock) on j.BID = b.BID
and
j.DT = (SELECT top 1 DT FROM tblJ (nolock) WHERE BID = b.BID order by dt desc)
where o.C like @C
July 29, 2015 at 10:56 pm
PJ_SQL (7/29/2015)
Hi,I have this script, it is taking long time to execute, what can I do to make it execute faster?
DECLARE @C varchar(10)
SET @C = '%A1%'
select *
from tblo o (nolock)
left join tblb b (nolock) on o.binid = b.binid
left join tblp p (nolock) on p.pid = o.pid
left join tblof o1 (nolock) on o1.pid = p.pid
left join tblJ J (nolock) on j.BID = b.BID
and
j.DT = (SELECT top 1 DT FROM tblJ (nolock) WHERE BID = b.BID order by dt desc)
where o.C like @C
Without DDL and sample data it's impossible to give you a good answer.
Some general guidelines for improving query performance that may help you include:
1) model your data so that you don't require so many joins
2) Avoid correlated subqueries with ORDER BY clauses; perhaps there is a better way to get that TOP 1 value
3) unless you really need every column from all tblo, tblb, tblp, tbof and tblJ you may benefit from changing you SELECT * to SELECT <only the columns you need>
4) If you can get away with changing "SET @C = '%A1%'" to "SET @C = 'A1%'" that might help.
Again, these are general suggestions based on what you have posted. Some DDL, sample data, expected output and a query plan could help get you a better answer.
-- Itzik Ben-Gan 2001
July 29, 2015 at 11:25 pm
PJ_SQL (7/29/2015)
Hi,I have this script, it is taking long time to execute, what can I do to make it execute faster?
DECLARE @C varchar(10)
SET @C = '%A1%'
select *
from tblo o (nolock)
left join tblb b (nolock) on o.binid = b.binid
left join tblp p (nolock) on p.pid = o.pid
left join tblof o1 (nolock) on o1.pid = p.pid
left join tblJ J (nolock) on j.BID = b.BID
and
j.DT = (SELECT top 1 DT FROM tblJ (nolock) WHERE BID = b.BID order by dt desc)
where o.C like @C
First inclination is that you need to get rid of all those NOLOCK directives. No it won't necessarily help with speed.
Second, the subquery in the join condition would probably do a lot better to be rewritten.
But to get solid recommendations, we need sample data and sample DDL along with expected results.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2015 at 4:35 am
Do a LIKE search where you have wild cards on the starting side of the predicate
LIKE '%a1'
Is going to cause scans and prevent index use. I would strongly advise attacking that part of the query. There may be other issues with the structures and indexes, but that right there is going to hit performance pretty hard.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2015 at 10:22 am
1) Asking for all columns from every table.
2) We need additional info to really help. Table create scripts (with indexes), row counts, query plan.
3) Leading percent on the like.
a) has to scan almost every character of every field
b) cannot get accurate estimate of what is coming out of filter --> very inefficient plans at least some of the time
4) A minor correction to what Grant said: the leading percent won't prevent index use. It will just prevent index SEEKs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 30, 2015 at 12:27 pm
TheSQLGuru (7/30/2015)
4) A minor correction to what Grant said: the leading percent won't prevent index use. It will just prevent index SEEKs.
Very true and worth noting.
A pretty hefty percentage of the time though, if you're just scanning the index, might as well not have it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2015 at 1:34 pm
What are you expecting to accomplish with the subquery? It basically compares one column to itself joined by the same condition.
July 30, 2015 at 5:07 pm
Luis Cazares (7/30/2015)
What are you expecting to accomplish with the subquery? It basically compares one column to itself joined by the same condition.
Looks like an expensive attempt to get the most resent row from tblJ for each BID... The correlated sub-query version of the following...
;WITH J AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY BID ORDER BY dt DESC) AS RN
FROM tblJ
)
SELECT
*
FROM
tblo o
LEFT JOIN tblb b ON o.binid = b.binid
LEFT JOIN tblp p ON p.pid = o.pid
LEFT JOIN tblof o1 ON o1.pid = p.pid
LEFT JOIN J ON b.BID = J.BID AND J.RN = 1
WHERE
o.C LIKE @C
July 31, 2015 at 7:23 am
I get this error :
Invalid column name 'RN'.
July 31, 2015 at 8:07 am
PJ_SQL (7/31/2015)
I get this error :Invalid column name 'RN'.
Have a look at the following... I included two different solutions... Either approach could be faster than the other depending on the indexes you have on your tables...
Both produce the same results.
-- Create a little test data...
IF OBJECT_ID('tempdb..#People') IS NOT NULL
DROP TABLE #People;
IF OBJECT_ID('tempdb..#Transactions') IS NOT NULL
DROP TABLE #Transactions;
CREATE TABLE #People (
PersonID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
PersonFullName VARCHAR(20)
);
CREATE TABLE #Transactions (
TransactionID INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
PersonID INT NOT NULL,
TransactionDate DATE,
TransactionAmount MONEY
);
-- The following index on the #Transactions table will offer substantial performance benefits to both queries...
-- Test the solutions with and without the index to see the differences...
CREATE NONCLUSTERED INDEX ix_Transactions_PersonID_TransactionDate ON #Transactions (
PersonID, TransactionDate DESC)
INCLUDE (
TransactionAmount)
INSERT #People (PersonFullName) VALUES
('Bob Jones'),('John Q Public'),('Will Smith'),('Huck Finn');
INSERT #Transactions (PersonID,TransactionDate,TransactionAmount) VALUES
(1,'2015-01-01',555.55),(1,'2015-02-01',444.55),(1,'2015-03-01',555.55),
(2,'2015-05-01',333.55),(2,'2015-06-01',444.55),(2,'2015-07-01',888.55),
(3,'2015-08-01',222.55),(3,'2015-09-01',333.55),(3,'2015-10-01',555.55);
-- Get a quick look at the test data before looking at the solution query...
SELECT * FROM #People p;
SELECT * FROM #Transactions t;
-- Solution 1 (using a CTE with the ROW_NUMBER function)
WITH GetLatestTran AS (
SELECT
t.TransactionID,
t.PersonID,
t.TransactionDate,
t.TransactionAmount,
ROW_NUMBER() OVER (PARTITION BY t.PersonID ORDER BY t.TransactionDate DESC) AS RN
FROM #Transactions t
)
SELECT
p.PersonID,
p.PersonFullName,
glt.TransactionDate,
glt.TransactionAmount
FROM
#People p
LEFT JOIN GetLatestTran glt
ON p.PersonID = glt.PersonID
AND glt.RN = 1;
-- Solution 2 (using OUTER APPLY & TOP 1 clause)
SELECT
p.PersonID,
p.PersonFullName,
t.TransactionDate,
t.TransactionAmount
FROM
#People p
OUTER APPLY (
SELECT TOP 1
t.TransactionDate,
t.TransactionAmount
FROM
#Transactions t
WHERE
p.PersonID = t.PersonID
ORDER BY
t.TransactionDate DESC
) t;
HTH,
Jason
July 31, 2015 at 11:19 am
PJ_SQL (7/31/2015)
I get this error :Invalid column name 'RN'.
Did you use ALL of Jason's query, including the part that has WITH at the beginning? That part is called a CTE, or Common Table Expression. It's a sort of an inline view, if you will, and behaves a lot like a view, in that a CTE can be inserted into, selected from, deleted from, or updated, as long as the CTE follows the same rules that a view would need to follow to allow such operations to occur. Post the exact query you tried, in it's entirety, when you received this error. Did you perhaps have only the SELECT portion highlighted in SSMS at the time you got the error?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply