We are having a performance issue with one of our views when it's called from c#. Looking at the SQL that nettiers is generating I see that all four parameters are passed in as nvarchar when they should be bit, uniqueueIdentifier, uniqueueIdentifier and datetime.This causes the query to create a plan that uses Convert_Implicit and affects index usage. The net result is that the query takes 10 seconds from c# and < 2 seconds from ssms. (I checked the connection settings are the same)My question Is there something I can do to make nettiers aware of the datatypes for calls to a view? Also, is this something that anyone else has observed?Thanks for any advice!Greg
I found the answer… When I was appending the criteria I was calling tostring on the column name so it wasn’t getting found.
Ie: query.Append(StageColumn.StageIDentifier.ToString(), stageIdentifier);
Rather than query.Append(StageColumn.StageIDentifier, stageIdentifier);
By sending in the string It wasn’t able to map the column and so it ended up as a string DbType
From: Blake Niemyjski [mailto:firstname.lastname@example.org]
Sent: Friday, February 24, 2012 4:07 PM
Subject: Re: [.netTiers General Support] SQL generated to call a view passes all params as nvarchar
I'll pass this onto the rest of the team to see if anyone else has any ideas. However, when I generated against this I had no problems with returning data (1000 rows) instantly in VS as well
as SSMS. I had some questions.
What are you using for the Component Layer? What version of SQL Server are you using? Is it hosted locally? What version of .netTiers are you using? How are you making the call to find?
I haven't come across this behavior. So your table definition has a bit \ uniqueidentifier and datetime columns which are selected from a view... Are there any PK's for this table? Is this view selecting from many different tables?
Is there any chance you could attach a table / view sample script that can reproduce this behavior.
CodeSmith Tools, LLC. Software Development Engineer
.NetTiers team | Visit http://www.nettiers.com
All tables have Pk's. The view does have a few left joins... but nothing horrific. I will make a test view that selects the columns from the table with no joins and see if I can reproduce it with a simplified example.
I can recreate the issue with the schema below.
Notice at the bottom the generated SQL uses nvarchar?
CREATE TABLE [dbo].CallNote(
[CallNoteIdentifier] [uniqueidentifier] NOT NULL,
[InstitutionIdentifier] [uniqueidentifier] NOT NULL,
[IsDeletedIndicator] [bit] NOT NULL,
[CreateDatetime] [datetime] NOT NULL,
CONSTRAINT [XPKCallNote] PRIMARY KEY NONCLUSTERED (
CREATE VIEW [dbo].[TestViewSearch]
FROM CallNote cn
This is the code that nettiers generates:
exec sp_executesql N'
with PageIndex as (
select top 2147483647 row_number() over (order by [IsDeletedIndicator]) as RowIndex
from [dbo].[TestViewSearch] where (IsDeletedIndicator = @Param0)
AND (InstitutionIdentifier = @Param1)
-- get total count
select @@ROWCOUNT as TotalRowCount;
',N'@Param0 nvarchar(1),@Param1 nvarchar(36)',@Param0=N'0',@Param1=N'00000000-0000-0000-0000-000000000005'
I'll pass this onto the rest of the team to see if anyone else has any ideas. However, when I generated against this I had no problems with returning data (1000 rows) instantly in VS as well as SSMS. I had some questions.
good find!. I'll mark this as a solution and hopefully anyone else running into this will find the solution!