March 2, 2016 at 2:10 am
tony28 (3/1/2016)
First I think it was my mistake that I wrote "NameOfColumn" instead of right values... so DATE is char(8) like 20160302 and seq is char(4) like from 0001 till 9999...
Yeah, that's one of the problems with asking online help. When you post a 50,000-line stored procedure, the helpers will respond with a request to simplify it to show just the problem area and nothing else because we don't have time to go over those monsters. And then when you do simplify and the helpers later find that a detail you left out turns out to be important, you'll get chastised over that.
Are char(8) and char(4) the actual data types of the columns 1PKCOLUMN and 2PKCOLUMN? Are the values used in the query hardcoded values, or are they variables, or parameters? In the latter case, what data types are those variables/parameters? Or is your actual unsimplified query doing a join and are these 1/2PKCOLUMN columns compared to data from a joined table?
Like you now, sometimes is very hard to change design of the tables if lot of applications select something, and you cannot modify applications or the design created someone else...
Yeah, I know. Also, I absolutely do not know if combining the two columns is really a good idea, that depends on so many factors I do not know.
This is reason why I have 1PKCOLUMN in where alone, and then combination..before than I started work here it was only combination and this result was very, but very poor. And almost every time scan all tables, now is only if statistics are not updated...
And that was a very good change. Yes, with just the second test you would always get a full scan.
Did you already test the modified version I suggested?
Now second day I am trying do Update Statistics With FULLSCAN and it looks like little helpful, but it was increased some CXPacket and some wait with CPU...
CXPACKETS wait are not a problem. They just indicate that you have parallelism.
What really matters is "user waits" - in other words is your performance now acceptable or are people still waiting for the database to respond? If they do, then is the time to look at wait statistics and other resources to try to find a root cause.
If this query is still slow, could you perhaps post an actual execution plan? (Note that this plan will show table and column names, unless you first use SQL Sentry Plan Explorer to anonymize it - just sayin' because I notice that you try hard to not show us the actual column names used). If you do post an execution plan, then please also post the total number of rows in all relevant tables.
March 2, 2016 at 4:05 am
Right actual data types.
Almost all time are parameters in procedures except few manual query..I know how procedures working and with first execution is still same execution plan.
If Iam using for reporting with very different scope of parameters , for example somebody can select whole year or one day, I am using with Recompile, because what I investigated and know from studying is not good using same ex.plan for all ...
If am using for application almost all time are selected same data except few exceptions, so here is not needed .
My point was if I will do UPDATE STATISTICS will be helpful for queries
on app procedures is possible that ex.plan will be good, if first execution will be for updated data.
in reporting procedures depends also when somebody execute first time, but here is very specific scope
Your suggestion I tried, but here is example when is not possible..
For example I need get top 5 like result below for two columns ( this is after night shift, when new orders come )
20160302,9997
20160302,9998
20160302,9999
20160303,0001
20160303,0002
... CXPACKETS right like you wrote, I just wanted to mean that it was increased..
And like I wrote, my purpose of this wasnt that somebody complain for something, my users are very lazy, if query is long they are going to cook coffee 😀
but I found that sometimes query useless reads lot of logical reads... So because I am nature curious, I wanted to know if is possible to avoid this...
Now I decide that will try do STATISTICS WITH FULLSCAN and will check performance of server and then query..
Thanks for advices and help.
March 2, 2016 at 4:42 am
tony28 (3/2/2016)
Your suggestion I tried, but here is example when is not possible..For example I need get top 5 like result below for two columns ( this is after night shift, when new orders come )
20160302,9997
20160302,9998
20160302,9999
20160303,0001
20160303,0002
I don't see why this would be impossible. The query (using hardcoded values for clarity here) in my format would be
WHERE 1PKCOLUMN >= '20160302'
AND NOT (1PKCOLUMN = '20160302' AND 2PKCOLUMN < '9997')
The first line selects everything on 20160302 and later; the second line excludes the items before 20160302/9997.
This may sound like more work, but SQL Server might find a plan that does this efficiently. And this form may be easier for the cardinality estimator, so you could get a better estimate of the rowcount, resulting in a better overall plan choice.
March 2, 2016 at 9:49 am
Don't dismiss this too quickly, you may be able to eliminate the need for manual stats work altogether. Just sayin.
Good luck
March 3, 2016 at 3:45 am
Hugo Kornelis (3/2/2016)
tony28 (3/2/2016)
Your suggestion I tried, but here is example when is not possible..For example I need get top 5 like result below for two columns ( this is after night shift, when new orders come )
20160302,9997
20160302,9998
20160302,9999
20160303,0001
20160303,0002
I don't see why this would be impossible. The query (using hardcoded values for clarity here) in my format would be
WHERE 1PKCOLUMN >= '20160302'
AND NOT (1PKCOLUMN = '20160302' AND 2PKCOLUMN < '9997')
The first line selects everything on 20160302 and later; the second line excludes the items before 20160302/9997.
This may sound like more work, but SQL Server might find a plan that does this efficiently. And this form may be easier for the cardinality estimator, so you could get a better estimate of the rowcount, resulting in a better overall plan choice.
I tried it.. after statistics update looks almost same..
(1763 row(s) affected)
Table 'Table'. Scan count 1, logical reads 216
your idea query
TotalSubtreeCost 13.86579
EstimateRows 1160180
current query
TotalSubtreeCost 14.70268
EstimateRows 353613.7
tomorrow will try before update stats if it has some benefit..
If it will have some benefit, maybe will thinking about change all procedures,, but it will be for long time..
But anyway good idea, I was not thinking about it like this..
Thanks
March 4, 2016 at 6:09 am
tony28 (3/3/2016)
I tried it.. after statistics update looks almost same..(1763 row(s) affected)
Table 'Table'. Scan count 1, logical reads 216
your idea query
TotalSubtreeCost 13.86579
EstimateRows 1160180
current query
TotalSubtreeCost 14.70268
EstimateRows 353613.7
I do not agrgee that an estimated rowcount of 1.1 million is almost the same as an estimated rowcounf of 350 thousand.
Please verify (by looking at an actual execution plan) which of these two rowcounts is closest to the truth. Also test both queries for performance - not the cost in the execution plan (which is just a meaningless estimate), but actual runtime and actual IO used (use SET STATISTICS TIME ON and SET STATISTICS IO ON).
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply