johnnyg123
asked on
sql count
I need to know the number of occurrences of orderid in that occurs in a separate table
I had been using
SELECT count(orderid)
FROM vDirectMail_Incentive_Mail HouseFile_ December20 15 WHERE orderId IN (
SELECT orderid
FROM orders)
This used to work great but recently it seems to hang
Any suggestions on alternatives????
I had been using
SELECT count(orderid)
FROM vDirectMail_Incentive_Mail
SELECT orderid
FROM orders)
This used to work great but recently it seems to hang
Any suggestions on alternatives????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
exec sp_who2 to see if you have any blocking going on. You can run it from another connection while the query is "locked up".
You could use the table hint "with nolock"
Table hints aren't recommended but they can bail you out occasionally.
For example: select * from myTable with nolock
Table hints aren't recommended but they can bail you out occasionally.
For example: select * from myTable with nolock
ASKER
Thanks all
I thought I had put nonclustered index for order id but apparently I had not
As soon as I added one, all is well
I thought I had put nonclustered index for order id but apparently I had not
As soon as I added one, all is well
Thanks for the split. Good luck with your query. -Jim
btw which table did you add a nonclustered index for order id? I would think that an orders table would have OrderID as a clustered primary key.
btw which table did you add a nonclustered index for order id? I would think that an orders table would have OrderID as a clustered primary key.
ASKER
query never completes (used to take a few seconds but I've let it go 30 minutes and still nothing) so I can't get the query plan