Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Looking for a faster count than count(*) for my sp. Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 3:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 3,499, Visits: 7,551
Here are some suggestions that might be wrong but I can't test performance without actual knowledge of your environment:
- Instead of using a CTE, use a temp table to insert the rows and use @@ROWCOUNT (as suggested by Gail) to count all the records you have.
- You could try changing the ROW_NUMBER() with an identity in the temp table.
- Follow Sean's advice on the way to create a catch-all-query by going to Gail's blog http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ (here's the link once again)
- Leave the order to the front-end. It seems that you have lots of options to order and your way seems messy and with possibility of problems if you want to add a new order.
- Drop the NOLOCK hint, it won't help over performance and might just give you inaccurate results.
-Don't believe someone is great just because it has several years of experience and has worked with big companies. I've done both things and that's not the reason I'm good (good but working on being great).

If you come for help, don't turn down just because you don't agree. This forum is full of talented and respected people that volunteer to help anyone.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501774
Posted Friday, October 4, 2013 6:15 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Thanks Luis and Gail for your help.

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
Post #1501802
Posted Saturday, October 5, 2013 5:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 42,767, Visits: 35,866
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 Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1501830
Posted Saturday, October 5, 2013 6:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
Luis, Gail and Sean, Thanks for your comments on this post. Including OP, anyone else who is following this post, will add something to his Knowledge.

Luis, I disagree on not using NOLOCK. Even my project uses it in all reporting queries, and removing NOLOCK will result in tons of calls and mails at helpdesk.
Post #1501833
Posted Saturday, October 5, 2013 7:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 42,767, Visits: 35,866
T.Ashish (10/5/2013)
Luis, I disagree on not using NOLOCK. Even my project uses it in all reporting queries, and removing NOLOCK will result in tons of calls and mails at helpdesk.


Do you know what NOLOCK actually does? Do all the users of those reports know that their reports are giving them incorrect data from time to time?

NOLOCK is not a performance tuning tactic, it's a hint that tells SQL to ignore locks and return potentially incorrect data (including duplicate rows and missing rows). Take a look through all your helpdesk calls, see how many you have for 'My report's showing incorrect results', if you have nolock everywhere you will have those (probably ignored because no one can reproduce the errors)

I have seen a banking report intended for auditors that was 20% too high because a couple of rows had been duplicated because of the nolock. If that had gone to the auditors, the bank would have been looking at millions in fines. Do you want to be the one responsible for something like that?

I've taken multiple systems that had nolock everywhere, tuned the queries and removed the nolocks as I went and the average number of 'slow reports' calls dropped as I did so.

If there's no time to tune queries and you want reports that don't block, there's an isolation level for that - read committed snapshot. No locks taken by readers and no risk of the incorrect results that NOLOCK gives.

btw, these dup/missing rows aren't things that happen once in a blue moon. I can set up a trivial demo that has a simple query returns rows read twice due to nolock about once every 5-10 seconds.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1501834
Posted Saturday, October 5, 2013 8:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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" ?
Post #1501836
Posted Saturday, October 5, 2013 8:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 42,767, Visits: 35,866
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 2008, MVP
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

Post #1501838
Posted Saturday, October 5, 2013 9:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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
Post #1501840
Posted Saturday, October 5, 2013 10:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 23,227, Visits: 31,924
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1501841
Posted Monday, October 7, 2013 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,273, Visits: 12,105
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502149
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse