Hi Friends,

I have one question about bind variable cause different execution
plan.

When I run the query without bind variable, and hard - coded
parameter, it runs very quickly. After I change the query as below. It
runs very slow. Do you have any idea?

DECLARE @spc varchar(30)
DECLARE @nbr3 varchar(30)
DECLARE @nbr4 varchar(30)
set @spc = 'SACCO'
set @nbr3 = '101007363'
set @nbr4 = '101007363%'

DECLARE @SQLString nvarchar(4000);
DECLARE @ParmDefinition nvarchar(500);


set @SQLString = N'SELECT TOP 101 A.SERV_PROV_CODE AS
SERV_PROV_CODE, ' +
'A.B1_PER_ID1 AS B1_PER_ID1,' +
'A.B1_PER_ID2 AS B1_PER_ID2,' +
'A.B1_PER_ID3 AS B1_PER_ID3,' +
'A.B1_PER_GROUP, ' +
'A.B1_PER_TYPE, ' +
'A.B1_PER_SUB_TYPE, ' +
'A.B1_PER_CATEGORY, ' +
'A.B1_SPECIAL_TEXT, ' +
'A.B1_CREATED_BY_ACA,' +
'A.R3_STD_TIME_CLASS_CODE, ' +
'A.B1_STANDARD_TIME, ' +
'A.B1_EVENT_CODE,' +
'A.B1_REF_ID, ' +
'A.B1_APPL_STATUS,' +
'A.REC_FUL_NAM, ' +
'A.REC_STATUS,' +
'A.PROJECT_NBR, ' +
'A.B1_ALT_ID, ' +
'A.B1_TRACKING_NBR, ' +
'A.B1_APPL_STATUS_DATE,' +
'A.B1_REF_ID1,' +
'A.B1_REF_ID2,' +
'A.B1_REF_ID3,' +
'A.B1_Q_UD1, ' +
'A.B1_Q_UD2, ' +
'A.B1_Q_UD3, ' +
'A.B1_Q_UD4, ' +
'A.APP_STATUS_GROUP_CODE, ' +
'A.B1_MODULE_NAME,' +
'A.B1_FILE_DD,' +
'A.REC_DATE, ' +
'A.B1_REPORTED_DATE, ' +
'A.B1_APPL_CLASS,' +
'A.B1_APP_TYPE_ALIAS,' +
'A.B1_CREATED_BY,' +
'A.B1_INITIATED_BY_PRODUCT,' +
'B.APP_STATUS,' +
'B.APP_STATUS_DATE, ' +
'B.DISPOSITION, ' +
'B.DISPOSITION_DATE, ' +
'B.TOTAL_FEE, ' +
'B.TOTAL_PAY, ' +
'B.PERCENT_COMPLETE, ' +
'B.BALANCE, ' +
'B.BALANCE_DATE, ' +
'B.HOUSE_COUNT, ' +
'B.BUILDING_COUNT,' +
'B.PUBLIC_OWNED, ' +
'B.CONST_TYPE_CODE, ' +
'B.ACTION, ' +
'B.GA_AGENCY_CODE,' +
'B.GA_BUREAU_CODE,' +
'B.GA_DIVISION_CODE, ' +
'B.GA_OFFICE_CODE,' +
'B.GA_SECTION_CODE, ' +
'B.GA_GROUP_CODE,' +
'B.GA_FNAME, ' +
'B.GA_MNAME, ' +
'B.GA_LNAME, ' +
'B.B1_APPL_SUB_STATUS, ' +
'B.B1_SHORT_NOTES,' +
'B.B1_CLOSED_DATE,' +
'B.B1_CLOSEDBY, ' +
'B.B1_REPORTED_CHANNEL,' +
'B.B1_ASGN_DEPT, ' +
'B.B1_ASGN_STAFF,' +
'B.B1_PRIORITY, ' +
'B.B1_SEVERITY, ' +
'B.B1_ASGN_DATE, ' +
'B.TOTAL_JOB_COST,' +
'B.B1_CLOSED_DEPT,' +
'B.B1_COMPLETE_BY,' +
'B.B1_COMPLETE_DEPT, ' +
'B.B1_COMPLETE_DATE, ' +
'B.B1_SCHEDULED_DATE,' +
'B.B1_CREATOR_DEPT, ' +
'B.PM_SCHEDULE_SEQ, ' +
'B.B1_EST_PROD_UNITS,' +
'B.B1_ACTUAL_PROD_UNITS,' +
'B.B1_EST_COST_PER_UNIT,' +
'B.B1_COST_PER_UNIT, ' +
'B.B1_EST_JOB_COST, ' +
'B.B1_PROD_UNIT_TYPE,' +
'B.B1_CREATED_BY AS B1CREATEDBY, ' +
'B.B1_TRACK_START_DATE,' +
'B.B1_ESTIMATED_DUE_DATE, ' +
'B.B1_IN_POSSESSION_TIME, ' +
'B.C6_ANONYMOUS_FLAG,' +
'B.C6_REFERENCE_TYPE,' +
'B.C6_APPEARANCE_DAYOFWEEK,' +
'B.C6_APPEARANCE_DD, ' +
'B.C6_BOOKING_FLAG, ' +
'B.C6_DFNDT_SIGNATURE_FLAG,' +
'B.C6_ENFORCE_OFFICER_ID, ' +
'B.C6_ENFORCE_OFFICER_NAME,' +
'B.C6_INFRACTION_FLAG, ' +
'B.C6_INSPECTOR_ID, ' +
'B.C6_MISDEMEANOR_FLAG,' +
'B.C6_OFFN_WITNESSED_FLAG, ' +
'B.C6_INSPECTOR_NAME,' +
'B.C6_ENFORCE_DEPT, ' +
'B.C6_INSPECTOR_DEPT,' +
'B.APP_STATUS_REASON,' +
'B.FIRST_ISSUED_DATE,' +
'B.UNDISTRIBUTED_JOB_COST, ' +
'B.B1_VAL_MULTIPLIER,' +
'B.B1_VAL_EXTRA_AMT, ' +
'G.EXPIRATION_CODE, ' +
'G.EXPIRATION_STATUS,' +
'G.EXPIRATION_DATE, ' +
'G.EXPIRATION_INTERVAL,' +
'G.EXPIRATION_INTERVAL_UNITS, ' +
'G.GRACE_PERIOD_INTERVAL, ' +
'G.GRACE_PERIOD_INTERVAL_UNITS,' +
'G.PENALTY_INTERVAL, ' +
'G.PENALTY_INTERVAL_UNITS, ' +
'G.NUM_PENALTY_PERIODS,' +
'G.PENALTY_PERIOD,' +
'G.RENEWAL_FEE_FUNCTION,' +
'G.RENEWAL_FEE_CODE, ' +
'G.PENALTY_FEE_FUNCTION,' +
'G.PENALTY_FEE_CODE, ' +
'G.PAY_PERIOD_GROUP, ' +
'G.UDF1, ' +
'G.UDF2, ' +
'G.UDF3, ' +
'G.UDF4, ' +
'C.B1_WORK_DESC ' +
' FROM B1PERMIT A ' +
' LEFT OUTER JOIN BPERMIT_DETAIL B ON A.SERV_PROV_CODE =
B.SERV_PROV_CODE' +
' AND A.B1_PER_ID1 = B.B1_PER_ID1 ' +
' AND A.B1_PER_ID2 = B.B1_PER_ID2 ' +
' AND A.B1_PER_ID3 = B.B1_PER_ID3 ' +
' LEFT OUTER JOIN BWORKDES C ON A.SERV_PROV_CODE = C.SERV_PROV_CODE '
+
' AND A.B1_PER_ID1 = C.B1_PER_ID1' +
' AND A.B1_PER_ID2 = C.B1_PER_ID2' +
' AND A.B1_PER_ID3 = C.B1_PER_ID3' +
' LEFT OUTER JOIN B1_EXPIRATION G ON A.SERV_PROV_CODE =
G.SERV_PROV_CODE' +
' AND A.B1_PER_ID1 = G.B1_PER_ID1 ' +
' AND A.B1_PER_ID2 = G.B1_PER_ID2 ' +
' AND A.B1_PER_ID3 = G.B1_PER_ID3, ' +
' F4PAYMENT PA, F4RECEIPT RE ' +
' WHERE A.REC_STATUS = ''A'' ' +
' AND A.B1_PER_ID3 NOT LIKE ''#%'' ' +
' AND PA.SERV_PROV_CODE = RE.SERV_PROV_CODE' +
' AND PA.RECEIPT_NBR = RE.RECEIPT_NBR' +
' AND A.SERV_PROV_CODE = PA.SERV_PROV_CODE ' +
' AND A.B1_PER_ID1 = PA.B1_PER_ID1' +
' AND A.B1_PER_ID2 = PA.B1_PER_ID2' +
' AND A.B1_PER_ID3 = PA.B1_PER_ID3' +
' AND ((RE.SERV_PROV_CODE = @spc1 AND RE.RECEIPT_NBR = @nbr1 ) OR '
+
' (RE.SERV_PROV_CODE = @spc2 AND RE.RECEIPT_CUSTOMIZED_NBR LIKE
@nbr2 )) ' +
' ORDER BY A.B1_FILE_DD DESC ';

SET @ParmDefinition = N'@spc1 varchar(30), @nbr1 varchar(30), @spc2
varchar(30), @nbr2 varchar(30) ';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@spc1 = @spc
, @nbr1 = @nbr3
, @spc2 = @spc
, @nbr2 = @nbr4
;

lsllcm (lsllcm (AT) gmail (DOT) com) writes:
First of all, I'm afraid that I did not make the effort of trying to
reconstruct the .sqlplan file from your three posts. You mentioned a
link in your post, but I did not see any. Maybe that is only one that
is visible on Google News from where you appear to post. If you are not
able to make proper attachments, the best is to make the file available
for download and post the URL.

It also helps, if you can post CREATE TABLE and CREATE INDEX statements
for your tables.

So to your question, when you hard-code value, the optimizer has more
information than when you use parameters. When you use parameters the
optimizer must account for all possible values the parameters may have,
as the query plan may be reused with other values. This can affect the
outcome.

Next, I don't really understand why you make this as dynamic SQL, but
maybe this is sent from your client in your application.

Anyway, I notice this condition:

Conditions with OR is always a challenge for the optimizer. You may
help the optimizer by writing the query as:

SELECT TOP 101 *
FROM (SELECT ...
...
AND RE.SERV_PROV_CODE = @spc1 AND RE.RECEIPT_NBR = @nbr1
UNION
SELECT ...
AND RE.SERV_PROV_CODE = @spc2 AND
RE.RECEIPT_CUSTOMIZED_NBR LIKE) AS u
ORDER BY B1_FILE_DD


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Nov 14, 2:21*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Reading the plan from the posts was too difficult for me, but I do
have an idea: Make sure your variable is EXACTLY the same data type
as the column to which you are comparing.

I had a case once where the column was a varchar, and I made the
variable an Nvarchar. The query performed horribly. When I changed
the variable to be a varchar, the query sped up by a factor of 4 (or
perhaps it was 10, I can't remember).

I remember someone telling me that the performance penalty for this
type of datatype mis-match can vary based on the collation page you
are using, but I don't remember the details. I believe that with
certain collations an Nvarchar / varchar mismatch can change the plan
from an index seek to a scan.


Thanks,

Bill

bill (billmaclean1 (AT) gmail (DOT) com) writes:
Very good point.

Yes. If you have a Windows collation, SQL Server seeks the index, but
in a more roundabout way. This is possible, because the varchar set
is a true subset of the full Unicode. My tests indicates that this gives
an overhead of factor 2 or 3.

But if you have an SQL collation, there different rules for some characters
between varchar and nvarchar, why the index is dead. How much this costs
you in terms of factor, depends on much data there is. It may over a
thousand times slower - or worse.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx