473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Uniqueidentifier causing strange execution plan

Can anyone help me with this strange problem please?

I have a stored procedure, with a parameter defined as a
uniqueidentifier. The procedure does a select with a number of joins,
and filters within the Where clause using this parameter.
(@orderHeader_id uniqueidentifier)

SELECT *
FROM originalOrderHeader ooh
INNER JOIN originalOrderLine ool
ON ooh.id = ool.id
FULL OUTER JOIN orderLine ol
on ool.id = ol.id
AND ool.productCode = ol.productCode
where (ooh.id = @orderHeader_id)
There is a clustered index on the id column of originalOrderHeader,
and on id and productCode of both originalOrderLine and orderLine.
These indexes are regularly rebuilt. The execution plan shows a seek
against these indexes, but the estimated row count values are huge,
and should be single figures.

If I change the SP to accept the parameter as a varchar, and then
explictly cast back to a uniqueidentifier in the where clause, the SP
runs much, much quicker, the execution plan is doing far less work,
and crucially the estimated row counts on the clustered index seeks
are correct (single figures).

Does anyone have any ideas what might be causing this?
Jul 20 '05 #1
1 1992
Vinny (vi*************@emulous.co.uk) writes:
I have a stored procedure, with a parameter defined as a
uniqueidentifier. The procedure does a select with a number of joins,
and filters within the Where clause using this parameter.
(@orderHeader_id uniqueidentifier)

SELECT *
FROM originalOrderHeader ooh
INNER JOIN originalOrderLine ool
ON ooh.id = ool.id
FULL OUTER JOIN orderLine ol
on ool.id = ol.id
AND ool.productCode = ol.productCode
where (ooh.id = @orderHeader_id)
This does not look right. Do you really mean FULL JOIN? Thanks to the
WHERE predicate, this is effectively an LEFT JOIN.
There is a clustered index on the id column of originalOrderHeader,
A clustered index on uniqueidentifier column is rarely a good idea,
because you easily get large amount of fragmentation.
and on id and productCode of both originalOrderLine and orderLine.
These indexes are regularly rebuilt. The execution plan shows a seek
against these indexes, but the estimated row count values are huge,
and should be single figures.

If I change the SP to accept the parameter as a varchar, and then
explictly cast back to a uniqueidentifier in the where clause, the SP
runs much, much quicker, the execution plan is doing far less work,
and crucially the estimated row counts on the clustered index seeks
are correct (single figures).


I can't say whether the mysterious use of FULL JOIN has anything to
do with it, but at least it's worth to investigate.

Since I don't know the full story how the tables look like, and have not
seen the query plans, it's difficult to be very precise. Certainly a
feature known as "parameter sniffing" is involved here. When SQL Server
builds the query plan for a stored procedure, it looks at the input
parameters and the value of these as a hint when it builds the plan.
Since the plan is cached, this means that if the first invocation of the
procedure has an odd value, you may end up with a bad plan. What you
achieve with your varchar parameter, is that you effectively kill
parameter sniffing, since the optimizer is blind for the values of
variables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Stefano | last post by:
Hi everibody, it's the first time i post on this newsgroup. I'm Stefano from Milano, italy. I'm a beginners with Sql2000. My problem is this. I run a View using enterprise manager and after less...
5
by: Bernie | last post by:
Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as...
3
by: Will Atkinson | last post by:
Hi All, I'm a relative newbie to SQL Server, so please forgive me if this is a daft question... When I set "Show Execution Plan" on in Query Analyzer, and execute a (fairly complex) sproc, I...
2
by: Jenny Zhang | last post by:
The osdl-dbt3 test starts with building and vacuuming the database. The execution plans were taken after the vacuuming. I did two tests with the same database parameters: 1. run two osdl-dbt3...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
1
by: Sandra | last post by:
I am trying to convert a string into a uniqueidentifier by using the following code string contrID = Request.Params["oId" SqlGuid sqlID = SqlGuid.Parse(contrID I am then putting sqlID into my...
2
by: Roy Rodsson via .NET 247 | last post by:
Hi all! I am using a stored procedure in SQL2000 for retrieving fileinformations from a db. the table as an uniqueidentifier for the file information. The stored procedure has a variable...
4
by: Praveen_db2 | last post by:
Hi All I am getting strange errors in my db2diag.log can any one tell me what these errors mean?? Following is the code from my db2diag.log...
2
by: Ina Schmitz | last post by:
Hi NG, does IBM Universal Database 8.2 make any difference between actual and estimated execution plans like in SQL Server ("set showplan_all on" for estimated execution plan and "set statistics...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.