Looking for a faster count than count(*) for my sp.

Question

Looking for a faster count than count(*) for my sp.

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

isuckatsql SSChampion Points: 10381 More actions · Answer 1

GilaMonster (10/5/2013)
isuckatsql (10/4/2013)
Sean, you make a lot of suggestions about all the things i am doing wrong, yet offer nothing helpful.
This is a discussion forum, so we discuss things, if you don't want to help then don't, but it serves no purpose to turn it into a pi**ing contest.
Nothing helpful, like his suggestion on how to do catch all queries, the question about the ramification of nolock, the question about the correlated subqueries (most of which you ignored)?
I'm personally getting the opinion you want verification you're right, not help, so I'm done here.

Gail,

I responded to his comment about correlated subqueries with Joins, and it was slower!

I know the pros and cons of NOLOCK.

I was looking for faster count assistance, which Sean did not help me with, all he wanted to do was tell me how bad the query was, and how i wasted thousands of dollars on unqualified DBA's, both of which are wrong!

Anyone can picks things apart, its the smart ones that offer solutions!

I have ended up getting far more productive answers on Google than i have here, which has sadly been very typical of this site.

I periodically come back, but a lot of the 'attitude' remains, which sadly is common among IT people!

Have you heard the term "pay it forward" ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

isuckatsql (10/5/2013)
Have you heard the term "pay it forward" ?

Do enlighten me, what should I be doing to 'pay it forward'?

I offered you advice on the count (as did Luis), you didn't acknowledge it. Your procedure has a catch-all query in it which gives terrible inconsistent performance (and the recompile on the procedure doesn't help that query form), we advised you how to redo that portion of the query, you didn't acknowledge it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

isuckatsql SSChampion Points: 10381 More actions · Answer 3

Gail,

I was not implying you do not 'pay it forward', you most certainly do, and always have!

I was referring to Sean.

Apologies for the confusion.

Ian

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

If you would really like some help with this, how about posting the DDL for the tables, some sample data for each of the tables, sample inputs for the procedure, and expected results for each of the inputs to the procedures.

I am certain that you would get something better than you have right now. I have looked at the code but I'm not quite certain what you are actually wanting from the procedure. Are you looking for a page of data + the total number of rows possible?

Plus, any changes I make to your code, I have nothing to test it against or to determine if it actually does what you expect. A little hard to help when there isn't enough to work with.

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

isuckatsql (10/4/2013)
Sean, you make a lot of suggestions about all the things i am doing wrong, yet offer nothing helpful.
This is a discussion forum, so we discuss things, if you don't want to help then don't, but it serves no purpose to turn it into a pi**ing contest.
Sean, since you offer no value to this discussion, i suggest you move along 🙂

Actually I was trying to point out that you were being given some advice and were defiantly ignoring that advice and defending the code that caused you to post here in the first place. In direct opposition to your assumption that I don't want to help, the very reason I posted is because I do want to help. Trust me I don't need to post on a forum to make myself feel good. I did in fact offer something that would be helpful if you would drop the attitude and read what I wrote. The biggest performance issue by far in your query is the "catch all" code. I posted a link to what is widely considered the best article on the topic.

I sincerely hope you are able to figure out a solution to your problem. You will however be doing it without my help because you have asked me to not assist you. Best of luck.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/