SQL query with execution time and server load problems
-
I'm trying to get the last post of each App_Name from this log-database.
The problem is that this query takes 25 seconds to execute and the server load is to great.
SELECT L1.[App_Name],
DATEDIFF(ss,MAX(L1.MESG_DATESTAMP),GETDATE()) as Seconds
FROM sbyn_csf.CSF_CME_LOG AS L1
JOIN sbyn_csf.CSF_LOGGER_LOG AS L2 ON L2.MESG_ID = L1.MESG_ID
WHERE logger_code = 90
AND [App_name] in ('002','010','022','025','036','037','040')
GROUP BY L1.[APP_NAME], L2.LOGGER_CODE
ORDER BY 1, 2
I've tried to add
WHERE L1.MESG_DATESTAMP BETWEEN '2010-01-01 00:00:00' AND GETDATE()but that didn't help much.I'm glad for any help!
-
Instead of a group by clause, I'd suggest going with either an ORDER BY and select the TOP 1 value or use the OVER ORDER BY with the ROW_NUMBER in a sub-select and take only those that have a row_number value of 1.
Either way will work. But, the real question is performance. What do your execution plans look like? You showed a sample structure, but where do you have indexes within these tables?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You might be better of taking the values in your in clause and putting them in a table with an index and then join on them because the IN gets treated like a series of OR's by the optimizer which likely causes an Index scan instead of seek.
Could you post a query plan?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hello!
Thanks for your replies, I going to try this.
My execution plans looks like:
-
If you right click on the plan and "Save As" a .sqplan file, you can post this online so that we can see more information. But, you do have an index scan there that's causing a great deal of pain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Ditto for posting the actual plan.
Also, you may want to use the OPTION (MaxDOP 1) query hint because it is unlikely you need parallelism on this query and that
could be causing the problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Here's the execution plan http://camart.se/temp/CSF.sqlplan
Hope this helps you help me 😀
-
Can you post table definitions and some sample data? I'd like to play with a couple of things.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Here is a small part of the database, approximately 1000 rows of data.
http://www.camart.se/temp/db.zip
SELECT L1.[App_Name],DATEDIFF(ss,MAX(L1.MESG_DATESTAMP),GETDATE()) as Seconds
FROM CSF_CME_LOG AS L1
JOIN CSF_LOGGER_LOG AS L2 ON L2.MESG_ID = L1.MESG_ID
--WHERE L1.MESG_DATESTAMP BETWEEN '2000-01-17 00:00:00' AND GETDATE()
WHERE logger_code = 101
--AND [App_name] in ('002','010','022','025','036','037','040')
GROUP BY L1.[APP_NAME], L2.LOGGER_CODE
ORDER BY 1, 2
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply