slow query on 64bit sql server?
-
my company get a chance to run a test on demo system itanium2 with sql2k 64bit installed.
i ran some queries. FOr update & delete, it run faster.
But in select queries, there are some queries that run extremely slow, about 3x-10x, even 50x slower.
i look carefully at the queries, and after i modified the filter
"where not b.fibcno is null" with
"where isnull(b.fibcno,'0')<>'0'"
note:b.fibcno is the other table joined within the queries
it ran at the same speed, even a little bit faster as the 32bit.
My question is, what is it all about? is wrong using "is null" instead of replacing the NULL values first, and then compared later, just like the query above?
i tried to installed the SP4, but nothing happens.
the queries involved about 10tables.
thx,
Edo
-
To get subjective answer, you would really need to post a heap of DDL & DML (table and index definitions, query syntax, etc...) and provide more detail about your datatypes and volume of data being queried.
--------------------
Colt 45 - the original point and click interface
-
oh .. and also the hardware specs including data file locations.
--------------------
Colt 45 - the original point and click interface -
Itanium 1.2Ghz + 1GB RAM vs Dual Xeon 2.8Ghz HT + 2GB RAM
i have shortened the query so hopefully easier to understand
select * from A
left join
(
select * from B
inner join (select keyB,max(period) as maxPeriod from C where period<=200506 group by keyB) MAXB
on B.key=MAXB.keyB AND B.period=MAXB.maxPeriod
 
B on B.keyB=A.keyB
left join F on f.keyF=b.keyF
left join G on G.keyG=b.keyG
left join H on H.keyH=a.keyH
left join I on i.keyI=a.keyI
left join (select keyK,sum(depnum) as total from K where left(cast(period as nvarchar(6)),4)<='2004' group by keyK) K on K.keyK=b.keyB
left join (select keyL,sum(depnum) as total from L where left(cast(period as nvarchar(6)),4)='2005' group by keyL) L on L.keyL=b.keyL
where b.keyB is not null
when i use the above query, the query run extremelyy slow compared to the xeon system (same database and table/copied).
but when i change the last part (isnull) with the
isnull(b.keyB,'0')<>'0'
it can ran in the same speed, even a little bit faster.
for extremely result, before i change the query,on the xeon system it need less than 1minute, but in the 64bit system needs more than 20minutes!!!!
i surely have no idea what happened, but i already tested 2times.
for note,it only happened to this kind of query, the other query i tested run well at the reasonable speed(faster than xeon)
i use no index.
table A contains 16000rows
table B,K,L --> same table, contains about 1million rows
the other table is not very big <100 rows.
-
So, you are saying that a query run on a box with 1.6 Ghz of power runs slower than a query run on a box with 5,6 Ghz of power. That seems reasonable to me.
Also, with only 1GB memory on the slow box compared to 2GB on the fast box you will get further loss of performance.
Your benchmark is proving 2 things: a) the functionality works, and b) your work will run slower on a slow box. If you want to compare 32 and 64 bit performance, you need to have a similar spec box for both tests.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
We migrated to a clustered Itanium 2 configuration last year and whilst we did see some improvement in performance across the board the main reason for doing it was the ability to utilise significantly more memory, we've moved from being able to run 15 incredibly intensive queries (on the 32-bit server) up to around 50 before seeing similar performance peaks on our itaniums.
The moral of this story is that whilst Itaniums are good they are not the answer to everything.
PS. you also need to take caching, load and a huge number of other things into account when testing performance between 2 systems, it could be that the data was already in the cache on the 32 bit server and not on the itaniums, it's just not that easy to say without going through the process from start to finish.
-
It looks like the SQL statement optimizer is different in 64 bit vs 32 bit. Perhaps a call to Bill Gates will result in a fix? I think it's 1-800-call-bill!
-
Beside the fact that the ItaniumII processor is internally faster as the X86 compatible Xeons, you can realize the real power of the 64 bit servers when you use (much?) over 4 GB RAM for your database server.
So for a database server of 1 GB RAM I wouldn't use any 64 bit proc server. It is just a waste of money IMHO.
Bye
Gabor
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply