Welcome to the CodeSmith Community!

SQL generated to call a view passes all params as nvarchar

.netTiers

A description has not yet been added to this group.

SQL generated to call a view passes all params as nvarchar

Answered (Verified) This question is answered

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

  • Post Points: 35
Verified Answer
  • Hi Blake,

     

    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

     

     

     

     

All Replies
  • Hello,

    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.

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • 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 (

    [CallNoteIdentifier] ASC

    )

    )

    CREATE VIEW [dbo].[TestViewSearch]

    AS

    SELECT cn.InstitutionIdentifier,

    cn.IsDeletedIndicator,

           cn.CreateDatetime

    FROM CallNote cn

    This is the code that nettiers generates:

    exec sp_executesql N'

    BEGIN

    with PageIndex as (

    select top 2147483647 row_number() over (order by [IsDeletedIndicator]) as RowIndex

    , [InstitutionIdentifier]

    , [IsDeletedIndicator]

    , [CreateDatetime]

    from [dbo].[TestViewSearch] where  (IsDeletedIndicator = @Param0)

    AND (InstitutionIdentifier = @Param1)

    )

    select

          [InstitutionIdentifier],

          [IsDeletedIndicator],

          [CreateDatetime]

     from PageIndex

    -- get total count

    select @@ROWCOUNT as TotalRowCount;

    END

    ',N'@Param0 nvarchar(1),@Param1 nvarchar(36)',@Param0=N'0',@Param1=N'00000000-0000-0000-0000-000000000005'

  • Hello,

    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?

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

  • Hi Blake,

     

    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

     

     

     

     

  • Ah,

    good find!. I'll mark this as a solution and hopefully anyone else running into this will find the solution!

    Blake Niemyjski
    CodeSmith Tools, LLC. Software Development Engineer
    Blog: http://windowscoding.com/blogs/blake/
    .NetTiers team | Visit http://www.nettiers.net

Page 1 of 1 (7 items)