Best way to determine SQL performance issue

I have been working on a SQL routine that pulls POs from a legacy application and inserts them into a modern ERP solution. In our data center the process pulls in all 20,000 POs in less than 20 minutes. We just tried to deploy this routine in the customer's environment and the process took over 10 hours. This routine deals with about 10 tables in two databases that are housed in the same instance of MS-SQL 2012. What tools can I run on both ends to identify what is causing the bottleneck on their system?
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk StraussSenior Full Stack DeveloperCommented:
In our data center the process pulls in all 20,000 POs in less than 20 minutes.
I'm sorry to have to say, but that is rather slow in my opinion. If you are using SQL Server Management Studio, why not try some of the built-in tools. Have a look at Performance Monitoring and Tuning Tools.
Check that the tables you are querying have indexes and that there aren't unnecessary UNION, DISTINCT or JOIN statements.

Another article you might like to have a look at: How to analyze SQL Server database performance using T-SQL
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
20K of rows in 20 minutes isn't slow. It's stopped!!!
Anyway, best thing to do is to analyze the Query Execution Plan. It's where you should start as it can show you how the query is executed and you can identify what's delaying the query.
0
PortletPaulfreelancerCommented:
Is extensive ETL being performed?  
Are you using stored procedures with cursors? (avoid cursors if you can)
Are you using scalar functions?  (these may be an issue particularly if used in joins or where clauses)

List out the steps each PO is subjected to, which is those is the most complex?
Moving through that list from most complex down; study that part of your code (execution plan if possible)

best wishes, we feel your pain
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rwheeler23Author Commented:
This is for an ERP solution provided by Microsoft. There are two stored procedures being called. Both of these sp's perform extensive data validation. SSIS cannot be used because of these sp's. There is only one cursor being used but I could illuminate that. It is used to query the legacy database to get the list of purchase orders. The rest are the T-SQL commands to first get the PO lines and then the PO header. The command that inserts the PO headers does perform a group by to get the PO totals before inserting the PO header. The point of my question was to get ideas as to why in production it runs so slowly as compared to the test environment. The databases are copies of each other. When I say old legacy database I mean something that was written in MS Access in the 1990's. I was able to talk the client into moving it into SQL 2012 but that is all that was done. There are no indexes to work with no will they allow me to create ones.  It was just so odd to see it run in 20 minutes on our image and take 10 hours on their server.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
There are no indexes to work with no will they allow me to create ones.
Indexes are the way to speed up queries. If they don't let to create indexes I can't see much to be done.
If you can't provide the code or the query execution plan, definitively, we can't help you much here.
0
rwheeler23Author Commented:
The generation of the executions plans will  be happening very soon and I will forward them along.
0
rwheeler23Author Commented:
Here are the two execution plans. The one called GSE was run on their server and the one called RBS was run on our server. Theirs took five times as long to run. What this script does is copy over PO data but first it has get setup values. I do not know how to read these properly so please let me know if these tell you anything.
GSE.sqlplan
RBS.sqlplan
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you provide the schema for PA43001 table, including the existing indexes?

You're working with a cursor but we don't have the cursor definition, only the name (po_line_cursor), so we can't provide recommendations for the cursor.

The following SELECT:
if not exists(select * from SVC00998)
	SELECT 
		@VENDORID = ISNULL(POH.VENDORID,'UNKNOWN'),
		@PODATE = ISNULL(POH.PODATE,'01/01/1900'),
		@REQDATE = ISNULL(POH.REQDATE,'01/01/1900'),
		@TERMS = ISNULL(POH.TERMS,'UNKNOWN'),
		@SHIPVIA = ISNULL(POH.SHIPVIA,'UNKNOWN'),
		@ISCLOSED = ISNULL(POH.ISCLOSED,0),
		@VGPCODE = case 
					WHEN VEN.VGPCODE IS NULL THEN VEN.VENDERID 
					ELSE LTRIM(RTRIM(VEN.VGPCODE)) 
				END,
		@VENDNAME = ISNULL(VEN.NAME,'UNKNOWN'),
		@PONUMBER = case PATINDEX('%-%',POH.PONUM) 
						WHEN 0 THEN POH.PONUM 
						ELSE SUBSTRING(POH.PONUM,0,PATINDEX('%-%',POH.PONUM)) 
					END
	from [GSE2000SQL].[dbo].[POHEADER] POH
		INNER JOIN [GSE2000SQL].[dbo].[VENMAS] VEN ON POH.VENDORID=VEN.VENDERID
	WHERE CASE PATINDEX('%-%',POH.PONUM) 
			WHEN 0 THEN POH.PONUM 
			ELSE SUBSTRING(POH.PONUM,0,PATINDEX('%-%',POH.PONUM))
		END = @I_vPONumber
	ORDER BY POH.PONUM

Open in new window

Can have the WHERE clause improved as the following one:
if not exists(select * from SVC00998)
	SELECT 
		@VENDORID = ISNULL(POH.VENDORID,'UNKNOWN'),
		@PODATE = ISNULL(POH.PODATE,'01/01/1900'),
		@REQDATE = ISNULL(POH.REQDATE,'01/01/1900'),
		@TERMS = ISNULL(POH.TERMS,'UNKNOWN'),
		@SHIPVIA = ISNULL(POH.SHIPVIA,'UNKNOWN'),
		@ISCLOSED = ISNULL(POH.ISCLOSED,0),
		@VGPCODE = case 
					WHEN VEN.VGPCODE IS NULL THEN VEN.VENDERID 
					ELSE LTRIM(RTRIM(VEN.VGPCODE)) 
				END,
		@VENDNAME = ISNULL(VEN.NAME,'UNKNOWN'),
		@PONUMBER = case PATINDEX('%-%',POH.PONUM) 
						WHEN 0 THEN POH.PONUM 
						ELSE SUBSTRING(POH.PONUM,0,PATINDEX('%-%',POH.PONUM)) 
					END
	from [GSE2000SQL].[dbo].[POHEADER] POH
		INNER JOIN [GSE2000SQL].[dbo].[VENMAS] VEN ON POH.VENDORID=VEN.VENDERID
	WHERE POH.PONUM LIKE '%' + @I_vPONumber + '%'
	ORDER BY POH.PONUM

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
And you don't need to check every time for the existence of records on SVC00998 table. Only once will be enough. For that you just need to put all the UPDATEs in a BEGIN ... END block:
(...)
if not exists(select * from SVC00998)
BEGIN
    UPDATE dbo.POP10110 SET DEX_ROW_TS = GETUTCDATE() ....
    UPDATE POP10110 SET INVINDX = @MISC_FP_WIP_INDEX WHERE PONUMBER = @PONUMBER AND CostCatID = 'MISC'
    (...)
    SELECT 
		@VENDORID = ISNULL(POH.VENDORID,'UNKNOWN'),
		@PODATE = ISNULL(POH.PODATE,'01/01/1900'),
    (...)
END

SELECT @ErrorString = ErrorDesc FROM [DYNAMICS].[dbo].[taErrorCode] WHERE [ErrorCode] = @ErrorState
(...)

Open in new window

0
rwheeler23Author Commented:
Here is the definition for the PA43001 table. I should rewrite this without the cursor. I just do not understand the time difference on our server versus the client server.

CREATE TABLE [dbo].[PA43001](
      [PAsfid] [smallint] NOT NULL,
      [PArecordid] [char](15) NOT NULL,
      [PAcosttrxid] [char](17) NOT NULL,
      [PAaccttype] [smallint] NOT NULL,
      [PAACTINDX] [int] NOT NULL,
      [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKPA43001] PRIMARY KEY NONCLUSTERED
(
      [PAsfid] ASC,
      [PArecordid] ASC,
      [PAcosttrxid] ASC,
      [PAaccttype] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
0
rwheeler23Author Commented:
I have no control over this:
if not exists(select * from SVC00998)

There are two routines provided by Microsoft that I have to use.
taPOHdr and taPOLine. These two routines do much data validation before records are inserted.
0
rwheeler23Author Commented:
By the way, the record count for the source tables are:

21,292 - PO Header
49,731 - PO Line

I think the delay is being caused by the inserts to the database tables. What this routine does is truncate the destination tables and then reads the source tables and repopulates the destination tables.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I should rewrite this without the cursor.
Agree. Cursor runs row by row and this may affect negatively the query performance.

I just do not understand the time difference on our server versus the client server.
If they don't have the same specifications then it's enough to have performance differences. Also, the amount of data in each database can have impact on the performance.

I have no control over this:
if not exists(select * from SVC00998)
What do you mean with you don't have control? You don't have access to the code or you can't change it?
0
rwheeler23Author Commented:
I do not have access to the stored procedure. Microsoft has locked it down. All I can do is call it as specified.
I have taken a copy of all databases and tables involved in the query. The only thing that is different is the physical server.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you can't change the SP then it will be very hard to improve it.
Only thing you can do is to provide more resources to the server but don't expect an huge performance increase.
What are both server specifications?
0
rwheeler23Author Commented:
Our server is an image running under VM Ware. Their server is a physical box. Both are running W2008 R2 server with SQL 2012. The RAM is the same and our drives are faster but not that must faster.  I am going to try running this routine at night under lower load conditions to see if it makes a difference.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
And the databases have the exactly data or there are differences?
How much memory the servers have and how much is configured for SQL Server usage?
Also, how many CPUs the servers have ?
0
rwheeler23Author Commented:
There is only one database involved. I copy the entire database from their server and copy it onto ours. I will connect later and check on the CPU count. Ours only has two. We have plenty of other scripts that run identically on both servers. The biggest difference with this one is that there are about a dozen tables involved in one join.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Doesn't matter the number of databases but the size of them.
Don't forget to say the amount of memory and how it's configured for SQL Server.
0
rwheeler23Author Commented:
My apologies, there is much going on right now. I hope to report on this later this week.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Any news on this?
0
rwheeler23Author Commented:
Thanks everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.