Query performance - 3 part name

Question

Post reply

Query performance - 3 part name

Don Halloran

SSCertifiable

Points: 5937
More actions
May 26, 2011 at 8:37 pm

#251667

Simplified code example. Same query, run from two different database contexts:
use DB_ONE
go
select*
fromDB_ONE.some_schema.some_tablet1
joinDB_ONE.some_schema.other_tablet2on t1.id = t2.id
go
use DB_TWO
go
select*
fromDB_ONE.some_schema.some_tablet1
joinDB_ONE.some_schema.other_tablet2on t1.id = t2.id
The first query, run in the context of the database whose objects are being queried, runs in less than a second.
The second query, run in the context of a different database, runs for at least 8 minutes.
The actual query involved is more complex than what I have here, but not ridiculously so. Code partly obscured for security:
SELECT@scalar = ISNULL(SUM(hd.qty), 0)
FROMsomedb.dbo.hdhd
INNER JOINsomedb.dbo.hmhmonhd.Hire_No = hm.Hire_No
WHERE(hm.Status = 'current' OR hm.Status = 'delivery')
ANDhd.Delivered = 1
ANDISNUMERIC(hd.ReturnNo) <> 1
ANDMachine_ID IN (
SELECTDISTINCT
fm.FLEET_NO
FROMsomedb.dbo.fmfm
INNER JOINsomedb.dbo.fl flonfl.Fleet_No = fm.FLEET_NO
WHEREfm.model = @modelname
ANDfm.FLEET_NO NOT LIKE 'SUB%'
ANDfl.QtyAvail > 10
AND(fm.STATUS = 'IN' OR fm.STATUS = 'OUT')
)
ANDhm.Branch_Code = @BranchCode
All schemas involved are owned by dbo and cross database ownership chaining is enabled.
Can anyone provide any possible reasons for such a huge performance difference?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 1

Latheesh NK

SSCertifiable

Points: 6559

May 26, 2011 at 10:45 pm

#1330774

Are you running the query from same server/machine?

crazy4sql SSCoach Points: 19590 More actions · Answer 2

use DB_ONE
go
select*
fromDB_ONE.some_schema.some_tablet1
joinDB_ONE.some_schema.other_tablet2on t1.id = t2.id
go
use DB_TWO
go
select*
fromDB_ONE.some_schema.some_tablet1
joinDB_ONE.some_schema.other_tablet2on t1.id = t2.id

What your execution plan report about these two different queries?

The actual query involved is more complex than what I have here, but not ridiculously so. Code partly obscured for security:
SELECT@scalar = ISNULL(SUM(hd.qty), 0)
FROMsomedb.dbo.hdhd
INNER JOINsomedb.dbo.hmhmonhd.Hire_No = hm.Hire_No
WHERE(hm.Status = 'current' OR hm.Status = 'delivery')
ANDhd.Delivered = 1
ANDISNUMERIC(hd.ReturnNo) <> 1
ANDMachine_ID IN (
SELECTDISTINCT
fm.FLEET_NO
FROMsomedb.dbo.fmfm
INNER JOINsomedb.dbo.fl flonfl.Fleet_No = fm.FLEET_NO
WHEREfm.model = @modelname
ANDfm.FLEET_NO NOT LIKE 'SUB%'
ANDfl.QtyAvail > 10
AND(fm.STATUS = 'IN' OR fm.STATUS = 'OUT')
)
ANDhm.Branch_Code = @BranchCode

Try to run the inner query on separate window and see how quickly you getting result from inner query?

----------
Ashish

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

I'd check the execution plans. Something has to be changing there. I'm not a fan of cross-database queries anyway.

"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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 4

both Server specifications are same ? like Processor , RAM ,Drives etc

My Blog
www.aureus-salah.com

Don Halloran SSCertifiable Points: 5937 More actions · Answer 5

Yep, same server, same instance, just different databases (the demonstration code in the first post covers it: 3 part name only, no linked servers involved, just switching database contexts).

I'll get the plans tomorrow. I have already determined a predicate that significantly slows down the query. I guess the question, though, is why it uses a different plan from a different database. I have a vague memory of statistics not being available across databases, but I'm finding conflicting information about that now (IIRC I rejected that now-remembered information as incorrect several years ago and haven't worried about it since)

The luxury of not having to do cross database queries unfortunately does not exist, since the ERP is a proprietary system and we are not permitted to modify the schema of that database. We therefore have a sibling database which holds the schema for all internally developed reports and applications which need to query the ERP.

For the curious, the painful predicate is the "ISNUMERIC(hd.ReturnNo) <> 1", which is not surprising since it's a non-sargable inequality (and the isnumeric function probably turns it into RBAR processing too...). I was able to speed up the query significantly after confirming with the dev that given the domain, a simpler hd.returnNo is not null and hd.returnNo != '' was equivalent. By the way, I despise schemas that allow this kind of multi-valued-no-value BS, but as I said, proprietary system, nothing I can do :angry:

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

allmhuran (5/29/2011)
a simpler hd.returnNo is not null and hd.returnNo != ''

You can actually go even simpler and faster because it only has one decision to make...

hd.returnNo > ''

But neither set of criteria is equivalent to your original "ISNUMERIC(hd.ReturnNo) <> 1". Was the intent there to simply find things that are NOT all digits?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

BTW... you can also remove the DISTINCT. It serves no purpose because WHERE/IN stops at the first occurance it finds. The DISTINCT is just burning some extra clock cycles.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Don Halloran SSCertifiable Points: 5937 More actions · Answer 8

Indeed Mr Moden, I've just talked to the dev who has told me that blank, null AND as it turns out zero all mean the same thing in the domain (sigh), so that's both better performing and works properly 🙂

The distinct in the subquery is actually one of the vendor's views, I thought it would be clearer to inline it here.

Still don't know what's going on with the cross database problem, though.