November 15, 2011 at 3:47 am
Hi
I have written a view to surround some code that is used in multiple places, with the idea that it removes repeated logic. However I'm getting some issue in terms of performance which I was not expecting.
My tables looks a bit like this:
CREATE TABLE dbo.Table1 (
IDINT,
String1VARCHAR(50),
String2VARCHAR(50),
String3VARCHAR(50),
CONSTRAINT PK_Table1 PRIMARY KEY (ID)
)
CREATE TABLE dbo.Table2 (
IDINT,
String1VARCHAR(50),
String2VARCHAR(50),
String3VARCHAR(50),
CONSTRAINT PK_Table2 PRIMARY KEY (ID)
)
and the view looks like:
SELECTt1.ID,
t1.String1,
t1.String2,
t2.String1
FROMdbo.Table1 t1
INNER JOIN
dbo.Table2 t2
ONt2.ID = t1.ID
When I then write a query like:
SELECT*
FROMdbo.Table3 t3
INNER JOIN
dbo.vw1 vw
ONvw.ID = t3.ID
It is very slow however... My thought was that by using the PK's in the view would allow the index to be used when joining onto the view?
But looking at the execution plan, the table is being scanned??
As it is, I cannot add an index to the view as it contains a function that cannot be schema bound (our system is a little more complex than my example shows).
Does anyone have any ideas how to utilize the existing table index within a view?
Thanks,
Tom
November 15, 2011 at 5:01 am
Does your actual view or query have a where clause?
Is it table 1, 2, or 3 that's being scanned? What's the DDL for table 3?
Is it a table scan or an index scan?
November 15, 2011 at 5:39 am
Hi,
Thanks for you questions... Your 1st question has actually made me realise the issue!
The view actually looks like:
SELECT t1.ID,
t1.String1,
t1.String2,
t2.String1
FROM dbo.Table1 t1
INNER JOIN
dbo.Table2 t2
ON t2.ID = t1.ID
WHERE t1.String1 IS NOT NULL
I had added it here to remove the repeated logic. However, removing it has caused SQL to use the ID effectively, its just a shame I can't have the logic I wanted... Unless there is a way around this?
Thanks
Tom
November 15, 2011 at 5:43 am
Please post the actual execution plan of the final select that scan.
A scan is not a bad thing. It often beats the hell out of seek + lookup.
Also you only have 1 where and I can't say how much data is being filtered out there. But unless it's an overwhelming majority of the data, a scan is quite possibly the best plan.
November 15, 2011 at 6:25 am
You could use the FORCESEEK table hint, but I wouldn't recommend it.
Does the query with the dummy WHERE clause outperform the one without, and are they synonymous queries?
As Ninja says, scans aren't necessarily bad, and posting your Execution plans would be good 🙂
November 15, 2011 at 6:28 am
Gazareth (11/15/2011)
You could use the FORCESEEK table hint, but I wouldn't recommend it.Does the query with the dummy WHERE clause outperform the one without, and are they synonymous queries?
As Ninja says, scans aren't necessarily bad, and posting your Execution plans would be good 🙂
I like to think of hints as thinking of myself as smarter than the guy who wrote the plans, code and did 1 trillion+ test run.
RARELY, you will find edge cases where a hint is useful. I've yet to find one of those hint in my prod env after 10 years.
November 15, 2011 at 6:42 am
Ha, let me rephrase that - you could use the FORCESEEK hint, but I really really really _really_ wouldn't recommend it 🙂
I have had cause to use an index (not force seek/scan) hint. Once.
November 15, 2011 at 6:42 am
Gazareth (11/15/2011)
Ha, let me rephrase that - you could use the FORCESEEK hint, but I really really really _really_ wouldn't recommend it 🙂
I reallly really really wanted to make that point come across! 😀
November 15, 2011 at 6:55 am
Thanks for the replies...
Since removing the WHERE clause from the view, the execution plan is now showing:
RID Lookup
Cost 14%
No of Rows 34
Query completion: 112 ms
Adding the where back into place, gives me the following
Table Scan
Cost 55%
No of Rows 3million+ (Entire table contents)
Query completion: approx 30 secs
I'm assuming that the where clause within the view is processed 1st, thus scanning the full data set before any applying the inner join?
November 15, 2011 at 7:05 am
I just can't tell you what's happening without seeing both actual plans.
Ever tried to get your car fixed without taking it to a mechaninc? Pretty much same thing here :hehe:.
November 16, 2011 at 1:28 am
Ninja's_RGR'us (11/15/2011)
I just can't tell you what's happening without seeing both actual plans.Ever tried to get your car fixed without taking it to a mechanic? Pretty much same thing here :hehe:.
I'm going to be borrowing that phrase shortly, thank you Remi. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2011 at 4:40 am
Evil Kraig F (11/16/2011)
Ninja's_RGR'us (11/15/2011)
I just can't tell you what's happening without seeing both actual plans.Ever tried to get your car fixed without taking it to a mechanic? Pretty much same thing here :hehe:.
I'm going to be borrowing that phrase shortly, thank you Remi. 🙂
NP, just don't forget to quote the original author, which I have no idea who it is. 😀
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply