April 14, 2009 at 4:27 am
Hi
I have a simple select query that runs forever in sql 2005
Here is the query
select * from Table1 where
REPLACE(LTRIM(REPLACE(BIBNAC, '0', ' ')), ' ', '0')
not in
(
SELECT
REPLACE(LTRIM(REPLACE(ACCOUNTNUM, '0', ' ')), ' ', '0')
FROM
Table2
)
I strip away the zero's in front of the number and then do the comparison.
When I run this part of the query
SELECT
REPLACE(LTRIM(REPLACE(ACCOUNTNUM, '0', ' ')), ' ', '0')
FROM
Table2
it completes in like 2 seconds, but when I run the whole query it runs forever
April 14, 2009 at 5:02 am
Will you have to do this query as part of your normal business processes or is it a one-off?
If the former, you could create a computed column in each of the tables that is both persisted and indexed, which stores the string with the leading zeros stripped off.
If the latter, you could do something similar with indexed temporary columns to hold the string with the leading zeros stripped off.
April 14, 2009 at 5:09 am
Thanks.
Its part of normal business processes.
I'm just a bit concerned because the same type of query over the same amount of data on a sql 2000 server takes a matter of a few seconds to complete.
Why does it take so long in 2005?
April 14, 2009 at 5:19 am
Rowan try this method instead; I know a left join is my preference, but I think tat because you are doing 4 operations on the column BIBNAC, and 4 operations on the column ACCOUNTNUM, and then the table scan to see what is not IN is big cost for the SQL.
See if either of these run any faster:
SELECT * FROM TABLE1 WHERE
CONVERT(INT,BIBNAC)
NOT IN
(
SELECT
CONVERT(INT,ACCOUNTNUM) WHERE ISNUMERIC(ACCOUNTNUM) = 1
FROM
Table2
)
WHERE ISNUMERIC(BIBNAC) = 1
--OR
SELECT Table1.*
FROM Table1
LEFT OUTER JOIN Table2
ON CONVERT(INT,BIBNAC) = CONVERT(INT,ACCOUNTNUM)
WHERE ISNUMERIC(ACCOUNTNUM) = 1
AND ISNUMERIC(BIBNAC) = 1
AND TABLE2.PRIMARYKEYCOLUMN IS NULL
Lowell
April 14, 2009 at 6:10 am
Thanks Lowell
I tried your first query because it is similar to my current query and it ran very fast.
SELECT * FROM TABLE1 WHERE
CONVERT(INT,BIBNAC)
NOT IN
(
SELECT
CONVERT(INT,ACCOUNTNUM) WHERE ISNUMERIC(ACCOUNTNUM) = 1
FROM
Table2
)
WHERE ISNUMERIC(BIBNAC) = 1
Now is this because sql 2005 have removed some features thats 2000 had, because I'm still stumped as to why my original query runs fine in 2000 but not in 2005
April 14, 2009 at 7:36 am
i'd need the real data to test, anything I say from here out is speculation.
ok we know the server is trying to compare BIBAC to ACCOUNTNUM;
we know that the server uses a cost based execution plan...if it takes to long to calculate the "perfect" execution plan, it skips to one that it thinks would probably work.
in a straight query, BIBAC NOT IN(..) is quick,
I'm thinking that the more functions you wrap around the column names, the execution plan gets farther and farther from reality...and that the actual execution plan for SQL2000 is different than the one for 2005. when the execution plan is not correct, you get long running queries...you'll see the same issue if you search "parameter sniffing", where a query is quick, but the stored proc using the "same" query is hella slow.
Using a different method to strip out preceeding zeros (convert in this case) might make it choose a smarter execution plan.
Lowell
April 14, 2009 at 7:46 am
If you want to get some idea as to why it works differently between 2000 & 2005, get the actual execution plan and see what SQL Server is doing with each query. That will tell you more.
By the way, after upgrading to 2005, did you rebuild all the statistics? If not, that could explain why you're getting poor execution plans.
"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
April 14, 2009 at 8:35 am
Ok I think I see what is happening.
So sql 2000 and sql 2005 might have different excecution plans for the same query?
Using a udf to remove the zero's also results in a slow forever running query.
I take it that this is a sql 2005 thing?
April 14, 2009 at 8:40 am
Grant's advice is important: bad statistics = bad execution plan.
update your statistics on your SQL2005, and see if your old query performs better.
if your UDF is doing the same code your original statement was doing, all you did was move your code...it's still the same code, so it would still perform the same way.
Lowell
April 14, 2009 at 8:52 am
Yeah i've updated statistics and reindexed.
The udf is different code.
April 14, 2009 at 8:56 am
Is it a table valued UDF or inline scalar? If it's table valued, that might the core issue righ there. And no, that's not unique to 2005.
Even if it's inline, it might be a problem. Again, I'd go back to the execution plans.
"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
April 14, 2009 at 9:58 am
Ok so what you are saying is that execution plans for the same query may be different in sql 2000 & 2005, and thus the reason for the different execution times.
April 14, 2009 at 10:05 am
As mentioned the UDF's are different, which means you comparing apples and oranges...
Could you post the two UDF's and then we can let you know how to improve them, or why one is working slower than the other.
Sounds like the code is what is changing the speeds and not the actual difference in SQL Server
I'm with what Grant said regarding the inline and non-line functions...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 14, 2009 at 11:31 am
It's not the same query if you've changed the code.
However, yes, with the same code, same data, same statistics distribution (you won't get the same statistics) you could get different execution plans between 2000 and 2005.
Also, is there a change to the amount of data being retreived or stored that could affect the statistics or their use?
"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
April 14, 2009 at 4:11 pm
Ok wait I think we are takling past each other here
Here is a step for step list of things I did from scratch
-- I restored a sql 2000 db into a sql 2005 instance on the same server
-- I kept the 2000 instance running for testing purposes
-- After I restored the db into 2005 I ran sp_updatestats and reindexed.
-- I then ran the following query on both the 2000 and the 2005 instance
select * from Table1 where
REPLACE(LTRIM(REPLACE(BIBNAC, '0', ' ')), ' ','0')
not in
(
SELECT
REPLACE(LTRIM(REPLACE(BIBNAC, '0', ' ')), ' ','0')
FROM
Table2
)
-- On the 2000 instance it runs for 3 seconds and on the 2005 instance it runs forever.
-- I then imported the data of the 2 tables used in the query into another existing 2005 db on
-- another machine but had the same result with the query running forever
-- I then used the following code
SELECT * FROM Table1 WHERE
ISNUMERIC(BIBNAC) = 1 and
CONVERT(numeric,BIBNAC)
NOT IN
(
SELECT
CONVERT(numeric,ACCOUNTNUM)
FROM
Table2
WHERE ISNUMERIC(ACCOUNTNUM) = 1
)
and ISNUMERIC(BIBNAC) = 1
-- And this query runs very quickly on sql 2005
-- I then created a udf so that I could reuse the code because I need to strip away zero's quite often
-- Here is the udf script
CREATE FUNCTION [dbo].[Rem_Zero] (@Polno nvarchar(50))
RETURNS varchar(30)
AS
BEGIN
DECLARE @NewPolno nvarchar(50)
if isnumeric(@Polno) = 1
begin
declare @number bigint
set @number = convert(bigint,@Polno)
set @NewPolno = convert(nvarchar(50),@number)
end
else
begin
set @NewPolNo = REPLACE(LTRIM(REPLACE(@Polno, '0', ' ')), ' ', '0')
end
RETURN(@NewPolno)
END
-- I then changed the query to the following
select * from Table1 where
dbo.rem_zero(BIBNAC)
NOT IN
(
SELECT
dbo.rem_zero(ACCOUNTNUM)
FROM
Table2 --where isnumeric(accountnum) = 1
)--and isnumeric(bibnac) = 1
-- But the query also ran forever in 2005
-- But then by fluke I discovered that the following query runs fine in 2005 by adding another
--condition in the where clause
select * from Table1 where
dbo.rem_zero(BIBNAC)
NOT IN
(
SELECT
dbo.rem_zero(ACCOUNTNUM)
FROM
Table2 where isnumeric(accountnum) = 1
) and isnumeric(bibnac) = 1
By adding another condition to the query will surely slow it down but in this case it changes the query from running forever to running in like 4 seconds
I need to preferably use a udf because I would need to strip away zero's often in my queries
This is where I currently am.
Why would I need to add the condition and how can I put this into my udf?
Apologies for the lengthy post but this is exactly what I have done up till now but I'm still stumped as to why the original query runs fine in sql 2000
Thanks
Viewing 15 posts - 1 through 15 (of 68 total)
You must be logged in to reply to this topic. Login to reply