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?
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.
0

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 Developer, Architect, and AuthorCommented:
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.
0
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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".
0
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
0
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.