Fastest SQL .... !!! Pls Help

[Fori]

Hi All,

i have 2 queries and i would like your opinion and suggestoins on the best query:

Query 1:


SELECT

inv_ProdCode,
inv_SDesc,

(case when (SELECT sum(loc_Freestock) FROM invlocationDB WHERE loc_invID = inv_ID) is null then 0 else (SELECT sum(loc_Freestock) FROM invlocationDB WHERE loc_invID = inv_ID) end) as FreeStock,
(case when (SELECT sum(loc_Allocated) FROM invlocationDB WHERE loc_invID = inv_ID) is null then 0 else (SELECT sum(loc_Allocated) FROM invlocationDB WHERE loc_invID = inv_ID) end)as Allocation,
(case when (SELECT sum(loc_OnOrder) FROM invlocationDB WHERE loc_invID = inv_ID) is null then 0 else (SELECT sum(loc_OnOrder) FROM invlocationDB WHERE loc_invID = inv_ID) end) as OnOrder,

inv_ID,
'S' as Type

FROM inventoryDB order by inv_prodCode Asc

Out of 15 executions it scored an average of 7.8 sec


Query 2:

SELECT

I.inv_ProdCode,
I.inv_SDesc,

(case when sum(D.loc_Freestock) is null then 0 else sum(D.loc_Freestock) end) as FreeStock,
(case when sum(D.loc_Allocated) is null then 0 else sum(D.loc_Allocated) end) as Allocation,
(case when sum(D.loc_OnOrder) is null then 0 else sum(D.loc_OnOrder) end) as OnOrder,

I.inv_ID,
'S' as Type


FROM inventoryDB I, invLocationDB D
WHERE D.loc_invID = I.inv_ID
group by inv_ProdCode,I.inv_SDesc,I.inv_ID
order by I.inv_prodCode Asc


Out of 15 executions it scored an average of 8secs


What do you think and can u help me optimize it!!
Thanks alot
Nick

 

[r937]

personally, i think COALESCE is more elegant than a CASE structure testing for NULL

select ...
coalesce(sum(D.loc_Freestock),0) as FreeStock,
coalesce(sum(D.loc_Allocated),0) as Allocation,
coalesce(sum(D.loc_OnOrder),0) as OnOrder,
...

rudy

http://r937.com/

 

[Fori]

OK Thanks i'll check it out!! but what about the queries!! how can i make them faster!

Thanks
Nick

 

[r937]

index your join fields

 

[Fori]

Could be an idea.... how do you do it xactly!!

 

[r937]

usually with the CREATE INDEX statement, but this may vary from database to database

(you do realize you're in the ANSI SQL forum, right?)

 

[Fori]

Oh sorry !! you're right!! for a moment i tought i'm in the SQL Server forum oops!!

thanks for your immense help

Nick