April 15, 2009 at 7:00 am
wow this is very strange....
I've created two tables with with the columns in your query added data and indeed the query runs in secs if not milliseconds on 2000 and well have yet to get a result in 2005 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 7:06 am
Can you post the actual execution plan instead of the estimated one? I'd really like to see, as much as possible, what's happening.
This is a weird one. Also, can you post the 2000 plan as a text plan? I hate the bloody things, but that's the only way to transmit the underlying data in 2000.
Thanks,
Grant
"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
April 15, 2009 at 7:59 am
Here is the actual execution paln for the query run on 2005
I'm not sure how to get the 2000 text plan you are talking about? Please excuse the ignorance
April 15, 2009 at 8:03 am
Can you tell me more about the configuration of the server all of this is running on?
April 15, 2009 at 8:03 am
You can use SET SHOWPLAN_ALL or SHOWPLAN_TEXT.
ALL provides more information but it's more difficult to read. Let's go with ALL despite the difficult, but you can post both.
"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
April 15, 2009 at 8:05 am
I am confused as to why the performance is so different between the two, but the fact that performance is sub-optimal is because of the functions on the columns in the WHERE clause. You're just not going to get index use out of that. If you need to run this query all the time, what about creating a derived column on the side. Use a trigger to populate it with values so that you can do a straight select with a standard WHERE clause against it instead of all this funky manipulation of strings.
"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
April 15, 2009 at 8:20 am
Grant Fritchey (4/15/2009)
I am confused as to why the performance is so different between the two, but the fact that performance is sub-optimal is because of the functions on the columns in the WHERE clause. You're just not going to get index use out of that. If you need to run this query all the time, what about creating a derived column on the side. Use a trigger to populate it with values so that you can do a straight select with a standard WHERE clause against it instead of all this funky manipulation of strings.
Here is the SHOWPLAN_ALL results
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
SET STATISTICS PROFILE ON12101SETSTATON0
(1 row(s) affected)
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
select * from KRI_ASP_AccFACSNtEqAccConsole_DATA_C where
substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1)
NOT IN
(
SELECT
substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) - patindex('%[^0]%', accountnum) + 1)
FROM
KRI_ASP_AccFACSNtEqAccConsole_DATA_A
)13101313.926760.10347269SELECT0
|--Nested Loops(Left Anti Semi Join, WHERE:((substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])-patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])+1)=NULL OR substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM])-patindex('%[1331Nested LoopsLeft Anti Semi JoinWHERE:((substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])-patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])+1)=NULL OR substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM])-patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_D313.926760.02.5706999E-31070.1034413[KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRTRTP], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRCRNO], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDOCT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF1], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF2], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRSTAT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDTPT]PLAN_ROW01.0
|--Table Scan(OBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_C]))1343Table ScanTable ScanOBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_C])[KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRTRTP], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRCRNO], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDOCT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF1], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF2], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRSTAT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDTPT]615.04.2022943E-27.5499999E-41074.2777944E-2[KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRTRTP], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRCRNO], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDOCT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF1], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF2], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRSTAT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDTPT]PLAN_ROW01.0
|--Table Spool1353Table SpoolLazy Spool1.01.6756756E-22.7999999E-7365.4525658E-2[KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]PLAN_ROW0615.0
|--Table Scan(OBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_A]))1365Table ScanTable ScanOBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_A])[KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]1.03.7578501E-27.9600002E-51683.7658099E-2[KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]PLAN_ROW01.0
(5 row(s) affected)
StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
SET STATISTICS PROFILE OFF14101SETSTATON0
(1 row(s) affected)
April 15, 2009 at 8:25 am
Lynn Pettis (4/15/2009)
Can you tell me more about the configuration of the server all of this is running on?
Its a Windows Server 2003 server SP2
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
April 15, 2009 at 8:37 am
Rowan (4/15/2009)
Lynn Pettis (4/15/2009)
Can you tell me more about the configuration of the server all of this is running on?Its a Windows Server 2003 server SP2
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Single proc? Dual Proc? Single Core? Dual Core? Quad Core? How much memory? Anything else regarding configuration?
April 15, 2009 at 8:49 am
Lynn Pettis (4/15/2009)
Rowan (4/15/2009)
Lynn Pettis (4/15/2009)
Can you tell me more about the configuration of the server all of this is running on?Its a Windows Server 2003 server SP2
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Single proc? Dual Proc? Single Core? Dual Core? Quad Core? How much memory? Anything else regarding configuration?
Sorry, its a Intel Xeon 3.4Ghz with 7 GB of RAM with 4 CPU's
April 15, 2009 at 9:02 am
This may be counter-intuitive. on SQL Server 2005, add this to the end of your query: OPTION (MAXDOP 1)
I would still like you to try my other SQL Server 2005 solution as well.
April 15, 2009 at 9:44 am
If I'm reading the data correctly, the estimated rows on the 2000 query is 13 right through, but the estimated values in 2005 are all over the place, from 1 up to 829 and it ultimately returns 112 rows, but was estimating 493. That's a fairly wide disparity. Can you try updating the statistics with a full scan? Instead of using sp_updatestats, run UPDATE STATISTICS [tablename] WITH FULLSCAN.
I think that's the cause of the disparity between the two plans. Although I could be very wrong.
"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
April 15, 2009 at 9:49 am
Thanks Lynn, i will try that asap
I did try your query where you use 'except'
This did cut the running time down quite a bit
April 15, 2009 at 9:51 am
did you try and update the stats like Grant mentioned. I'm very interested to know why different versions of sql are giving very different results
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 9:53 am
Thanks Grant i will try that asap
Viewing 15 posts - 31 through 45 (of 68 total)
You must be logged in to reply to this topic. Login to reply