June 25, 2008 at 4:42 pm
Hi,
I have query that runs dramatically slower on a higher spec'd 64bit machine vs a 32 bit machine (both windows server 2003).
I'm not sure if this is the actual problem but it is the only thing different when testing. The databases are identical! The issue seems to be with the where clause and using "in" with a split function, example below. The example table has 150k rows. If i remove the split function and replace with actual values "(6507,6508)" the query is lightning fast. I though the split function was slow but even if is substitute out with a "select paramaters from MyParamaterTable" it is still extremely slow., see example 2
Does anybody have any ideas as to what it can be. It is so bad i've had to replace the below query with dynamic sql to handle the paramater list! The weird thing is that this query is really fast on our 32 bit poduciton machine.
example 1 - SLOW on 64 bit
declare @ParamaterList varchar(1000)
set @ParamaterList='6507'
select distinct Column1, column2
from MyTable
where
Paramater in (Select convert(nvarchar,Value) from dbo.FN_SplitCommaInClause(@ParamaterList,','))
or
@ParamaterList=''
example 2 - Still SLOW on 64 bit
declare @ParamaterList varchar(1000)
set @ParamaterList='6507'
select distinct Column1, column2
from MyTable
where
Paramater in (select paramaters from MyParamaterTable)
or
@ParamaterList=''
June 25, 2008 at 4:45 pm
Extra info - 32 bit machine is a dual core (3gb ram), 64 bit is a quad core (12gb ram)
June 25, 2008 at 5:02 pm
Can you attach the different query plans, in xml format?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 25, 2008 at 5:14 pm
here are the 32 vs 64 bit plans for the same query
June 25, 2008 at 7:36 pm
thanks. no clues there, unfortunately. I suspect that it has something to do with the function; can you post its listing?
Also, if you select directly from your paramaters table on the 64bit system, how does that perform?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 25, 2008 at 8:09 pm
ALTER FUNCTION [dbo].[FN_SplitCommaInClause]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Value VARCHAR(50))
AS
BEGIN
INSERT INTO @IDTable
SELECT SUBSTRING(@ItemList+@delimiter, N,
CHARINDEX(',', @ItemList+@delimiter, N) - N)
FROM dbo.Tally
WHERE N <= LEN(@ItemList)
AND SUBSTRING(@delimiter + @ItemList,
N, 1) = @delimiter
ORDER BY N
RETURN
END
The performance is still bad (but better) even when i select straight from the paramaters table. NB: the above split is based on the Tally table example from an article on this site.
June 26, 2008 at 5:54 am
It's interesting that you are getting an index scan on the Geni_SAPHR table. Does it have an index on the CostCtr field?
I would not have expected the query to generate a loop join.
When you run it on the 64bit machine, does it use parallelism (sp_who2 show the spid multiple times)? It may be that the loop join on the 32 bit machine is staying with a single thread and scanning the table and the 64 bit machine is multi-threading into oblivion on you. You could try the MAXDOP 1 option on 64 bit machine to see if this is the problem. If it is, I would see if you can add an index to lose the loop join. When the optimizer cannot come up with something good, it often will come up with something really, really bad.
June 26, 2008 at 7:32 am
markl (6/25/2008)
example 1 - SLOW on 64 bitdeclare @ParamaterList varchar(1000)
set @ParamaterList='6507'
select distinct Column1, column2
from MyTable
where
Paramater in (Select convert(nvarchar,Value) from dbo.FN_SplitCommaInClause(@ParamaterList,','))
or
@ParamaterList=''
This query looks like it is part of a larger procedure or batch. Could you post the whole procedure, please?
thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2008 at 7:55 am
Try replacing your function with this one, in the query:
CREATE FUNCTION [dbo].[FN_SplitCommaInClause_N]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1))
Returns TABLE
AS
RETURN
WITH cteTally as (
Select TOP 4000
Row_Number() OVER (Order by object_id) as [N]
From master.sys.All_Columns)--one table should be enough, up to 4000
SELECT TOP 4000
SUBSTRING(@ItemList+@delimiter, N,
CHARINDEX(',', @ItemList+@delimiter, N) - N)
as [Value]
FROM cteTally
WHERE N <= LEN(@ItemList)
AND SUBSTRING(@delimiter + @ItemList,N, 1) = @delimiter
ORDER BY N
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2008 at 2:59 pm
Wow, the split function above made a huge difference. It's really fast now. I've attached the query plan for comparison. I ran sp_who2 and i didn'tget any spid repeating.... Do you have any idea why the 64bit had cuch trouble with the previous split function/query? Thanks for you time on this!
June 26, 2008 at 3:28 pm
The first split function returned both an index and value. What is the best way of incorporating this into the second cte function?
June 26, 2008 at 3:38 pm
OK, this works...
createFUNCTION [dbo].[FN_SplitCommaInClause]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1))
Returns TABLE
AS
RETURN
WITH cteTally as (
Select TOP 4000
Row_Number() OVER (Order by object_id) as [N]
From master.sys.All_Columns) --one table should be enough, up to 4000
SELECT TOP 4000
SUBSTRING(@ItemList+@delimiter, N,
CHARINDEX(',', @ItemList+@delimiter, N) - N)
as [Value], Row_Number() OVER (Order by N) as [Index]
FROM cteTally
WHERE N <= LEN(@ItemList)
AND SUBSTRING(@delimiter + @ItemList,N, 1) = @delimiter
ORDER BY N
June 26, 2008 at 4:16 pm
Sorry, I missed that there were two columns returned...
As for why it is faster, this is an Inline Table-Valued-Function. They are special in that they do not have procedural code so the SQL Optimizer does not have treat them like a black-box. It can treat them more like a View (though not completely, apprently) and less like a Function, which is a good thing.
The catch is, it really should not have made much difference in this case: the function call was pretty trivial anyway (unless: your Tally table was really big and was not indexed or ordered? .. maybe..).
Most likely the optimizer was just make really bad choices for some obscure set of reasons and this change just got it back to where it made better choices again.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2008 at 5:51 pm
Interesting, Thanks for your help on this, much appreciated.
June 26, 2008 at 6:28 pm
Mark... does your Tally table have a clustered PK on it?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply