Paging looses on performance plan caching with explicit TOP clause?

Posts   
 
    
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 29-Oct-2007 20:57:33   

Hi,

I have been profiling my application and one thing I've noticed was this kind of query:

INSERT INTO #TempTable ....... SELECT 1234 ..... FROM .........

Correct me if I'm wrong but doesn't the explicit use of the number of items in the TOP clause instead of a 'dynamic' alternative (TOP @items) cause SQL Server 2005 not to use the cached execution plan for that query?

Also, it messes my TOP 10 queries by CPU report, as all I see is the same query with differing TOP values simple_smile

Best regards, Robert Wilczynski.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Oct-2007 10:55:18   

Sqlserver 2005 paging uses a CTE with rownumber, so no temptable used there. THe temptable is specifically for sqlserver 7/2000. The top therefore has to be hardcoded into the query.

If you're using sqlserver 2005, please set the compatibility mode of the DQE to sqlserver2005 (see application configuration through the config file for details)

Frans Bouma | Lead developer LLBLGen Pro
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 30-Oct-2007 11:45:21   

Thanks Frans,

Any plans to include a custom LLBLGen configuration section to handle such setting? I'm not much of a documentation reader, I admit. Having such config section with intellisense would probably eliminate those annoying posts from me as it would take minutes to determine all settings of interest by just trying different things directly in a config file.

Robert.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 30-Oct-2007 18:20:26   

Robert.W wrote:

Thanks Frans,

Any plans to include a custom LLBLGen configuration section to handle such setting? I'm not much of a documentation reader, I admit. Having such config section with intellisense would probably eliminate those annoying posts from me as it would take minutes to determine all settings of interest by just trying different things directly in a config file.

It's my understanding that you get intellisense once an XSD is in scope of the editor, where the XSD contains the meta-data for the config sections you want to edit. So unless this is installed by the installer in the .net framework folder, I don't think intellisense will be possible. But perhaps I'm overlooking something...

Frans Bouma | Lead developer LLBLGen Pro
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 30-Oct-2007 19:43:42   

The file that makes intellisense work in VS is in one of VS directories:

C:\Program Files\Microsoft Visual Studio 8\Xml\Schemas\DotNetConfig.xsd

I guess that modifying that file would make instellisense for a custom configuration section work. There is also a catalog.xml file there which is worth looking at. It might be possible to map a custom config section XSD file (thus enabling intellisense) by mapping a namespace to a file and using the referencing the namespace in the configuration section:

<LLBLGen xmlns="http://llblgen.com/"> ...

I haven't done that myself - fortunately we're not developing developer centric products simple_smile - but it seems you can make this work.

Anyway, I would be happy with a *.chm help file listing all classes in the "LLBLGen.Configuration" namespace (or whatever you decide to call it) - that would be equally helpful serving as a reference for what can be tuned using the *.config file.

Robert.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-Oct-2007 10:39:50   

Robert.W wrote:

The file that makes intellisense work in VS is in one of VS directories:

C:\Program Files\Microsoft Visual Studio 8\Xml\Schemas\DotNetConfig.xsd

I guess that modifying that file would make instellisense for a custom configuration section work. There is also a catalog.xml file there which is worth looking at. It might be possible to map a custom config section XSD file (thus enabling intellisense) by mapping a namespace to a file and using the referencing the namespace in the configuration section:

<LLBLGen xmlns="http://llblgen.com/"> ...

I haven't done that myself - fortunately we're not developing developer centric products simple_smile - but it seems you can make this work.

I don't think it's appropriate to modify vs.net installed files, not only is it a security issue I think, but it's also not something I'd like to do: I also don't like it when installers tamper with the settings of my vs.net installation. the only one I know of is to load an .xsd into the ide and an xml file also open in the ide should refer to that xsd and it then has intellisense (as we use in the task/preset etc. xml files)

Anyway, I would be happy with a *.chm help file listing all classes in the "LLBLGen.Configuration" namespace (or whatever you decide to call it) - that would be equally helpful serving as a reference for what can be tuned using the *.config file. Robert.

All config configuration information is in a single section in the manual: Application configuration through config files. With the left tree in the chm file it's easy to jump to the section you want.

Frans Bouma | Lead developer LLBLGen Pro
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 31-Oct-2007 10:58:25   

The argument about modifying the VS installed files is fair - you probably don't want to get in the hassle of keeping track of different IDE versions and where to put which files when there are more important things to implement.

Regarding the manual - yes I already know where to look for information and skimmed the whole section. Putting all extra effort like intellisense aside I still think a custom configuration section would be nice to have if only for the purpose of extracting LLBLGen configuration to an external *.config file.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-Oct-2007 11:41:15   

Robert.W wrote:

Regarding the manual - yes I already know where to look for information and skimmed the whole section. Putting all extra effort like intellisense aside I still think a custom configuration section would be nice to have if only for the purpose of extracting LLBLGen configuration to an external *.config file.

That's a fair point. The main reason we didn't do this was because of the requirement to define a section definition element at the top of the config file, which is not always something people fully understand or realize, and in ASP.NET for example it requires the full assembly name reference, while in winforms/console apps it doesn't. I.o.w.: it would cause headaches for people while all they wanted to do was to add a single setting which could be added in the appSettings element without hassle.

We do use separate sections sometimes, if we need to group settings together, but not for individual settings. It's an idea we could add in v3 though, it would make things more clear indeed. I'll add a request for the single section for settings to the tracker.

Frans Bouma | Lead developer LLBLGen Pro
Robert.W
User
Posts: 79
Joined: 19-Jun-2007
# Posted on: 31-Oct-2007 21:07:52   

Getting back to the original topic - after setting compatibility mode to SQLServer2005 I'm getting a lot of following queries:

WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM ( SELECT TOP 1038 ... FROM ... WHERE ((...))) AS _tmpSet ) SELECT * FROM __actualSet ORDER BY [__rowcnt] ASC WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC

Why not use the TOP (@param) there? Am I missing something?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Nov-2007 12:02:27   

No you're not missing something, it's indeed something to improve. The SELECT query generator doesn't know the query is for paging, so it simply generates the TOP if required with the number embedded into the SQL. It doesn't use a parameter, as it's not really important. However I didn't think of execution plan trashing here, which is indeed something to improve. Although sqlserver does try to parameterize values in a sql string, it gives up rather early to preserve performance.

I'll file this as an enhancement for v2.6. Thanks for reporting! simple_smile

Frans Bouma | Lead developer LLBLGen Pro