April 23, 2002 at 1:14 pm
Hi, I needto translate an oracle sql statement to sql server. In my oracle sql, I have HASH hint. If I don't use the Hash hint, it would be very very slow.
Here is my sql in oracle:
SELECT /*+ HASH(machine_connect_speed), HASH(machine_ram), HASH(machine_platform), HASH(machine_location)*/ a.user_id, a.mach_id, loc.descp lc_descp, plat.descp plat_descp,
rm.descp rm_descp, decode (a.network_ind, '1', 'Yes', '2', 'No') net_ind, spd.descp spd_descp, a.mach_platform_other
FROM user_machine a, machine_connect_speed spd, machine_ram rm,
machine_platform plat, machine_location loc
where ... /* some out join where condition */
Can anyone give me some help on how to
use hash hint or anything equivalent in sql server?
Thanks in advance.
Abby Zhang
April 23, 2002 at 2:48 pm
Hints usually are not needed in the cost based method of querying data in SQL. You basic syntax I really could work without the where as I have no idea how to relate the data and just kinda went with the flow on that. If you can post your where clause I can give you a better idea. I did convert the decode statement for you.
Ex.
SELECT
a.user_id,
a.mach_id,
loc.descp lc_descp,
plat.descp plat_descp,
rm.descp rm_descp,
CASE a.network_ind
WHEN '1' THEN 'Yes'
WHEN '2' THEN 'No'
END net_ind,
spd.descp spd_descp,
a.mach_platform_other
FROM
user_machine a
(INNER,LEFT,RIGHT) JOIN
machine_connect_speed spd
ON
spd.col = a.col
(INNER,LEFT,RIGHT) JOIN
machine_ram rm
ON
rm.col = a.col2
(INNER,LEFT,RIGHT) JOIN
machine_platform plat
ON
plat.col = a.col3
(INNER,LEFT,RIGHT) JOIN
machine_location loc
ON
loc.col = a.col4
where ... /* some where condition joins where is handled in the on clauses*/
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 23, 2002 at 3:41 pm
Hi, Thanks for translate the decode part. But what I am really looking for is the hash hint part. It is true that without the hash part, the sql would still work. BUT, without the hash hint part, the sql will work extremely slow. Because machine_connect_speed, machine_ram, machine_platform, machine_location are mapping tables, their size is small, for each table, there are only around 10-20 rows. So in oracle, when a small table is used for the mapping purpose againt a much bigger table, Hash hint is used to performance purpose.
I am looking for something equivalence in SQL SERVER.
The whole sql is like this:
SELECT /*+ HASH(machine_connect_speed), HASH(machine_ram), HASH(machine_platform), HASH(machine_location)*/ a.user_id, a.mach_id, loc.descp lc_descp, plat.descp plat_descp,
rm.descp rm_descp, decode (a.network_ind, '1', 'Yes', '2', 'No') net_ind, spd.descp spd_descp, a.mach_platform_other
FROM user_machine a, machine_connect_speed spd, machine_ram rm,
machine_platform plat, machine_location loc
WHERE a.delete_ind = 'N'
AND loc.delete_ind(+) = 'N'
AND plat.delete_ind(+) = 'N'
AND rm.delete_ind(+) = 'N'
AND spd.delete_ind(+) = 'N'
AND a.mach_conn_speed_code = spd.mach_conn_speed_code(+)
AND a.mach_ram_code = rm.mach_ram_code(+)
AND a.mach_platform_code = plat.mach_platform_code(+)
AND a.mach_location_code = loc.mach_location_code(+)
Thanks.
Abby
April 23, 2002 at 8:02 pm
Try this in Query Analyzer with Show Execution Plan turned on.
SELECT
a.user_id,
a.mach_id,
loc.descp lc_descp,
plat.descp plat_descp,
rm.descp rm_descp,
CASE a.network_ind
WHEN '1' THEN 'Yes'
WHEN '2' THEN 'No'
END net_ind,
spd.descp spd_descp,
a.mach_platform_other
FROM
user_machine a
LEFT JOIN
machine_connect_speed spd
ON
spd.mach_conn_speed_code = a.mach_conn_speed_code AND
spd.delete_ind = 'N'
LEFT JOIN
machine_ram rm
ON
rm.mach_ram_code = a.mach_ram_code AND
rm..delete_ind = 'N'
LEFT JOIN
machine_platform plat
ON
plat.mach_platform_code = a.mach_platform_code AND
plat..delete_ind = 'N'
LEFT JOIN
machine_location loc
ON
loc.mach_location_code = a.mach_location_code AND
loc.delete_ind = 'N'
WHERE
a.delete_ind = 'N'
Just a few notes. If this is a major query and run often or other queries also use the same fields more than any other then to get maximum bennefit create a clustered index on all foreign keys (spd.mach_conn_speed_code, rm.mach_ram_code, plat.mach_platform_code and loc.mach_location_code) and a non-clustered index on a.delete_ind unless the field has only a handfull of options (you have N so I am assuming N or Y most likely and if so consider changing to a 1/0 bit field instead). As for the hints you really should not need to do any kind of hinting for this but if the Execution Plan does not do as expected then do a
SET SHOWPLAN_TEXT ON
GO
TheQurey
GO
SET SHOWPLAN_TEXT OFF
GO
and post the resulting execution plan so I can understand what it did. Finally you stated that the child tables were small (20 or so records) so you may want to set the fill factor of indexes to 100 on those tables, if larger and you have changes often to their data then try 80-90% for maximum speed and reset the fill factor as often as needed (this varies). Let me know what happens.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 4:08 am
Here i is with the HASH hint for the join.
SELECT
a.user_id,
a.mach_id,
loc.descp lc_descp,
plat.descp plat_descp,
rm.descp rm_descp,
CASE a.network_ind
WHEN '1' THEN 'Yes'
WHEN '2' THEN 'No'
END net_ind,
spd.descp spd_descp,
a.mach_platform_other
FROM
user_machine a
LEFT HASH JOIN
machine_connect_speed spd
ON
spd.mach_conn_speed_code = a.mach_conn_speed_code AND
spd.delete_ind = 'N'
LEFT HASH JOIN
machine_ram rm
ON
rm.mach_ram_code = a.mach_ram_code AND
rm..delete_ind = 'N'
LEFT HASH JOIN
machine_platform plat
ON
plat.mach_platform_code = a.mach_platform_code AND
plat..delete_ind = 'N'
LEFT HASH JOIN
machine_location loc
ON
loc.mach_location_code = a.mach_location_code AND
loc.delete_ind = 'N'
WHERE
a.delete_ind = 'N'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 6:06 pm
Hi, Thanks for your response. I will try it out on either Friday or Sunday, because I am on another short assignment today and tomorrow. I can't have an execution plan yet because we so far don't have any data.
Will update you on Friday or Sunday. Thanks again for your help.
Abby
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply