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_MailHouseFile_December2015 WHERE orderId IN (
                        SELECT orderid
                        FROM orders)

This used to work great but recently it seems to hang

Any suggestions on alternatives????
johnnyg123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Try this and also generate an execution plan (ctrl-M in SSMS) and post a screen shot of that as well.

SELECT COUNT(M.orderid) AS OrderCount
FROM vDirectMail_Incentive_MailHouseFile_December2015 AS M
INNER JOIN orders AS O
   ON M.orderid = O.orderid

If your "hang" is a performance issue then you will want to make sure you have an index on the DirectMail table's orderid column.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
There's not enough information here to answer your question.  Please post the T-SQL behind the view, and posting the execution plan as an attachment would be great too.

btw the IN clause can be real inefficient if the orders table is huge.  As a knee-jerk reaction, try this..

SELECT count(orderid)
FROM vDirectMail_Incentive_MailHouseFile_December2015 vdm
   JOIN (SELECT DISTINCT orderid FROM orders) o ON vdm.OrderID = o.OrderID

Open in new window


Also, I see you have the month and year name in the view.  How big are the tables that it uses, and perhaps a partitioning solution would be in order if there are a large number of rows.
johnnyg123Author Commented:
Unfortunately,

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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Brian CroweDatabase AdministratorCommented:
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".
Lee SavidgeCommented:
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
johnnyg123Author Commented:
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
Jim HornMicrosoft SQL Server Data DudeCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.