October 7, 2010 at 4:08 pm
Hi,
I've a table with 20 mill records.
when i run this simple query but with the sp_executesql the query run for 30 sec
when i run the with out the sp_executesql it run 1 sec.
i see that the execution plan is different in the 2 query.
why is that and how i can fix this?(my ERP program use the sp_executesql all the time )
THX
--script A
dbcc dropcleanbuffers
dbcc freeproccache
GO
exec sp_executesql N'select PART,PARTNAME,BARCODE
from PART
where PARTNAME
like ltrim(rtrim( @P1 ))
',N'@P1 nvarchar(13)',N'202000000000%'
--script B
dbcc dropcleanbuffers
dbcc freeproccache
GO
select PART,PARTNAME,BARCODE
from PART
where PARTNAME
like ltrim(rtrim( '202000000000%' ))
October 7, 2010 at 4:32 pm
Mad-Dog (10/7/2010)
I've a table with 20 mill records.when i run this simple query but with the sp_executesql the query run for 30 sec
when i run the with out the sp_executesql it run 1 sec.
i see that the execution plan is different in the 2 query.
why is that and how i can fix this?(my ERP program use the sp_executesql all the time )
THX
--script A
dbcc dropcleanbuffers
dbcc freeproccache
GO
exec sp_executesql N'select PART,PARTNAME,BARCODE
from PART
where PARTNAME
like ltrim(rtrim( @P1 ))
',N'@P1 nvarchar(13)',N'202000000000%'
--script B
dbcc dropcleanbuffers
dbcc freeproccache
GO
select PART,PARTNAME,BARCODE
from PART
where PARTNAME
like ltrim(rtrim( '202000000000%' ))
Execution via sp_executesql is using a bind-variable as a search argument while manual execution is using a literal.
For query-optimizer this usually makes a huge difference at the time of selecting an execution plan. Plans based on bind-variables are expected to be reused, optimizer doesn't know the actual value of the search argument.
You may want to try a hint to force a plan like the one you see on manual execution.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 8, 2010 at 1:02 am
thanks for the replay.
what are my other options if i can't use HINT on the query because i can't mess with the query that come out from the ERP?
October 8, 2010 at 1:29 am
Curious , since both queries include the non-sargable expression ltrim(rtrim(<value>)), both should result in then same , very poorly performing , execution plan.
Can you post both the plans ?
You may be able to fix this by adding a computed column of ltrim(rtrim( PARTNAME )) and then placing an index on that.
October 8, 2010 at 1:42 am
Notice that you are using 2 different data types in your query. In one of them you use ASCI character, and in the other one you are using Unicode. I believe that this is the cause of the different query plans.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 11, 2010 at 4:21 am
hi
i upload the database to new server (more stronger CPU,MEM,DISK I/O).
and i see strange results when i run the same query.
is there any explanation to that kind of results while the databases are identical?(both SQL are 2008 64BIT sp1 diffrent OS)
--query
exec sp_executesql N'select hasavot.dbo.PART.PARTNAME , hasavot.dbo.PART.PART , hasavot.dbo.PART.TYPE , hasavot.dbo.PART.T$PROC , hasavot.dbo.PART.PARTDES , hasavot.dbo.PART.UNIT , hasavot.dbo.PART.UPD , hasavot.dbo.PART.LEADTIME , hasavot.dbo.PART.STATUS , (0.0 + ( convert(decimal(20,3), hasavot.dbo.PART.FATQUANT) )) , (0.0 + ( convert(decimal(19,2), hasavot.dbo.PART.PRICE) )) , (0.0 + ( convert(decimal(16,2), hasavot.dbo.PART.LASTPRICE) )) , (0.0 + ( convert(decimal(19,2), hasavot.dbo.PART.COST) )) , hasavot.dbo.PART.REV , hasavot.dbo.PART.PRIVTYPE , hasavot.dbo.PART.PATTERN , hasavot.dbo.PART.SERNPATTERN , hasavot.dbo.PART.PUNIT , (0.0 + ( hasavot.dbo.PART.CONV )) , hasavot.dbo.PART.AVGDATE , hasavot.dbo.PART.KITFLAG , (0.0 + ( convert(decimal(17,3), hasavot.dbo.PART.DOLLAR) )) , hasavot.dbo.PART.TAKEFLAG , hasavot.dbo.PART.WTYPE , hasavot.dbo.PART.PREFERRED , hasavot.dbo.PART.EXCESS , hasavot.dbo.PART.PARTPARAM , hasavot.dbo.PART.PDES , hasavot.dbo.PART.COSTDATE , hasavot.dbo.PART.MPART , hasavot.dbo.PART.AUTOSERIAL , hasavot.dbo.PART.SIZEBAR , hasavot.dbo.PART.CURRENCY , (0.0 + ( convert(decimal(13,2), hasavot.dbo.PART.SCRAP) )) , (0.0 + ( convert(decimal(19,2), hasavot.dbo.PART.SECONDCOST) )) , (0.0 + ( convert(decimal(19,2), hasavot.dbo.PART.SECONDPRICE) )) , (0.0 + ( convert(decimal(13,3), hasavot.dbo.PART.COSTQUANT) )) , hasavot.dbo.PART.ACTUALCOSTTYPE , hasavot.dbo.PART.SERNFLAG , hasavot.dbo.PART.FAMILY , hasavot.dbo.PART.CURDATE , hasavot.dbo.PART.LASTCURRENCY , hasavot.dbo.PART.LASTCOSTFLAG , hasavot.dbo.PART.REVFLAG , hasavot.dbo.PART.PRICEFLAG , (0.0 + ( convert(decimal(16,2), hasavot.dbo.PART.FOBPRICE) )) , hasavot.dbo.PART.FOBCURRENCY , (0.0 + ( convert(decimal(16,2), hasavot.dbo.PART.PURPRICE) )) , hasavot.dbo.PART.T$USER , hasavot.dbo.PART.UDATE , hasavot.dbo.PART.BARCODE , (0.0 + ( convert(decimal(16,2), hasavot.dbo.PART.MAXREPAIRPRICE) )) , hasavot.dbo.PART.REPAIRCURRENCY , hasavot.dbo.PART.UNSPSC , hasavot.dbo.PART.MOLDPART , (0.0 + ( convert(decimal(16,2), hasavot.dbo.PART.RBALANCE) )) , hasavot.dbo.PART.PARTTYPE , hasavot.dbo.PART.PARTSTAT , hasavot.dbo.PART.OWNER , hasavot.dbo.PART.EXTFILEFLAG , hasavot.dbo.PART.LOCFLAG , hasavot.dbo.PART.PRICEPOLICY , hasavot.dbo.PART.TURNKEY , hasavot.dbo.PART.SHOWINWEB , hasavot.dbo.PART.PALLETTYPE , hasavot.dbo.PART.SUPERPHARM , hasavot.dbo.PART.NOTFIXEDCONV , hasavot.dbo.PART.LOTBYVENDOR , (0.0 + ( convert(decimal(16,2), hasavot.dbo.PART.MINPRICE) )) , hasavot.dbo.PART.STORAGETYPE , hasavot.dbo.PART.PIKORDER , hasavot.dbo.PART.CREATEDDATE , hasavot.dbo.PART.TEL_CUST , hasavot.dbo.PART.TEL_SUBCUSTID , hasavot.dbo.PART.TEL_PATIENTID , hasavot.dbo.PART.TEL_MEMBERID , hasavot.dbo.PART.TEL_FILE , hasavot.dbo.PART.TEL_PARTDES
from hasavot.dbo.PART
where hasavot.dbo.PART.PARTNAME like ltrim(rtrim( @P1 ))
',N'@P1 nvarchar(13)',N'202000000000_'
--old machine io statics
Table 'PART'. Scan count 9, logical reads 1480237, physical reads 10458, read-ahead reads 1376467, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--new machine io statics
Table 'PART'. Scan count 17, logical reads 1475446, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
October 11, 2010 at 4:23 am
Please post the execution plans as per
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 11, 2010 at 4:31 am
execution plans attached.
THX
October 11, 2010 at 4:45 am
The plans are the same 'shape' , there is a slight difference in the number of estimated rows (not much though).
The Difference in your io statistics you quoted
--old machine io statics
Table 'PART'. Scan count 9, logical reads 1480237, physical reads 10458, read-ahead reads 1376467, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--new machine io statics
Table 'PART'. Scan count 17, logical reads 1475446, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
look to me like on the new machine , the data is already in memory. no physical reads = no disk io.
as stated , The ltrim(rtrim IS KILLING YOU.
This needs to be resolved asap. Are you able to change this ?
October 11, 2010 at 5:42 am
no i can't change the query.
this is the weird thing i run the query a lot of time in the slow server and the results are the same 1.5 min to finish and i'm not dropping the cache before running the query,while in the new server after the first run it take 2 sec to finish.
October 11, 2010 at 5:49 am
Are they doing the same quantity of physical reads ?
--EDIT, also go back to the developers / designers / third party provider and tell them that this is an unacceptable query. Scanning this amount of record is a poor use of the machines time an resources.
Try doing as i previously suggested (adding a computed column of ltrim(rtim and put an index on it). SqlServer should then do an index seek and your performance issue here will disappear.
October 11, 2010 at 5:54 am
no physical reads on the new server on the second run.
if i remove the (ltrim(rtirm()) the results are very good.
why the ltrim rtrim do that?
October 11, 2010 at 6:15 am
Because now , sqlserver can use an index.
the ltrim ( rtrim are stopping that from happening.
This is the difference between Sargable and Non-Sargable.
Using a function (there are a few exceptions) will force sqlserver to scan the table and evaluate the function FOR EACH row.
October 11, 2010 at 6:44 am
OK.
and the difference in the sql server machine while the database are the same,can i check anything on that matter?
October 11, 2010 at 6:52 am
Mad-Dog (10/11/2010)
OK.and the difference in the sql server machine while the database are the same,can i check anything on that matter?
From what you have said so far, the difference sounds like on the new machine all the data it needs is in memory , on the old its is having to pull the data from the disks (very slow in comparison).
If the new machine is not in production,
clean the cache with 'DBCC dropcleanbuffers'. Now the new machine will have to go to disk.
Is there now a difference ?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply