January 18, 2006 at 12:45 am
We made the step to using SQL2005 instead of sql200 because the locking mechanism in sql2005 is far better than sql2000.
First we tried the beta version, which worked fine but some queries lasted much longer than it used to in sql2000.
We first thought this would be fixed when the production version was released but no, still same problem.
It looks like the query engine in sql2005 is more restrictive than before.
Anyone has experience with this ?
January 18, 2006 at 4:18 am
can you post examples of a query where this happens?
MVDBA
January 18, 2006 at 5:23 am
I know the second query is faster because of the "exists" statmenent but in sql200 the first query reurned results as fast as the second one.
I wonder how many surprises there are in sql2005.
This one is very slow
SELECT * FROM vwItemsListByModelSeries Items
WHERE LocaleCode = '001'
AND ((MileStoneCode IN ('M4','M5','M6','M7.5'))
AND (ModelSeriesID='457'))
AND Items.ItemNumber NOT IN (SELECT ItemNumber FROM Items WHERE RealmCode = 2 AND ItemNumber IS not NULL and itemnumber = Items.ItemNumber)
AND Items.ID NOT IN (SELECT CorporateID FROM Items WHERE RealmCode = 2 AND CorporateID IS not NULL and corporateid = items.id)
ORDER BY ItemNumber;
This one returns results in few milliseconds
SELECT * FROM vwItemsListByModelSeries Items
WHERE LocaleCode = '001' AND ((MileStoneCode IN ('M4','M5','M6','M7.5'))
AND (ModelSeriesID='457'))
AND not exists (SELECT 0 FROM Items fr WHERE fr.RealmCode = 2 and (fr.itemnumber = items.itemnumber or fr.corporateid = items.id))
ORDER BY ItemNumber;
Following is also a strange change in sql2005
select convert(nvarchar(10),max(isnull(convert(decimal(18,0),a.itemnumber)+1,b.startvalue)))
from numberingschemes b
left join itemnumbers a on (b.startvalue <= convert(decimal(18,0),a.itemnumber) and b.endvalue >= convert(decimal(18,0),a.itemnumber) and itemnumber not like '%[^0-9]%')
where b.code = 'EXTI'
Numberingschemes only contains numeric values, but in itemnumbers there are alfanumeric values.
The query works in sql2005 but not in sql2005 (with the same data).
In Sql2005 the result is an error : cannot convert nvarchar to numeric
I understand why it can go wrong, i do not understand why the query engine works differntly.
This makes converting databases from sql2000 to sql2005 much harder.
January 18, 2006 at 5:46 am
not sure about the performance drop, but i might be able help you on the convert problem
the issue is convert(decimal(18,0),a.itemnumber - your where clause filters out alphas, but it doesn't deal withempty strings (unless i've just completely lost the plot and misread the query - not enough coffee this morning)
try the following
select convert(decimal(18,0),'')
which will give you a type conversion failure
select convert(decimal(18,0),NULL)
which will give you a NULL RETURN
maybe (i think) in addition to the "not like '%[^0-9]%'" clause you should have (AND DATALENGTH(itemnumber)>0)
MVDBA
January 18, 2006 at 5:56 am
In the tables there are no empty strings like ''
If this would be the case it also would fail in sql2000
not like '%[^0-9]%' is used because i only want numeric itemnumbers returned (isnumeric is not sufficient because of an ancient FORTRAN notation which allows D and E).
The only explanation why it fails in SQL2005 is that the query engine builts the temporary tables in a different order and applies the restrictions later.(to late in my example)
January 19, 2006 at 2:22 am
quite possibly - i know that msaccess has several similar issues - but i can't beleive they'd make the same mistakes on SQL2005
i think you'd better wait for service pack 1
MVDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply