April 9, 2009 at 5:58 pm
Hi
I have recently upgraded a sql server 2000 db to a sql server 2005 db.
I updated the statistics and re-indexed after the upgrade aswell.
I kept the db on a sql server 2000 instance on the same server for testing purposes.
When I run a simple select query on the 2005 instance and I run the same query on the 2000 instance I get a remarkable performance difference between the two.
I then imported the data from the two tables used in the query into an existing 2005 db and ran the query and still I have the same remarkably slow performance.
So this made me realise that the problem was not with the upgrade but with sql server 2005 itself.
Here is the query:
select * from Table1 where
REPLACE(LTRIM(REPLACE(BIBNAC, ISNULL('0', '0'), ' ')), ' ', ISNULL('0', '0'))
not in
(
SELECT
REPLACE(LTRIM(REPLACE(ACCOUNTNUM, ISNULL('0', '0'), ' ')), ' ', ISNULL('0', '0'))
FROM
Table2
)
Table1 has 900 records
Table2 has 149000 records
When I run this query on the sql server 2000 instance it runs for about 3 seconds.
When I run this query on the sql server 2005 instance it runs forever(haven't left it to finish running, takes too long)
Is there anything I am missing?
Any help would be appreciated.
Regards
Rowan
April 9, 2009 at 6:31 pm
did you run DBCC UPDATEUSAGE as well?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2009 at 6:53 pm
Yes i did. dbcc updateusage('dbname') ?
April 9, 2009 at 7:17 pm
there was a similar thread on the issue, and even though the person had updated usage, updated statistics, the issue did not clear until they DROPPED their current statistics, then created stats, and then dbcc freeproccache(spelling?)
only after that was done did their upgrade perform normally;
hope that idea helps
Lowell
April 9, 2009 at 8:39 pm
April 10, 2009 at 3:42 am
Ok thank you, I will try that.
But just a quick question. I created a sql 2005 db as a test, and imported the two tables I use into this test db and the query still ran for a long long time.
Seeing that this was not run on a upgraded db but rather a newly created 2005 db, would the problem still lie with the statistics?
Regards
April 10, 2009 at 3:44 am
have you tried profiling the database to see if there are any oddities there?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2009 at 3:48 am
No I haven't.
How do I do that with sql 2005?
April 10, 2009 at 3:49 am
using either profiler or a server side trace!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2009 at 4:21 am
ok i'm guessing your query got changed to make it less complex for the web...
but there seems to be a some steps tha seem weird to me.
if you yank out the constants of Isnull('0','0')
yo basically get this:
REPLACE(LTRIM(REPLACE(BIBNAC'0', ' ')), ' ','0')
that's replacing the '0' with with spaces, trimming it, then replacing any spaces with back with '0'
are you just trying to remove preceding zeros?
Lowell
April 10, 2009 at 4:22 am
Rowan (4/10/2009)
Ok thank you, I will try that.But just a quick question. I created a sql 2005 db as a test, and imported the two tables I use into this test db and the query still ran for a long long time.
Look at the statistics,execution plans and indexes
would the problem still lie with the statistics?
May be, but since you have just imported the table i don't there would be a problem with bad stats.
April 10, 2009 at 4:35 am
Hi. Yes i'm trying to strip away the preceeding zero's. With my orginal query i had a user defined function doing this, but when the query was so slow i thought the udf was the problem so i tried doing it without using the udf. If run the query without removing the zero's it runs in like 3 seconds.
I see you point with the isnull.
But why does it run perfectly in sql 2000 with or without using a udf. I am stumped!
Thanks for your replies.
April 10, 2009 at 5:47 am
Keep in mind when you use udf functions in "where" clause, it is change the where sargable to NONsargable and sql server wiil not use indexes if they exists. (this happen with isnull and too ..i dont see why use isnull in your case)..
I solve a problem like this resolving the UDF to a #table and after that join whit this table :..somenthig like
select a.c1,a.c2,b.c1 from table1 a, table2 b where
df_dosomething(a.c1) = b.c1
and a.c2 = @parameter
I change to
create table #final(c1 int, c2 int)
insert into #final (c1,c2)
select c1,dbo.udf_dosomething(c1)
from table1
where c2 = @parameter
create index index_1 on #final(c1)
select a.c1,a.c2,b.c1 from
#final a, table2 b
where a.c1 = b.c1
you can Create a computed column with the final value and work with it.
alter table table1 add NewColumn as (REPLACE(LTRIM(REPLACE(BIBNAC, ISNULL('0', '0'), ' ')), ' ', ISNULL('0', '0')))
But i dont like to say somenthing without analize the exection plan.
April 10, 2009 at 6:15 am
Yeah I will get rid of the 'isnull'
I looked at the execution plan in both sql 2000 and sql 2005 and they are the same, and this does not help with the fact that the execution times are so different between 2000 and 2005.
April 10, 2009 at 11:36 am
Rowan (4/10/2009)
But why does it run perfectly in sql 2000 with or without using a udf. I am stumped!Thanks for your replies.
sql 2000 let you do a whole load of stuff that is now removed/tightened down in sql 2005. Take "order by" in a view!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply