Query can be improved?

Question

Query can be improved?

GonnaCatchIT

SSCrazy

Points: 2797
More actions
March 23, 2016 at 10:55 pm

#314235

Hi Team,
My SP is timing out , timeout is set to 30 seconds. SP has below query to fetch the youngest id
SELECT TOP(1) ID
FROM Table1 TB1
LEFT OUTER JOIN TB2 VADET ON TB1.VA_SET_INTERNAL_ID = TB2.VA_SET_INTERNAL_ID
AND TB2.STATUS = 'ACTIVE'
GROUP BY ID,
TB1.PIN,
NO_OF_PIN,
TB2.STATUS,
TB1.STATUS HAVING NO_OF_PIN <> COUNT(INT_ID) AND TB1.STATUS = 'ACTIVE'
Though table data are not much huge (about 2 lakh records only).
Can you please provide some pointers on how this query can be changed/improved.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

Without looking at the underlying tables and the query plan even the best SQL expert wont be of any help.Please post query plan,table structure and underlying indexes.

Just a shot in the dark:Try replacing Top(1) with Min.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

GonnaCatchIT (3/23/2016)
Hi Team,
My SP is timing out , timeout is set to 30 seconds. SP has below query to fetch the youngest id
SELECT TOP(1) ID
FROM Table1 TB1
LEFT OUTER JOIN TB2 VADET ON TB1.VA_SET_INTERNAL_ID = TB2.VA_SET_INTERNAL_ID
AND TB2.STATUS = 'ACTIVE'
GROUP BY ID,
TB1.PIN,
NO_OF_PIN,
TB2.STATUS,
TB1.STATUS HAVING NO_OF_PIN <> COUNT(INT_ID) AND TB1.STATUS = 'ACTIVE'
Though table data are not much huge (about 2 lakh records only).
Can you please provide some pointers on how this query can be changed/improved.

With a little formatting it looks like this:

SELECT TOP(1) ID

FROM Table1 TB1

LEFT JOIN VADET TB2

ON TB1.VA_SET_INTERNAL_ID = TB2.VA_SET_INTERNAL_ID

AND TB2.STATUS = 'ACTIVE'

GROUP BY

ID,

TB1.PIN,

NO_OF_PIN,

TB2.STATUS,

TB1.STATUS

HAVING NO_OF_PIN <> COUNT(INT_ID)

AND TB1.STATUS = 'ACTIVE'

Now I might be wrong but this looks like the result of trial and error. TOP and GROUP BY in the same query, with multicolumn partitions but a single scalar output?> It's all bonkers. The only way to tune this is to throw it away and start again from scratch, building the new query by design, not guesswork. If you know what the business rules are (we'll need a lot more than "query to fetch the youngest id") then post them up and folks will help you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Post the execution plan and additional suggestions might come up. But that's a very good suggestion you already have from Chris.

"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

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

And prefix every column in the query with a table alias. Remember, we have no idea what columns are in what tables.

Which table is "ID" in?

Which table is "NO_OF_PIN" in?

Which table is "INT_ID" in?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".