You can try JOIN.

SELECT DISTINCT p.pub_name
FROM publishers p
JOIN titles t ON p.pub_id = t.pub_id

Didi you analyze execution plan of your queries to determine what could slow them down?

>I like to know how do we optimize SQL code
>using IN into separate query from
>
>USE pubs
>SELECT pub_name
>FROM publishers
>WHERE pub_id IN <============
> (SELECT pub_id
> FROM titles
> WHERE type = 'business')
>
>Is there any better way to query the subquery first,
>then place the result inside the IN (...)
>
>Like
>
>USE pubs
>SELECT @result = pub_id
> FROM titles
> WHERE type = 'business'
>
>
>SELECT pub_name
>FROM publishers
>WHERE pub_id IN (@result)
>
>
>Since most of my SQL stored procedure is slowing down
>due to subquery. I am trying to optimize it
>and not sure why some query is working, some is halting
>my DB (lock). It does not have this problem when my
>DB size is less than 1GB.
>