July 13, 2011 at 1:46 pm
I am having trouble getting this to return records quickly -
SELECT Count('x') FROM PayeeMaster PM (nolock)
INNER JOIN PayeeLink PL (nolock) ON PM.PayeeID = PL.PayeeID
INNER JOIN PaymentParent PP (nolock) ON PL.UserID = PP.UserID AND PL.PayeeRefNo = PP.PayeeRefNo
INNER JOIN PaymentChild PC (nolock) ON PP.UserID = PC.UserID AND PP.PaymentRefNo = PC.PaymentRefNo
INNER JOIN AccountProfile Acct (nolock) ON PP.UserID = Acct.UserID AND PP.AccountRefNo = Acct.AccountRefNo
INNER JOIN func_charlist_to_table('AM, P, VR', DEFAULT) b ON PC.[PaymentStatus] = b.[str]
July 13, 2011 at 2:10 pm
The biggest concern is using that function on your last JOIN. Can you find another way to accomplish the same thing?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
July 13, 2011 at 2:14 pm
Does it run way faster if you take out the last join?
Why do you use nolock? http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Can you post the actual execution plan?
July 13, 2011 at 2:21 pm
It looks like your using a JOIN for a WHERE clause.
If so, try replacing the JOIN with
WHERE PC.PaymentStatus IN ('AM', 'P', 'VR')
instead of using a JOIN.
By the way, if you can change the column name STR to another name, that would be ideal (since STR is a function name in SQL).
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
July 13, 2011 at 3:05 pm
it does run faster (3 sec) if I take out the last join
I talked to the dev and he says he must use that function -
July 13, 2011 at 3:07 pm
krypto69 (7/13/2011)
it does run faster (3 sec) if I take out the last joinI talked to the dev and he says he must use that function -
Please post the function's code and the actual execution plan of the query with it.
July 13, 2011 at 3:08 pm
Ask the dev to change the function into an iTvF and use the CROSS APPLY approach.
July 13, 2011 at 3:22 pm
lutz - it is a table valued function
here's the function - -- ==========================================================================================
-- Author: Jeremy Truitt (as taken from Erland Sommarskog, SQL Server MVP. Located at:
-- http://www.sommarskog.se/arrays-in-sql-2000.html#iterative
--
-- Create date: 1/30/2008
-- Description:Fast solution for returning a table when given a list of strings for joining to.
--
-- This function provides a much better solution than using dynamic SQL when trying to do
-- something like the following which does NOT work:
--
-- CREATE PROCEDURE get_product_names @ids varchar(50) AS
-- SELECT ProductID, ProductName
-- FROM Northwind..Products
-- WHERE ProductID IN (@ids)
--Which you could then then try to call:
--EXEC get_product_names '9, 12, 27, 37'
--(This would return an error of course.)
--
-- Instead use this function to return a table from your list that you can then join on!
-- Example Usage:
--
-- CREATE PROCEDURE get_company_names_iter @customers nvarchar(2000) AS
-- SELECT C.CustomerID, C.CompanyName
-- FROM Northwind..Customers C
-- JOIN iter_charlist_to_table(@customers, DEFAULT) s ON C.CustomerID = s.nstr
-- go
-- EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'
--
-- Note: The return table has two columns beside listpos, namely
-- str and nstr which both contain the list elements, one is varchar
-- and the other is nvarchar, and you should use the column that matches the
-- column you are joining with. Since Northwind..Customers.CustomerID is nchar(10), nstr
-- is the choice in the example above.
-- Do not use nstr when you join with a varchar column, because this can give very bad performance.
-- ==========================================================================================
ALTER FUNCTION [dbo].[func_charlist_to_table]
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
RETURN
END
July 13, 2011 at 3:24 pm
table valued yes, but not in-line...
I expected a string delimiter function.... 😉
Search this site for DelimitedSplit8K to find a much better performing solution.
July 13, 2011 at 3:30 pm
LutzM (7/13/2011)
table valued yes, but not in-line...I expected a string delimiter function.... 😉
Search this site for DelimitedSplit8K to find a much better performing solution.
http://www.sqlservercentral.com/Forums/Topic1101315-203-6.aspx#bm1129283
July 13, 2011 at 3:41 pm
On the other side: why bother calling a function in the first place instead of hardcoding the values in a subquery?
INNER JOIN
(SELECT 'AM' as [str] UNION ALL
SELECT 'P' UNION ALL
SELECT 'VR') b
ON PC.[PaymentStatus] = b.[str]
July 13, 2011 at 3:58 pm
Or even just
WHERE PC.[PaymentStatus] IN ('AM','P','VR')
July 13, 2011 at 4:00 pm
We all agree on that Lutz but...
krypto69 (7/13/2011)
I talked to the dev and he says he must use that function -
So I'm guessing that this is a dynamic parameter and not static value
July 13, 2011 at 4:17 pm
Ninja's_RGR'us (7/13/2011)
We all agree on that Lutz but...krypto69 (7/13/2011)
I talked to the dev and he says he must use that function -
So I'm guessing that this is a dynamic parameter and not static value
can you please explain why the function needs to be used?
July 13, 2011 at 4:23 pm
I am a bit confused by this query - what is being counted here? I would bet anything that there is no reason to even join to all of the tables to get the count.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply