November 23, 2008 at 5:23 am
Hi,
we use an ERP program that users run queries likes that very often.
i run this query in the DTA and its say that nothing should be changed in the indexes (0% improve)
can i improve this syntax in any way(this query run for 7SEC)?
THX
November 23, 2008 at 7:42 am
The DTA can be a somewhat useful tool, but it's not terribly reliable.
Do you have an execution plan for the query? That would help identify what's occuring within your system.
First off, that is a huge output list. You may see performance problems just from the volume of data you're attempting to move.
A few things are jumping out at me that you might want to look at.
First:
INNER JOIN pilot.dbo.XVR_SETTLEMENT
ON 1 = 1
That is not going to use an index and will result in a table scan as well as an undefined join. What are you trying to get at with that bit of the query?
There are several joins that look like this:
LEFT OUTER JOIN pilot.dbo.TAXES
ON (pilot.dbo.TAXES.TAX = COALESCE(pilot.dbo.ORDERSA.TAX, 0))
That COALESCE statement is going to prevent index use, so you will get table scans. I don't know the data or structure, but this is going to be problematic. You need to do the join with the OR constract that COALESCE represents. Same thing with the stuff in the WHERE clause.
I'm sure others can point out more.
I took a couple of minutes and laid out the TSQL so it was readable (may the gods keep & protect Red Gate & SQL Prompt). It might be helpful to others that follow after me.
"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
November 23, 2008 at 7:47 am
i think because he's locked into using the ERP to generate the queries and cannot adjust them, we are a bit limited on suggestions....the executtion plan will help a lot, but i think the only thing we'll be able to do is suggest indexes on certain columns that were int he joins and where statemetns.
Lowell
November 23, 2008 at 8:00 am
Ah, true, I hadn't thought about that. Still, the execution plan can point out where an index might help. He might be able to use a plan guide but I'll bet the generated code is different each & every time.
"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
November 23, 2008 at 8:11 am
Grant Fritchey (11/23/2008)
I took a couple of minutes and laid out the TSQL so it was readable (may the gods keep & protect Red Gate & SQL Prompt). It might be helpful to others that follow after me.
Heh... and now, the fine print...
This is a real person and not a compensated actor.
A few things are jumping out at me that you might want to look at.
First:
INNER JOIN pilot.dbo.XVR_SETTLEMENTON 1 = 1
That is not going to use an index and will result in a table scan as well as an undefined join.
Another name "undefined join" is "Cross Join". You need to figure out how the XVR_SETTLEMENT relates to the other tables and establish a real join. 😉
Also, there are a total of 62 tables being joined... that's gotta be a record for me... I've never seen such a thing before. I'd recommend that if you really need this output, that someone sit down and break it into optimized pieces instead of trying to do it all in a single query.
Grant's tips on how the use of Coalesce in the WHERE clause will never allow and Index Seek are pretty much spot on, as well.
Heh... remind me to never let the users at work design anything, especially with an ERP tool.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2008 at 8:20 am
Jeff Moden (11/23/2008)
Grant Fritchey (11/23/2008)
I took a couple of minutes and laid out the TSQL so it was readable (may the gods keep & protect Red Gate & SQL Prompt). It might be helpful to others that follow after me.Heh... and now, the fine print...
This is a real person and not a compensated actor.
A few things are jumping out at me that you might want to look at.
First:
INNER JOIN pilot.dbo.XVR_SETTLEMENTON 1 = 1
That is not going to use an index and will result in a table scan as well as an undefined join.
Another name "undefined join" is "Cross Join". You need to figure out how the XVR_SETTLEMENT relates to the other tables and establish a real join. 😉
Also, there are a total of 62 tables being joined... that's gotta be a record for me... I've never seen such a thing before. I'd recommend that if you really need this output, that someone sit down and break it into optimized pieces instead of trying to do it all in a single query.
Grant's tips on how the use of Coalesce in the WHERE clause will never allow and Index Seek are pretty much spot on, as well.
Heh... remind me to never let the users at work design anything, especially with an ERP tool.
62 is your record? You need to let your developers play more. We had one with 82 tables once. They were really shocked when it ran slowly. The question I kept getting was "What's wrong with SQL Server? If it lets me do this, it must be OK."
"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
November 23, 2008 at 8:35 am
Grant Fritchey (11/23/2008)
62 is your record? You need to let your developers play more. We had one with 82 tables once. They were really shocked when it ran slowly. The question I kept getting was "What's wrong with SQL Server? If it lets me do this, it must be OK."
Heh... no... haven't seen one like this before. The tools that generate code like this one and like Hibernate are gettin' kinda spooky and I've always resisted using them because of things like the inadvertant cross join this one made and the disrespect they have for things like sargability, etc. The good part about people using such things is that people like many of us will always be needed to fix stuff espcially in the area of "performance and scalability of code"... and very high dollar values. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2008 at 9:29 am
Jeff Moden (11/23/2008)
Heh... no... haven't seen one like this before. The tools that generate code like this one and like Hibernate are gettin' kinda spooky and I've always resisted using them because of things like the inadvertant cross join this one made and the disrespect they have for things like sargability, etc. The good part about people using such things is that people like many of us will always be needed to fix stuff espcially in the area of "performance and scalability of code"... and very high dollar values. 😉
Too true. I've often thought, especially recently, especially at PASS, about taking on a consulting gig rather than working for just one company, but I don't want to travel.
I am looking forward (with great trepidation I might add) to the generated queries we're going to see out of nHibernate. I suspect I'll get days of entertainment out of them.
"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
November 23, 2008 at 9:53 am
thanks for your replays.
i can't touch the query because it come directly from the ERP(Priority).
can i do something else (beside DTA)to check this query to get better performance?
THX
November 23, 2008 at 10:28 am
Your options are severely limited.
First, check the execution plan. Identify where the scans are occurring, if any, and see if you can affect them with an index or three. Think compound indexes. The more you can make your indexes covering in this scenario, the better. With such a complex query, I almost guarantee the DTA missed opportunities.
After that... you can look at... possibly improving perceived performance with a plan gude and an OPTION FAST 1 (note the key word and tricky phrase, "perceived". Actual performance will in fact suffer). That's the problem with generated code. Your options are very limited. You might be able to get a good plan and force it using the USE PLAN option, but such a huge query, that's going to be extremely difficult to pull off, if it will even do anything for you. I would suggest that you test the possibility of turning FORCED PARAMETERIZATION on on your database. It won't necessarily help with this query, but if everything is coming in ad hoc and dynamic, it should help overall.
Seriously though, I'm unsure that any of these, except the indexes, will do you a bit of good.
"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
November 23, 2008 at 11:10 am
do you have the ability to make the ERD use a specific view for the report, instead of underlying tables?
if that were true, you could cleanup some of the queries, by making them join table to a view that pulls the right data together to avoid the 1-1 joins.
Lowell
November 23, 2008 at 5:14 pm
Mad-Dog (11/23/2008)
thanks for your replays.i can't touch the query because it come directly from the ERP(Priority).
can i do something else (beside DTA)to check this query to get better performance?
THX
Ok... no disrespect meant, but didn't you ask how you could optimize the query? If you can't touch it, then there's no question here. The ability to do anything with index optimization is negated by the poor form of both the WHERE clause and the fact that the code has a cross join in it. Translation... it's broke and it's gonna continue to be broke until someone decides that you can actually touch the query.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2008 at 12:41 pm
in continuing to our discussion , i've a query that run for 34SEC.
if i remove the exec sp_executesql and fill all the parameters like in the query it run in less then 1SEC.
is there anything i can setup in the SQL that will use the query plan wisely?
THX
exec sp_executesql N'select top 1 system.dbo.USERSB.SNAME , pilot.dbo.XVR_MANNING.PROFITCENTER_A , pilot.dbo.XVR_MANNING.PROFITCENTER_B
from pilot.dbo.INVOICEITEMS inner join system.dbo.T$$INVOICES on ( system.dbo.T$$INVOICES.IV = pilot.dbo.INVOICEITEMS.IV ) and system.dbo.T$$INVOICES.T$LINKID = ltrim(rtrim( @P1 )) inner join pilot.dbo.ORDERITEMS on ( pilot.dbo.ORDERITEMS.ORDI = pilot.dbo.INVOICEITEMS.ORDI ) inner join pilot.dbo.XVR_MANNING on ( pilot.dbo.XVR_MANNING.MANNING = pilot.dbo.ORDERITEMS.XVR_MANNING ) inner join pilot.dbo.ORDERS on ( pilot.dbo.ORDERS.ORD = pilot.dbo.ORDERITEMS.ORD ) inner join pilot.dbo.PART on ( pilot.dbo.PART.PART = pilot.dbo.INVOICEITEMS.PART ) inner join system.dbo.USERSB on ( system.dbo.USERSB.USERB = pilot.dbo.XVR_MANNING.USERB )
where not exists ( select @P2
from pilot.dbo.INVOICEITEMS A
where ( A.IV = pilot.dbo.INVOICEITEMS.IV ) and ( A.KLINE <> pilot.dbo.INVOICEITEMS.KLINE ) and ( A.ORDI = pilot.dbo.INVOICEITEMS.ORDI ) and ( A.COSTC <> pilot.dbo.INVOICEITEMS.COSTC ) ) and ( pilot.dbo.PART.XVR_ASSIGNATION = @P3 ) and ( pilot.dbo.XVR_MANNING.PROFITCENTER_A <> pilot.dbo.XVR_MANNING.PROFITCENTER_B ) and ( pilot.dbo.XVR_MANNING.PROFITCENTER_A <> @P4 ) and ( pilot.dbo.XVR_MANNING.PROFITCENTER_B <> @P5 ) and ( system.dbo.T$$INVOICES.IV <> @P6 ) and system.dbo.T$$INVOICES.T$LINKID = ltrim(rtrim( @P7 ))
',N'@P1 varchar(18),@P2 char(1),@P3 char(1),@P4 int,@P5 int,@P6 int,@P7 varchar(18)','TRG07:ezz42401par:','X','Y',0,0,0,'TRG07:ezz42401par:'
November 26, 2008 at 1:16 pm
It sounds like you're getting parameter sniffing. You need to force a recompile on the procedure. Take a look at plan guides and see if you can implement one with the OPTION RECOMPILE hint.
"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
November 26, 2008 at 9:21 pm
That would certainly help... but the OP said he can't touch the code because it's generated by and ERP. This is all vry much a moot issue.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply