From Books Online:

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Employees

SELECT * FROM Northwind.dbo.Employees

I noticed LLBLGen does not use databasename.owner.object, but it uses owner.object. When I read the above quoted part of Books Online it should use 3 part naming, or am I missing something?

It does in adapter (always) and it also now does in selfservicing since 1.0.2004.2 when you use 2 or more catalogs in the project.

Is there a way to test if it generates a new execution plan? In selfservicing it uses [schema].[tablename], which IMHO should be enough to find back an execution plan. Though without proper measuring I don't know for sure .

(edit). Hmm. I read in BOL indeed that a 'fully' qualified name is a name which has 4 parts, server.catalog.owner.object. Leaving out server, defaults to the current server. I wonder if I leave out catalog will that default to the catalog connected ? The thing is: everything I read talks about 'has a better chance to find back the execution plan', though with a semi qualified name it also should work very well... (edit2): other reports on google in google groups/sqlserver newsgroup is that using a 2 part name (owner.object) already seems to be enough.

Anyway, I'll add a switch to the generator options in the designer which will allow the user to specify if he/she wants catalog names being generated into the selfservicing code.

(edit3): Ok, I did some tests (select * from master..syscacheobjects shows the live execution plans).

It turns out that select * from customers and select * from dbo.customers aren't seen as the same statements, though when either one of them is executed multiple times, it's plan is re-used, even from different connections. Apparently using more elements leads to more chance to find back the execution plan. Also, if the owner is specified (like I already do), for example dbo.customers, there is no adhoc plan, just compiled and executable plans. I think that makes a big difference, which is thus already the case.

So it's not impossible to reuse an existing plan with not fully qualified names, but chances increase when you use fully qualified names.

Could this be due to the fact there's a limit on the amount of time SQL Server spends to find an existing plan?

In any case, your testing proves Books Online wrong.

PeterDeB wrote:

So it's not impossible to reuse an existing plan with not fully qualified names, but chances increase when you use fully qualified names. Could this be due to the fact there's a limit on the amount of time SQL Server spends to find an existing plan?

Probably. It also increases the change of having 2 plans into the cache which both refer to 'Employees' but one is in Northwind.dbo and the other is in Workers.Frans, so I think it then gives up as it can't decide which one to use and will simply compile the code (I think, it also could decide to do other parsing still ).

In any case, your testing proves Books Online wrong.

I wouldn't say wrong, it sometimes talks about chances as well. The more elements you specify the better, which is of course true. I found some discussions about performance tuning with SqlServer MVPs as well, which talk about specifying owner.objectname as the bare minimum. What I saw proves that: when I specify: select * from master..syscachedobjects I get 'adhoc' plans as well and a compiled plan when I specify select * from master.dbo.syscachedobjects I don't get an adhoc plan, just a compiled plan.

similar for select * from dbo.customers : no adhoc plan, just compiled plan select * from customers: adhoc plan then compiled plan.

So having the owner in there is crucial.