T-SQL proc that takes forever.
This is a T-SQL problem I have been chasing for 3 months and haven't found a fix for... I think I am close, but other problems keep undermining my progress... darn it...
I have a stored procedure that is used to match incoming customers with existing customers on the database. I know, it is a pig from the word go, but for the first 2 years, it has run like a Swiss watch.
Back in May, this step has gone sideways and is taking upwards of 4 hours to complete... here is the rub, 90% of the time, it happens on a Monday morning... processing data from Sunday, the lowest volume day of the week.
The rest of the time, the match takes about 30 minutes. Today, for example, it took 5.6 hours to process 3400 records... that's 6 seconds per record!!! I have a problem with that. Something is hitting this hard... and yes, I know today is not Monday, but the last time it freaked out on a non-Monday, was also a Thursday. I am sensing a pattern... and I don't like it!
How it works:
1. A VB App calls the proc, passing name, address, email and phone data
2. The proc then compares combinations, looking for matches. The matches are in order of importance, name/address, name/email, etc
3. A match is only performed if the record has not yet been matched, meaning, if it matches by name/address, the name/phone is ignored
4. If a new record exists, it is then inserted into the database... that includes the name, address, phone and email tables.
5. The next record is then processed and the loop continues.
Again, I know, not perfect, but by processing each record, it allows the next record to be tested against the newest added record.
I do want to redo this process, and I am coding the new version, but what baffles me is the Monday only extended run time. This I do not get, and what I also don't get is why other processes and updates don't do the same thing. I get no error messages from the system, nothing else is running...
I run backups nightly, along with integrity checks and index rebuilds... no love.
Could this be in the VB part? Could that be bogging things down? The act of calling the SP? I am using VB 2015 Enterprise Edition, compiled CPU agnostic.
Any ideas?
Thanks in advance,
Andy
I have a stored procedure that is used to match incoming customers with existing customers on the database. I know, it is a pig from the word go, but for the first 2 years, it has run like a Swiss watch.
Back in May, this step has gone sideways and is taking upwards of 4 hours to complete... here is the rub, 90% of the time, it happens on a Monday morning... processing data from Sunday, the lowest volume day of the week.
The rest of the time, the match takes about 30 minutes. Today, for example, it took 5.6 hours to process 3400 records... that's 6 seconds per record!!! I have a problem with that. Something is hitting this hard... and yes, I know today is not Monday, but the last time it freaked out on a non-Monday, was also a Thursday. I am sensing a pattern... and I don't like it!
How it works:
1. A VB App calls the proc, passing name, address, email and phone data
2. The proc then compares combinations, looking for matches. The matches are in order of importance, name/address, name/email, etc
3. A match is only performed if the record has not yet been matched, meaning, if it matches by name/address, the name/phone is ignored
4. If a new record exists, it is then inserted into the database... that includes the name, address, phone and email tables.
5. The next record is then processed and the loop continues.
Again, I know, not perfect, but by processing each record, it allows the next record to be tested against the newest added record.
I do want to redo this process, and I am coding the new version, but what baffles me is the Monday only extended run time. This I do not get, and what I also don't get is why other processes and updates don't do the same thing. I get no error messages from the system, nothing else is running...
I run backups nightly, along with integrity checks and index rebuilds... no love.
Could this be in the VB part? Could that be bogging things down? The act of calling the SP? I am using VB 2015 Enterprise Edition, compiled CPU agnostic.
Any ideas?
Thanks in advance,
Andy
Éric Moreau
Very tough to be able to say anything. You (because from our side we can't) need to find out where the time is taken. What is VB doing? What is the SQL stored proc doing? You will need to find out by tracing or debugging what is the taking all the time.
Post a copy of your procedure.
And likely there's some way to enable logging of SELECT/UPDATE statements taking a long time or using no indexes.
Also look for code which uses LIKE %..% inefficiently.
If you're running exactly the same workload + this problem occurs, then likely something else is hammering your disk.
Windows... Sigh... With Linux you can pretty much figure out i/o hogs instantly.
Maybe someone one has a tool suggestion to track i/o over specific time slices, then you can look for... likely write hogs during the time frame when your system slows down.
Hint: Move this code out of a procedure into normal code, so you can debug/instrument your code better.
Procedure debugging tends to be much harder than SQL which is scripted in some language, like PERL or PHP.
And likely there's some way to enable logging of SELECT/UPDATE statements taking a long time or using no indexes.
Also look for code which uses LIKE %..% inefficiently.
If you're running exactly the same workload + this problem occurs, then likely something else is hammering your disk.
Windows... Sigh... With Linux you can pretty much figure out i/o hogs instantly.
Maybe someone one has a tool suggestion to track i/o over specific time slices, then you can look for... likely write hogs during the time frame when your system slows down.
Hint: Move this code out of a procedure into normal code, so you can debug/instrument your code better.
Procedure debugging tends to be much harder than SQL which is scripted in some language, like PERL or PHP.
To solve the problem of the same new customer appearing more than once in the input file, you can eliminate the duplicates on that file first. Then use several joins to match to the main table. That should be much faster than running a separate query for each input row.
ASKER
Thank you for your comments. The SP is attached.
I am in the process of redesigning this... as I said, it isn't perfect, but it was working great. Now it is pigging on random Monday's, and now, the occasional Thursdays. Since 28 May, it has run long like this at least half the time. On average, every other Monday. It will go for 2 weeks great, then fail for 2 weeks... most odd.
I have checked server logs, SQL logs and nothing is throwing up a flag. I checked all indexes and they are ok.
Match.txt
I am in the process of redesigning this... as I said, it isn't perfect, but it was working great. Now it is pigging on random Monday's, and now, the occasional Thursdays. Since 28 May, it has run long like this at least half the time. On average, every other Monday. It will go for 2 weeks great, then fail for 2 weeks... most odd.
I have checked server logs, SQL logs and nothing is throwing up a flag. I checked all indexes and they are ok.
Match.txt
That doesn't tell us where the time is spent. Create a test database to mimic the Monday load and give a try to a profiler to find out where your time is consumed
What does your Execution Plan look like?
Any high estimates? If you run the proc with the actual execution plan, do the actual number of rows and expected number match (statistics updated)? If indexes aren't fragmented, do you need a new index on a high cost scan?
SET SHOWPLAN_ALL ON
GO
SET FMTONLY ON
GO
EXEC sp_Match <params>
GO
SET FMTONLY OFF
GO
SET SHOWPLAN_ALL OFF
GOAny high estimates? If you run the proc with the actual execution plan, do the actual number of rows and expected number match (statistics updated)? If indexes aren't fragmented, do you need a new index on a high cost scan?
ASKER
I will have to test it to get you that information. I will get back as soon as I am done... but it will take a couple of hours, other fires just started!
Andy
Andy
ASKER
I ran with an execution plan, and nothing flagged. All lookups were quick, using about 3% to 5% of the query. inserts were about 12%.
One thing I did... part of the data I am processing is related to email blast requests. All I have is an email address, so I pulled that out and created a separate prematch process that handles those in a batch, vs one by one. This morning, the match ran in record time - 15 minutes.
I think I might be on to something.
Also, I neglected to include - the VB is being run by Win 2012R2 Task Scheduler... I learned today about run priority. Scheduler defaults to a 7, 4 is ideal. So I bumped it up to 4. I won't know if that helps until tomorrow.
One thing I did... part of the data I am processing is related to email blast requests. All I have is an email address, so I pulled that out and created a separate prematch process that handles those in a batch, vs one by one. This morning, the match ran in record time - 15 minutes.
I think I might be on to something.
Also, I neglected to include - the VB is being run by Win 2012R2 Task Scheduler... I learned today about run priority. Scheduler defaults to a 7, 4 is ideal. So I bumped it up to 4. I won't know if that helps until tomorrow.
ASKER
To those following this thread, I found something huge... then we lost our IT data center to ransomware...
I have a names table, this holds customer names. I had a bug which loaded it with 70 million repetitive records.
Smith Company
- and -
Smith Company Peoria
The bug caused a flipflop load of both... thanks to an OR statement vs and AND statement.
Otherwise, the email preload helped tons.
I have a names table, this holds customer names. I had a bug which loaded it with 70 million repetitive records.
Smith Company
- and -
Smith Company Peoria
The bug caused a flipflop load of both... thanks to an OR statement vs and AND statement.
Otherwise, the email preload helped tons.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.