March 12, 2014 at 8:49 pm
Hi guys,
I have question about this hint...
What do you think about this.
For example is this good, if I use variable in where clausule ?
What I know a procedure is started first time with some compilation plan and then is same, regardless of that variables were changed. In this time I use hint WITH RECOMPILE, but not allways is needed
Today I tested something and found problem with logical reads, very different without recompile and with recompile= like directly written value.
And without recompile looks like scan all table. So I dont want this.
So my one way is use this hint, right ?
March 12, 2014 at 11:13 pm
There's nothing really wrong with using a parameter in a WHERE clause, however in cases where the parameter could drastically change the results, then it could have an impact on the execution plan generated. For instance, depending on what statistics you have for the tables/columns being queried (or how updated they are to begin with), a query that returns a small result set for an small account, may produce a different plan at a later time for a larger account. While in most cases the execution plan should be the same, the plan (or several copies of the plan are cached so they can be re-used).
Using WITH RECOMPILE at the procedure level basically forces SQL to generate a brand new plan each and every time the procedure runs, regardless of whether or not a prior cached plan would have worked or not. Use this option carefully!
Employing OPTION(RECOMPILE) at a statement level can be better (as other parts of the procedure code will be cached) but still forces sql to generate a new execution plan each time the code runs for that particular statement. This involves extra resources, and may not always be a good decision if your code runs multiple times throughout the minute/hour/day, etc.
You might have noticed a difference in the logical reads due to several things: stale statistics, index fragmentation, implicit conversion between the WHERE clause predicate and the parameter value being passed in, etc. Try rebuilding the indexes used in the execution plan or updating the statistics, and verify that there is no implicit conversions taking place...assigning a literal value and passing in a value via a parameter isn't always the same...for instance:
WHERE This = 1
Versus:
D ECLARE @Value varchar(1)
SET @Value = 1
<<code here>>
WHERE This = @Value
This isn't the same providing that the predicate "This" is an integer datatype. An implicit conversion will occur between the varchar data type and the integer datatype...which will typically result in an index scan.
Please post your code and the execution plan, the experts around here will have it fixed up in no time!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 13, 2014 at 2:15 am
As with all hints, use sparingly, only where needed and only after understanding exactly why the hint is needed. It's not something to dump onto every single statement.
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
March 13, 2014 at 6:41 am
You said variables several times. Be sure to differentiate between variables and parameters. A parameter, defined with the stored procedure or in a parameterized query, can be sampled, or sniffed, by the optimizer. That makes the plans generated specific to that value (usually a very good thing). Local variables, DECLARE @something, are not sniffed except in a recompile situation. So, from what you said, it's possible that if you're using a local variable and you're putting RECOMPILE on lots or all of your queries, you're getting bad sniffing through the variable.
Like Gail said, any, all, hints should be use sparingly and only after lots of testing to validate that the hint is the appropriate one. No hints should be blasted into all queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2014 at 4:38 pm
thanks for answer
I have to remember index and statistics, I forgot for this almost every time during investigation ...
Maintenance plan is at saturday, so I rebuild all indexes and statistics in these tables, but results is same , after using hint is better
funny is that yesterday I insert to where clausule first column of index and use this hint and I solve also problem with parallelysm, but today with little more data parallelysm is here again ...
execution plan shows missing index, so I added two columns to noncluster index and I removed this hint. And result is very better and more than before with hint!!
Thank you for advice. I first time use this hint , because I know that can cause problem , but I wanted to try, but finnaly I found better solution ...
sorry for english.
March 13, 2014 at 4:58 pm
this is query - I have procedure for this, problem is that TB_RCV_ALCDATA table is very sensitive, there is lot of data and this table use lot of programs and there is adhoc query ... Korean style ..>
declare @stationchar(4)
set @station = 'P370' --PBSOut
DECLARE @TEMP_ORDER_DATE char(8)=(
SELECT PROD_DATE FROM
(
SELECT PROD_DATE,
ROW_NUMBER() OVER (ORDER BY PROD_DATE desc ) AS Row
FROM( SELECT distinct top 4 PROD_DATE from [dbo].TB_RCV_ALCDATA order by PROD_DATE desc ) A
)B WHERE Row=4)
-- PBSout(P370)???, DEL(SD)? ??? ????? ????.
-- DEL ??? P370?? ???????? ??? ??!
--first procedure
SELECT TOP 1 A.TR_ID,
A.DATA_TYPE, A.PROD_DATE,
A.COMMIT_NO, A.BODY_NO, C.CAR_CODE
FROM [dbo].[TB_RCV_ALCDATA] A with(nolock)
left outer join [dbo].[TB_CODE_CARTYPE] C with(nolock)
on substring(A.BODY_NO,1,2) = C.CAR_PROJECT
WHERE
PROD_DATE>=@TEMP_ORDER_DATE AND --added
A.WO_FLAG = 'T' AND A.MASTER_FLAG = 'F'
ORDER BY A.TR_ID
--second procedure
SELECT TOP 1 A.*,
--B.TECH_SPEC, B.OPT_219,B.PART_UNIQUE,
C.CAR_CODE
FROM [dbo].[TB_RCV_ALCDATA] A with(nolock)
--left outer join [dbo].[TB_RCV_FSC] B with(nolock)
--on A.FSC_TDATA = B.FSC_TDATA
left outer join [dbo].[TB_CODE_CARTYPE] C with(nolock)
on substring(A.BODY_NO,1,2) = C.CAR_PROJECT
WHERE
PROD_DATE>=@TEMP_ORDER_DATE AND --added
A.WO_FLAG = 'F'
AND ( A.STATION_ID = @station OR A.DATA_TYPE = 'SD' )
ORDER BY A.TR_ID
March 13, 2014 at 5:02 pm
I forgot script for tables and data, in attachment you can see. I hope that is it in right format, I didnt do long time .. but in our tables is data from 2months, and this is just from one day.
March 13, 2014 at 5:05 pm
And now I found one thing, if it will be data like I sent, only from one day with one PROD_DATE, the query above for @TEMP_ORDER_DATE will be null or nothing right ?? I think that is not possible to do in our database, because every time are data almost from 14days after move to PRE_DB, but for sure....Some idea how to fix it ?? any case ??
March 13, 2014 at 5:24 pm
OK I fixed so for declare @TEMP_ORDER_DATE use this
DECLARE @TEMP_ORDER_DATE char(8)=(
SELECT TOP 1 PROD_DATE FROM
(
SELECT PROD_DATE,
ROW_NUMBER() OVER (ORDER BY PROD_DATE asc ) AS Row1
FROM( SELECT distinct top 4 PROD_DATE from [dbo].TB_RCV_ALCDATA order by PROD_DATE desc ) A
)B ORDER BY Row1
)
March 13, 2014 at 5:40 pm
here is my optimizing progress ,,original procedure it was very slow, I dont understand how the programmer can be satisfied with this..
BEFORE
With Parallelism
Table 'TB_RCV_ALCDATA'. Scan count 9, logical reads 59682
SQL Server Execution Times:
CPU time = 157 ms, elapsed time = 1164 ms.
or
SQL Server Execution Times:
CPU time = 377 ms, elapsed time = 555 ms.
or
SQL Server Execution Times:
CPU time = 315 ms, elapsed time = 398 ms.
AFTER
Without Parallelism, but today with
Table 'TB_RCV_ALCDATA'. Scan count 1, logical reads 15767
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 31 ms.
or
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 68 ms.
AFTER AGAIN
Table 'TB_CODE_CARTYPE'. Scan count 1, logical reads 2
Table 'TB_RCV_ALCDATA'. Scan count 1, logical reads 82
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
March 13, 2014 at 7:08 pm
Glad you got it figured out without resorting to using the hints 😉
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 13, 2014 at 7:52 pm
MyDoggieJessie (3/13/2014)
Glad you got it figured out without resorting to using the hints 😉
Thank you, I am glad also 🙂
March 13, 2014 at 10:36 pm
Can somebody explain me one more thing?
I have again problem, but on another query, after using this hint I got good results,, without it same like without optimize..
Without hint Ex.plan shows again problem with missing index, but with hint result is better but sometimes it shows missing index also and sometimes didnt show problem with missing index ( I think depends on query)
Of course result was better after change index , but sometimes I am not sure ( I didnt create ) if with modified index I cannot cause problem on another query.. Is any way how can I do it ??
Create directly new index ?? Do you have experience, how much can be indexes in tables? If you have good reading about it, you can post link...
Thank you
March 14, 2014 at 6:15 am
You should never opt to use RECOMPILE over looking to see if you need an index (and creating it if it is needed). That being said, you don't want to go creating indexes all over the place when it might not be needed. The Execution plans index suggestions need to be taken with a grain of salt...in my opinion, it's a trigger for me to look more closely at my WHERE clause, JOINS, and SELECT's to ensure I've not created any non-sargable predicates, or added new columns to my SELECT that would trigger a keylookup against the clustered index or scan. There are a few things that could lead to the execution plan indicating this as a "false positive".
Again, please post your code for the new query. For starters (at a high level), ensure that the columns in your WHERE clause and JOINS are the key columns in your index, and the items in your SELECT have been included in the index as well. While you may have many queries that will use the same index, you might find (through testing) that creating a couple slightly different indexes work better for you than creating one giant "catch-all" index that covers everything.
You need to test.
Check out this link: http://sqlinthewild.co.za/index.php/category/sql-server/indexes/
It contains many blog articles about indexing, between those and the stairway series here on SSC you should have all you ever wanted to know about indexing...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 14, 2014 at 8:05 pm
Thank for advice and link
Here is next thread for this query .. http://www.sqlservercentral.com/Forums/Topic1551418-391-1.aspx?Update=1
I have problem with variable = I declare same type like in Design of this table, but estimated rows are very different if I use variable or directly values. I tried on three databases and on every db I had different ex plan, hah 😀 Is any solution how can I prevent non recompile(or how can I call this. ) query due to variables ?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply